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.

score 2 · Accepted Answer · answered Jun 30 '17 at 03:03

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 '17 at 03:03

Scott

17,127
5
53
64

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... – pitalig Jun 30 '17 at 03:36
You need to read up on Postgres date/time specs: https://www.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. – Alan Thompson Jun 30 '17 at 03:51
1

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](https://stackoverflow.com/questions/6663765/postgres-default-timezone) and I also changed the date column type to "timestamp [without time zone]". – pitalig Jun 30 '17 at 14:58

Clojure change date after add to postgresql

1 Answers1