3

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)?

Joe
  • 46,419
  • 33
  • 155
  • 245

2 Answers2

12

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"]
sw1nn
  • 7,278
  • 1
  • 26
  • 36
  • _to add some google keywords/a back link_: my problem was that I would store a UTC in a MySQL db, and I would query for it in clojure, it would suddenly be a local time, and no amount of coercion would get it interpreted as UTC. I asked `clj-time` maintainers to drop a note of this in their docs somewhere: https://github.com/clj-time/clj-time/issues/233 – Josh.F May 13 '17 at 01:06
0

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.

Arthur Ulfeldt
  • 90,827
  • 27
  • 201
  • 284
  • Thanks! sw1nn's answer solved it, but I thought that `exec-raw` didn't work with selects? I get `SQLException Can not issue executeUpdate() for SELECTs com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1074)` when I `(k/exec-raw "select * from demo")`. – Joe Aug 05 '14 at 08:51