CODEWITHSUNDEEP
mysqljdbcclojuretimezonesqlkorma
Joe
Joe

Reputation: 47609

Timezone issues with MySQL and clj-time

I have a table in MySQL.

create table demo ( theDate datetime );

I insert two dates, one in daylight saving time, one not.

(require '[clj-time.core :as t])
(require '[clj-time.coerce :as coerce])
(require '[korma.core :as k])

(k/insert :demo (values {:theDate (coerce/to-sql-date (t/date-time 2014 01 01))}))
(k/insert :demo (values {:theDate (coerce/to-sql-date (t/date-time 2014 06 01))}))

From my MySQL client it looks like the right values have gone in:

mysql> select * from demo;
+---------------------+
| theDate             |
+---------------------+
| 2014-01-01 00:00:00 |
| 2014-06-01 00:00:00 |
+---------------------+

When I select with Korma (I don't imagine Korma is doing anything relevant on top of JDBC), I get a timezone difference in the non-daylight saving time date.

=> (k/select :demo)
[{:theDate #inst "2014-01-01T00:00:00.000000000-00:00"}
 {:theDate #inst "2014-05-31T23:00:00.000000000-00:00"}]

And when I select the dates:

(map #(-> % :theDate coerce/from-sql-date t/month) (k/select :demo))
(1 5)

Whereas I would have expected to get (1 6) (I deliberately put the dates on a month boundary to illustrate). The same thing happens when I use date rather than datetime MySQL type.

What am I missing? How do insert [(t/date-time 2014 01 01) (t/date-time 2014 06 01)] and get back (1 6)?

Upvotes: 3

Views: 1049

Answers (2)

sw1nn
sw1nn

Reputation: 7328

The result you get depends on the default timezone for the JVM. You can fix that via whatever mechanism the host operating system gives you.But in my experience it's generally better to force the JVM to a known value explicitly.

This is achieving with a property on the command line, or in leiningen project.clj

:jvm-opts ["-Duser.timezone=UTC"]

Upvotes: 12

Arthur Ulfeldt
Arthur Ulfeldt

Reputation: 91534

We encountered this as well and ended up using korma's exec-raw and specifying:

at time zone 'utc' as theData

to actually get back the correct time zone.

Upvotes: 0

Related Questions