MySQL Bugs: #71084: Wrong java.sql.Date stored if client and server time zones differ

Bug #71084	Wrong java.sql.Date stored if client and server time zones differ
Submitted:	5 Dec 2013 4:58	Modified:	5 Feb 2015 17:50
Reporter:	Pasi Eronen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.27	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
The program below attempts to store date 2013-11-20 to the database, but the date read back (and actually stored, as shown in mysql command-line tool) is 2013-11-19.

Server time zone is UTC, client time zone is Europe/Helsinki. Things work correctly if the client is also in UTC, or if useLegacyDatetimeCode=true.

Tested with mysql-server-5.5.34, mysql-connector-java-5.1.27.jar on Ubuntu 12.04 LTS with OpenJDK 7 (7u25/IcedTea 2.3.10)

How to repeat:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

class DateTest {
   public static void main(String[] args) throws Exception {
       String url = "jdbc:mysql://127.0.0.1:3306/test?useLegacyDatetimeCode=false";
       String userName = "root";
       String userPassword = "";
       
       Connection conn = DriverManager.getConnection(url, userName, userPassword);
       
       Statement st = conn.createStatement();
       st.execute("drop table if exists date_test");
       st.execute("create table date_test ( x date )");
            
       PreparedStatement pst = conn.prepareStatement("insert into date_test values (?)");
       java.sql.Date date = java.sql.Date.valueOf("2013-11-20");
       System.out.println("Storing java.sql.Date "+date);
       pst.setDate(1, date);
       pst.execute();
       
       ResultSet rs = st.executeQuery("select x from date_test");
       rs.next();
       System.out.println("Got back java.sql.Date: " + rs.getDate(1));
   }
}

Prints output:

Storing java.sql.Date 2013-11-20
Got back java.sql.Date: 2013-11-19

Also, the date stored in the database (as shown in "mysql" cli) is 2013-11-19.

Hi Pasi,

Thank you for this bug report, we are analyzing this behavior. Meanwhile you may want to try the method PreparedStatement.setDate(int parameterIndex, Date x, Calendar cal) with a client's Calendar instance and see how it works for you.

If I replace the setDate line with "pst.setDate(1, date, java.util.Calendar.getInstance())", the date is stored correctly as 2013-11-20.

Hi Pasi,

Regarding this issue JDBC is aware of these requirements and that's why it provides overloaded methods in PreparedStatement and ResultSet for date-time values that take calendars which specify the timezone to be used. Programmers are encouraged to use those.

While useLegacyDatetimeCode default mode 'true' doesn't modify temporal values, when it is 'false' the methods (set|get)Date and (set|get)Timestamp without Calendar argument, from PreparedStatement and ResultSet objects, perform time correction between client and server time zones for DATE, TIME and DATETIME data types. This allows 'normalizing' date-time values from clients in different time zones to a common time zone - the server time zone, and keep these date-time types symmetric to TIMESTAMP data type which is converted between session and server timezones by the server. When a client retrieves those values back it "de-normalizes" them to its own time zone. This feature also prevents duplication of date-time values when DST to non-DST switches occur.
Another issue you must be aware of is that, when useLegacyDatetimeCode=false, the methods get/setDate() without Calendar argument not only perform the time zone correction but also truncate time info, so as in your example:
- '2013-11-20' >(tz correction)> '2013-11-19 23:00:00.0' >(truncate)> '2013-11-19' > stored in server.
- get from server '2013-11-19' >(tz correction)> '2013-11-19 1:00:00.0' >(truncate)> '2013-11-19'
The similar methods that take a Calendar argument overrides the automatic time zone translation described above.

To get more information about time zone support please read http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html .

I will close this bug report as "Not a Bug". Feel free to reopen it or file another if you find something behaving incorrectly.

Thank you,

According to JDBC specification for PreparedStatement.setDate, the method without Calendar "Sets the designated parameter to the given java.sql.Date value using the default time zone of the virtual machine that is running the application." 

Since Calendar.getInstance() returns the default time zone, setDate(i, d) and setDate(i, d, Calendar.getInstance()) should behave identically. Which they don't -- so I think this is a bug.

Also, I discovered that this actually works correctly (same date is returned) when useServerPrepStmts=true. Thus, it is possible that this is actually the same bug as 46099.

Please read my last comment again.

Consider, for example, the following cases:

1. If what you need is to store date-time values without any kind of conversion then you should use useLegacyDatetimeCode=true (default behavior if property is not set), but keep in mid that MySQL doesn't store time zone information in temporal values so, when a DST switch occur, you may get duplicate values.

2. If what you need is to be protected against date-time duplicated values due to DST switches for example, then you should use useLegacyDatetimeCode=false (or some other kind of control in your application). This way, all date-time values are translated to a non-DST aware time zone, and thus eliminating possible duplicates.

This is one of the reasons for the useLegacyDatetimeCode=false logic.

Note that setDate(i, d) and setDate(i, d, Calendar.getInstance()) behave the same way when client and server area aligned in the same time zone.

The time zone conversion makes sense SQL TIMESTAMP, DATETIME and TIME types, which include a time component (which occurs in some time zone). They also behave sensibly: if a client writes a java.sql.Time or java.sql.Timestamp to the database and reads it immediately back (using the same client time zone and the same Connector/J settings), it gets back the same value (except perhaps during DST switches). 

But this bug is only about SQL DATE type, which doesn't include any time component, it's just plain year/month/day. Conceptually, you can't really  add or subtract one hour to plain date, so time zone setting should not affect SQL DATEs at all. But it seems some parts of Connector/J are treating SQL DATE as a TIMESTAMP/DATETIME with 00:00:00 midnight, probably because of historical reasons, which leads to this bug/bugs.. (if only Java had had Joda-Time's distinction between DateTime and LocalDate from the beginning...)

Also, having different behavior with useServerPrepStmts=true cannot be the intended behavior...

Hi Pasi,

We will investigate this issue further on. For the time being we will consider it as "verified" for DATE type.
We are aware of related problems with useServerPrepStmts=true too.

Thanks again for your input.

Added the following entry to the Connector/J 5.1.35 changelog:

"A java.sql.date value was stored incorrectly on the server and also returned incorrectly if the client and the server were in different time zones when useLegacyDatetimeCode=false or useTimezone=true. This was due to the time-zone conversion performed by Connector/J on the SQL DATE type. To avoid the issue, a new property noTimezoneConversionForDateType has been created for Connector/J, which is set to “true” by default, preventing Connector/J to perform the kind of time-zone conversion that caused this bug.

In addition, another new property cacheDefaultTimezone has been created: when it is set to “true” (by default), Connector/J caches the time zone first obtained from the client and uses it throughout the time the application is running. When it is set to “false,” Connector/J becomes aware of time zone changes in the client at runtime that are initiated by, for example, calling java.util.TimeZone.setDefault(zone)."

Edited the entry in the Connector/J 5.1.35 changelog for this bug:

"A java.sql.date value was stored incorrectly on the server and also returned incorrectly if the client and the server were in different time zones when useLegacyDatetimeCode=false or useTimezone=true. This was due to the time-zone conversion performed by Connector/J on the SQL DATE type. To avoid the issue, a new property noTimezoneConversionForDateType has been created for Connector/J, which is set to “true” by default, preventing Connector/J to perform the kind of time-zone conversion that caused this bug.

In addition, another new property cacheDefaultTimezone has been created: when it is set to “true” (by default), Connector/J caches the time zone first obtained from the client and uses it throughout the time the application is running. When it is set to “false,” Connector/J becomes aware of time zone changes in the client at runtime that are initiated by calling java.util.TimeZone.setDefault(zone)."

is this issue still present in latest server(5.6,5.7.8)?using  8 connector

Hi, we have same problem using spring-boot 2.x.x. We downgraded mysql-conector to 5.1.48 version.

Is this issue fixed in any version of 8.x MySQL Connector.

We recently migrated from Mysql 5.7 to Mysql 8.0.18

This issue started occurring for us in Mysql 8.0

It was working in 5.7 using 5.1.48 Connector