JDBC Mysql Timezone issue with Day Light saving

Question

I am facing an issue with MySql Select statement for date field with Day Light Saving.

Cause: java.sql.SQLException: HOUR_OF_DAY: 2 -> 3

The only solution worked for is setting serverTimezone=GMT-6 in MySql Connection String. But this I will need to change when daylight saving is not there.

If I am trying to set serverTimezone=America/Denver then it give same error.

Cause: java.sql.SQLException: HOUR_OF_DAY: 2 -> 3

My Database time zone is America/Denver

My project is Jersey project with Java 8 and Mybatis.

I tried multiple suggested solution but nothing is working concrete. Please guide me on this.

With respect, it didn't work is not a helpful description of a fault. What happened? Did the server refuse the connection? Did it deliver incorrect results? Is the MySQL column declared DATETIME or TIMESTAMP? Please edit your question. — O. Jones
@Joni I cannot show you the code. There is date column with value 2019-03-10 02:04:13. The issue is with these types of records only. The issue is coming with Select Query. If I execute the query on the Mysql database directly it works. — Ajay Sharma
Well that time is wrong, on 2019-03-10 the clock was never 02:04:13 in America/Denver. It skipped from 1:59:59 to 3:00:00 timeanddate.com/time/change/usa/denver — Joni
What behavior would you expect: should your query return the time as 03:04:13? — Joni

Basil Bourque Basil Bourque · Accepted Answer · 2020-04-24T16:14:54

You should be tracking moments in your database in a column of type TIMESTAMP in MySQL version 8. This type is akin to the SQL standard type TIMESTAMP WITH TIME ZONE.

Using JDBC 4.2 or later, exchange java.time objects with your database.

ZoneId z = ZoneId.of( "America/Denver" ) ;
LocalDate ld = LocalDate.of( 2020 , Month.JANUARY , 23 ) ;
LocalTime lt = LocalTime.of( 2 , 0 ) ;
ZonedDateTime zdt  = ZonedDateTime.of( ld , lt , z ) ;

The ZonedDateTime class automatically adjusts for anomalies such as Daylight Saving Time (DST). If 2 AM does not exist on a certain date on a certain date with a “Spring ahead” DST cutover, the time-of-day is adjusted to 3 AM.

Oddly, JDBC 4.2 requires support for OffsetDateTime but not the more commonly used Instant and ZonedDateTime. So convert.

myPreparedStatement.setObject( … , zdt.toOffsetDateTime() ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
ZonedDateTime zdt = odt.withZoneSameInstant( z ) ;

Notice that using this approach means we don’t care about the current default time zone on The server or the client, nor do we care about the current default time zone in your session.

JDBC Mysql Timezone issue with Day Light saving

1 Answers