MySql Timezone JDBC issue

Question

I am trying to insert a date value in MySql table name person and column name regdate with data type = datetime. I am setting a value e.g. '2019-08-21 20:25:20' but after saving +5:30 hours get added and value which gets stored is '2019-08-22 03:55:20'. Generating the date value using below Java code

Timestamp curDate = Timestamp.valueOf(Instant.now().atZone(ZoneId.of("Asia/Kolkata")).toLocalDateTime());

and then using .setTimestamp(1, curdate); in INSERT query.

I have checked that the timezone of MySql is set to IST (GMT+0530). App Server timezone is also set to IST. But I am not able to understand why +5:30 hours are getting added even if I explictly setting the date value.

I have tried setting timezone in connection string as ?serverTimezone=Asia/Kolkata but didn't work.

But if I run the same code using my local machine connecting same MySql instance, I get no problem and same value gets stored without addition of 5:30 hours. I checked App Server timezone and it is IST.

MySql version - 5.7.17-log mysql-connector-java - 8.0.15

Am I missing something?

Answer 1

You have a few problems here.

Avoid legacy date-time classes

First of all, you are mixing the terrible legacy date-time classes (java.sql.Timestamp) with the modern java.time classes. Don’t. Use only classes from the java.time packages.

LocalDateTime cannot represent a moment

You are using LocalDateTime to track a moment, which it cannot. By definition, that class does not represent a point on the time line. That class has a date and a time-of-day but intentionally lacks the context of a time zone or offset-from-UTC. Calling toLocalDateTime strips away vital information about zone/offset.

Tools lie

You are likely getting confused by the well-intentioned but unfortunate behavior of many tools to dynamically apply a time zone while generating text to represent the date-time value retrieved from the database. You can avoid this by using Java & JDBC to get the pure unadulterated value from the database.

TIMESTAMP WITH TIME ZONE

You failed to disclose the exact data type of your column in your database. If you are trying to track a moment, use a data type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE. In MySQL 8 that would, apparently, be the TIMESTAMP type according to this doc. (I am a Postgres guy, not a MySQL user.)

In JDBC 4.2 and later, we can exchange java.time objects with the database. So no need to over touch java.sql.Timestamp again.

Unfortunately, the JDBC spec oddly chose to not require support for Instant (a moment in UTC) nor forZonedDateTime(a moment as seen in some particular time zone). The spec does require support for [OffsetDateTime`]2.

Tip: Learn to work in UTC for the most part. Adjust into a time zone only when required by business logic or for presentation to the user.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;  // Capture current moment in UTC.

Write to the database via a prepared statement.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

View that moment through the wall-clock time used by the people of some particular region (a time zone).

ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;