Clojure change date after add to postgresql

Question

I'm having a problem with clojure and postgresql.

When I insert, for example, "2017-06-27" to DB (date format column) it saves "2017-06-26" and when I select this result, the object returned is #inst "2017-06-28T03:00:00.000-00:00"

The most strange is that this happens when I run tests at my local machine, because I've created a docker setup and there the same code works perfect.

Here are the codes:

(ns balances.db
  (:require [clj-time.coerce :as c]
            [clj-time.format :as f]))

; Function to convert data objects from queries to string
(defn db-to-str [date]
  (f/unparse (f/formatters :date) (c/from-sql-date date))
)

; Function to convert string to data objetcs that can be inserted in db
(defn str-to-db [date]
  (c/to-sql-date date)
)

(ns balances.operation
  (:require [clojure.java.jdbc :as jdbc]
            [balances.db :refer [db-spec]]))

(defn create-operation [accountid amount description operationdate]
  (jdbc/insert! db-spec :operations {
    :accountid accountid 
    :amount amount 
    :description description 
    :operationdate operationdate
  })
)

(ns balances.operation-test
  (:use clojure.test
        ring.mock.request
        balances.core-test)
  (:require [balances.operation :as operation]
            [balances.db :as db]))

(use-fixtures :each db/clear-db-fixture)

(deftest operation
    (testing "create operation"
      (let [op (first (operation/create-operation 1 100 "Test operation" (db/str-to-db "2017-06-27")))]
        (is (= "2017-06-27" (db/db-to-str (op :operationdate))))
      )
    )
)

At local enviroment, create-operation test fails, returning:

expected: (= "2017-06-27" (db/db-to-str (op :operationdate)))
  actual: (not (= "2017-06-27" "2017-06-26"))

At docker enviroment the test pass.

Scott · Accepted Answer · 2017-06-30 03:03:02Z

2

It sounds like your local environment has a different Locale and/or timezone to your database and docker environment.

Open a Clojure REPL in each environment and check to see what the JVM Locale is set to:

(str (java.util.Locale/getDefault))

Also open a terminal shell on each environment (bash?) and type date to see what the OS time zone is set to.

If any one of them is different, then you will know that you need to synchronise them and correct at least one of the JVM or OS environments to use the same timezone and Locale.

answered Jun 30, 2017 at 3:03

Scott

17.3k5 gold badges56 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pitalig Over a year ago

Thanks! It's it! While docker date is "...UTC...", local is "...-3..." so it's adding 3 to the date when it is saved. But now, how can I avoid this? I want to save exactly the date that was typed...

Alan Thompson Over a year ago

You need to read up on Postgres date/time specs: postgresql.org/docs/9.1/static/datatype-datetime.html. In general, the easiest approach is to always, always use UTC for input & output or you will have recurring time zone conflicts. Note that Postgres always uses UTC for stored timestamps. All other time zones are only for input/output.

pitalig Over a year ago

Just to leave information for others that came here, the issue was solved by changing postgresql timezone to UTC with "SET TIME ZONE 'UTC';", as explained here and I also changed the date column type to "timestamp [without time zone]".

Collectives™ on Stack Overflow

Clojure change date after add to postgresql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related