MySQL JDBC date issues with database server in different timezone

Question

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)

I think UNIX_TIMESTAMP value is not the same as System.currentTimeMillis() some sort of conversion needs to be done between UNIX_TIMESTAMP (milliseconds) to Java's millisecond representation. Check this link: http://stackoverflow.com/questions/3188425/java-date-format-conversion — george_h, Jun 22 '12 at 15:06
I know it's not the same, let me fix the example, it's not about the zero's at the end. @Olaf The startDateTime column is defined as 'datetime' but with 'timestamp' I have the same issue — Somatik, Jun 22 '12 at 15:11
MySQL TIMESTAMP datatype is time zone -aware, while DATETIME is time zone -agnostic. If you run query `SELECT startDateTime FROM Event WHERE id=574` in Europe/London and Europe/Brussels, you will get the same results for a DATETIME field but different for the TIMESTAMP field — Olaf, Jun 22 '12 at 18:05
even with timestamp type the jdbc driver messes up (as indicated in my question) — Somatik, Jun 23 '12 at 09:04

Somatik · Accepted Answer · 2012-07-19T08:02:06.080

2

I was able to fix this issue with the connection string parameter

useJDBCCompliantTimezoneShift=true&useSSPSCompatibleTimezoneShift=true

but I'm not 100% comfortable with this as it causes even more trouble when the database is not in UTC/GMT timezone.

edited Jul 19 '12 at 08:02

answered Jun 26 '12 at 14:09

Somatik

4,723
3
37
49

score 1 · Answer 2 · answered Jun 22 '12 at 18:12

1

The JDBC driver is using time zone settings from the JVM it is running in. If you are running your application in the Europe/Brussels, it assumes that timezone. If your database server is running in another timezone, the results of calculations made in Java might differ from the results of the calculations made in SQL.

answered Jun 22 '12 at 18:12

Olaf

6,249
1
19
37

Makes no sense to me, an absolute date (unix time) should stay the same when querying data from the server, I need this fixed. – Somatik Jun 23 '12 at 09:03
1

I appreciate your sense of humor! You can always store unix time as a long integer. The DATETIME data type is admittedly broken in all databases that I know of. The TIMESTAMP (or as it called in many other databases, TIMESTAMP WITH TIMEZONE) was supposed to fix that, but the decades of relying on the DATETIME data type left so many developers confused on how things work, that good luck making it work unless you control the code for both the source and consumer of data. – Olaf Jun 23 '12 at 14:38

sytolk · Answer 3 · 2013-10-31T14:16:51.313

you can set MySQL Timestamp column type and:

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.user}"/>
    <property name="password" value="${jdbc.pass}"/>
    <property name="idleConnectionTestPeriodInMinutes" value="60"/>
    <property name="idleMaxAgeInMinutes" value="240"/>
    <property name="maxConnectionsPerPartition" value="30"/>
    <property name="minConnectionsPerPartition" value="10"/>
    <property name="partitionCount" value="3"/>
    <property name="acquireIncrement" value="5"/>
    <property name="statementsCacheSize" value="100"/>
    <property name="initSQL" value="SET time_zone='${database.timezone}'"/>

this is my bonecp dataSource -> see the initSQL this query is executed on the init of every mysql connection. If you web server is in Europe/Brussels set this time zone to ${database.timezone} this works for me. If you mysql have empty timezone tables you neeads to import it form Linux locales like:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

MySQL JDBC date issues with database server in different timezone

3 Answers3