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