MySQL JDBC date issues with database server in different timezone
- by Somatik
I have a database server in "Europe/London" time zone and my web server in "Europe/Brussels". Since it is summer time now my application server has a 2 hour difference.
I created a test to reproduce my issue:
Query q = JPA.em().createNativeQuery("SELECT UNIX_TIMESTAMP(startDateTime) FROM `Event` WHERE `id` =574");
BigInteger unix = (BigInteger) q.getSingleResult();
System.out.println(unix + "000 UNIX_TIMESTAMP to BigInteger");
Query q2 = JPA.em().createNativeQuery("SELECT startDateTime FROM `Event` WHERE `id` =574");
Timestamp o = (Timestamp) q2.getSingleResult();
System.out.println(o.getTime() + " Timestamp");
The startDateTime column is defined as 'datetime' (but same issue with 'timestamp')
The output I am getting is this:
1340291591000 UNIX_TIMESTAMP to BigInteger
1340284391000 Timestamp
Reading java date objects results in a shift in time zone, how do I fix this? I would expect the jdbc driver to just set the "unix time" value it gets from the server in the Date object.
(a proper solution should work with any timezone combination, not only for db in GMT)
