Timezone issues with MySQL and clj-time
I have a table in MySQL.
create table demo ( theDate datetime );
I am inserting two dates, one is during summer time and the other is 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, this looks like the correct values:
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 think Korma does everything JDBC related), I get the timezone difference in Daylight Saving Time.
=> (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 dates:
(map #(-> % :theDate coerce/from-sql-date t/month) (k/select :demo))
(1 5)
Whereas I was expecting to receive (1 6)
(I am deliberately putting dates on a month boundary to illustrate). The same thing happens when I use the MySQL type date
instead datetime
.
What am I missing? How to insert [(t/date-time 2014 01 01) (t/date-time 2014 06 01)]
and return (1 6)
?
source to share
The result you get depends on the default timezone for the JVM. You can fix this with whatever mechanism the host operating system gives you. But in my experience it is usually better to force the JVM for a known value.
This is achieved with a property on the command line or in leiningen project.clj
:jvm-opts ["-Duser.timezone=UTC"]
source to share