MySQL DATE type and JDBC connection conversion issues
Today I was working on my side project accountable and ran into the fun of trying to line up timezones between the application and the MySQL server I'm using. I say fun because this of course involves the infamous java.util.Date, which has been a pain to developers.
In my application, I create Instant classes since that's the recommended
way to go now. And for my purposes I wanted to store them as UTC that way
I can convert them to any locale I'd like to later on, since I'm also
only concerned about the date and no hours, I used the MySQL Date type
for my database table, this, when combined with Anorm's parser leads me
to the use of the
Date class in my code.
The failing test was failing with an odd error. When creating and
persisting my domain class to the database, the
would read as the correct UTC timestamp. However, when reading the
field back out of the database when retrieving that same instance, I'd
see a change equal to my timezone. This struck me as a bit odd, since I
had spent some time trying to make sure I created UTC and stored UTC
After a few hours scratching my head I came to the conclusion that I must have done something wrong, and when I found my pre-database-insertion code using my system zone I thought to myself, ah of course!
val localTime = ZonedDateTime.ofInstant(Instant.ofEpochSecond(expense.dateOccured), ZoneId.systemDefault)
Clearly, this must be it. After all, that's where the extra hours might come from right? And changed it promptly to:
val localTime = ZonedDateTime.ofInstant(Instant.ofEpochSecond(expense.dateOccured), ZoneId.of("UTC"))
But unfortunately, this wasn't the case. It simply shifted my hours for both the stored instance and the returned instance. Remembering that the MySQL Date type documentation mentions some conversions on storage, I figure'd I'd read it again:
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
So it's only for the TIMESTAMP type, not for the type I'm using here. This meant that it had to have something to do with the java client itself. Looking at the connector paramaters one can pass to the JDBC it became clear that the client was performing a conversion from the client side of the connection to whatever the server was speaking in. Checking the timezone of the server is fairly simple from a MySQL CLI:
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); -- OR/AND select @@global.time_zone;
If the above two give back a time difference of
00:00:00 or the time
+00:00 then your server is in UTC already. Mine wasn't, so I
updated my my.cnf file to set the
[client] default-time-zone='+00:00' ... [mysqld] default-time-zone='+00:00'
Sadly, this still didn't fix my problem entirely, now instead of both
sides of the
Date being shifted, I was back to where I was before with
a valid UTC on creation, and an incorrect returned value from what felt
like the database. After some more thinking, I started wondering if the
client's default parameters included automatically converting Date's from
the server's time to the recieving end. And sure enough, once I set the
timezone on the application side:
javaOptions in Test ++= Seq("-Dconfig.file=conf/test.conf", "-Duser.timezone=UTC") javaOptions in Runtime += "-Duser.timezone=UTC"
My issues disappeared. But setting the user.timezone is a bit drastic as it affects the entire JVM. I found another option in the parameter lists that seems like it should fix my problem without having to specify the timezone at the JVM level, especially since it seems that anorm doesn't set a calendar which is good, as the answer on that S.O. question explicitly says the trick only works with a null Calendar. But, like one of the commenters I was still getting the client conversion on reading the data for some reason.
So for now, I'm going to set my JVM to run in UTC as that seems like the only thing that actually fixes the client-reading conversion problem. This isn't a bad thing, as if I were running this application on a "real" server and not just my local computer, I'd probably be running that box on UTC anyway.