CODEWITHSUNDEEP
javamysqljdbc
ps0604
ps0604

Reputation: 1071

MySQL: date value in table different than value in select result due to timezone

I'm running a java based application accessing MySQL with JDBC connector.

If I run this select in MySQL Workbench (where the type of per_date_from is DATE):

select per_date_from from formulas;

I get a single row:

1993-05-27

Now, if I run this Java code:

    stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select per_date_from from formulas");
    while (rs.next()) {
        java.sql.Date d = rs.getDate(1);
        System.out.println("d=" + d);
        java.sql.Timestamp ts = rs.getTimestamp(1);
        System.out.println("ts=" + ts);
    }

I get this result in the console:

09:35:51,412 INFO  [stdout] (default task-2) d=1993-05-26

09:35:51,412 INFO  [stdout] (default task-2) ts=1993-05-26 23:00:00.0

Both the Java code and the MySQL Workbench have the following option in the connection:

serverTimezone=America/New_York

What is going on? Why does the column type DATE have a time?

UPDATE

MySQL server version: 5.7.20

JDBC connector version: 8.0.13

According to here they are compatible

Upvotes: 1

Views: 183

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

Any ideas what could have happened?

Daylight Saving Time. serverTimezone=America/New_York tells MySQL Connector/J to use that time zone, which observes DST. However, your JVM is using GMT-05:00 which is a fixed offset and does not observe DST. So if the MySQL DATE happens to be during the period where New York is on Eastern Daylight Time then the Timestamp will be an hour off ...

sql = "SELECT CAST('1993-05-27' AS DATE)"; 
ResultSet rs = st.executeQuery(sql);
rs.next();
Timestamp t = rs.getTimestamp(1);
System.out.println(t);  // 1993-05-26 23:00:00.0

... whereas if the date was during the period when New York was on Eastern Standard Time then the Timestamp would contain the correct date:

sql = "SELECT CAST('1993-01-27' AS DATE)"; 
ResultSet rs = st.executeQuery(sql);
rs.next();
Timestamp t = rs.getTimestamp(1);
System.out.println(t);  // 1993-01-27 00:00:00.0

Moral of the story:

Stop using java.sql.Timestamp. For column types like DATE and DATETIME that do not contain timezone information use java.time.LocalDate and java.time.LocalDateTime instead.

Upvotes: 1

Related Questions