clj-time.coerce java.sql.Date .toString decrements date?

Question

test_ns.clj

(ns test-ns
  (:require [clj-time.jdbc :as tj]
            [clj-time.coerce :as tc]))

(.toString (tc/to-sql-date (tc/from-string "2018-09-28")))

=> "2018-09-27"

Why does .toString decrement the date?

The expectation is that the formatted date string would match its input
tc/to-sql-date converts to java.sql.Date class
The .toString method appears to be used by hugsql default jbdc adaptor, which is a project dependency used for sql transactions

project.clj:

(defproject my-project "0.1.0-SNAPSHOT"
  ...
  :min-lein-version "2.0.0"
  :dependencies [[org.clojure/clojure "1.10.0-beta1"]
                 [duct/core "0.7.0-alpha8"]
                 [duct/module.sql "0.5.0-alpha1"]
                 [duct/module.logging "0.4.0-alpha1"]
                 [duct/database.sql.hikaricp "0.3.3"]
                 [com.walmartlabs/lacinia "0.21.0"]
                 [com.rpl/specter "1.1.1"]
                 [net.mikera/core.matrix "0.62.0"]
                 [com.wsscode/pathom "2.2.4"]
                 [org.clojure/core.async "0.4.474"]
                 [org.postgresql/postgresql "42.2.5"]
                 [org.clojure/core.async "0.4.490"]
                 [com.layerware/hugsql "0.4.9"]
                 [cheshire "5.8.1"]
                 [camel-snake-kebab "0.4.0"]
                 [hickory "0.7.1"]
                 [incanter "1.9.3"]
                 [clj-http "3.9.1"]
                 [environ "1.1.0"]]
  :plugins [[lein-environ "1.1.0"]
            [duct/lein-duct "0.11.0-alpha6"]
            [lein-exec "0.3.7"]]
  :main ^:skip-aot portfolio-management.main
  :test-paths   ["test"]
  :source-paths ["src"]
  :target-path "target/%s"
  :resource-paths ["resources" "target/resources"]
  :prep-tasks ["javac" "compile" ["run" ":duct/compiler"]]
  :profiles
  {:dev          [:project/dev :profiles/dev]
   :repl         {:prep-tasks   ^:replace ["javac" "compile"]
                  :repl-options {:init-ns user}}
   :uberjar      {:aot :all}
   :profiles/dev {}
   :project/dev  {:source-paths   ["dev/src"]
                  :resource-paths ["dev/resources"]
                  :dependencies   [[integrant/repl "0.3.1"]
                                   [eftest "0.4.1"]
                                   [kerodon "0.9.0"]
                                   ]}})

EDIT

~$ java -version
openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-8u191-b12-2ubuntu0.18.04.1-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)

I cannot reproduce this. An empty project with that `project.clj` and code returns "2018-09-28". — user2609980, Mar 02 '19 at 13:56
Can reproduce. I had to remove the `clj-time.jdbc` `:require` since it caused errors, but the code still runs, giving `"2018-09-27"` as a result. Did your requiring of `clj-time.jdbc` cause a `No such var: jdbc/IResultSetReadColumn` error? — Carcigenicate, Mar 02 '19 at 17:56
Interestingly, `(let [d (tc/to-sql-date (tc/from-string "2018-09-28"))] [(.getMonth d) (.getDate d)])` returns `[8 27]`. It almost seems like something is treating months and days as starting with 0, when they should be starting with 1. — Carcigenicate, Mar 02 '19 at 18:06
I think this is a time zone issue. One of the date constructors is taking local time into consideration, while another isn't. Note [this](https://gist.github.com/carcigenicate/a55f05b7a0c65105a3338ccbf4cc8dc0). This would be a problem since `to-sql-time` does it's conversion via dealing with milliseconds since the epoch. That would also possibly explain why Erwin can't repro, since it would depend on the local time. I think this is a library bug. — Carcigenicate, Mar 02 '19 at 19:06

score 2 · Accepted Answer · answered Mar 03 '19 at 14:11

I'm pretty sure that this is a timezone issue as Carcigenicate already pointed out. from-string parses a string in the UTC timezone and java.sql.Date uses the default JVM timezone. The returned result is correct if you're in a timezone which is "behind" UTC (to the west) because in those timezones the date 2018-09-28 00:00:00 is actually 2017-09-27.

(java.util.TimeZone/setDefault(java.util.TimeZone/getTimeZone "GMT"))
(.toString (tc/to-sql-date (tc/from-string "2018-09-28")))
;; => "2018-09-28"

(java.util.TimeZone/setDefault(java.util.TimeZone/getTimeZone "GMT-1"))
(.toString (tc/to-sql-date (tc/from-string "2018-09-28")))
;; => "2018-09-27"

You shouldn't use java.sql.Date and expect that it returns a date in the UTC timezone as long as that's not your default timezone.

Thank you! Setting the default time zone to GMT resolved the reported behavior. Did not expect a date object to have such a dependency but this makes sense. — skullgoblet1089, Mar 04 '19 at 02:35
Yeah, it's bit tricky and we got bitten by this in production too. And because we're in GMT+1 time zone but some of our customers run in GMT-X it was pretty tricky; especially since the conversion was done "silently" when saving data into the database. — Juraj Martinka, Mar 05 '19 at 08:09

clj-time.coerce java.sql.Date .toString decrements date?

1 Answers1