MySQL Bugs: #80201: PreparedStatement.setTimestamp doesn't comply with JDBC specification

Bug #80201	PreparedStatement.setTimestamp doesn't comply with JDBC specification
Submitted:	29 Jan 2016 17:21	Modified:	21 Oct 2020 12:56
Reporter:	Stephen Halsey	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.36	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	gettimestamp, settimestamp, time zone, timestamp, useLegacyDatetimeCode

Description:
The PreparedStatement interface's setTimestamp method is documented at:-

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setTimestamp(int...)

void setTimestamp(int parameterIndex,
Timestamp x,
Calendar cal)
throws SQLException
"Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application."

I have the following setup:-
Client in Los Angelese time zone.
Server in New York time zone.

This means that:
- If the client sends a time to the server of 04:00 PST using setTimestamp(int parameterIndex, Timestamp x) the specification above says that the time that should be stored on the database should be 04:00 (i.e. the client Los Angelese time)
- If the client sends a time to the server of 04:00 PST with a Calendar set to UTC using setTimestamp(int parameterIndex, Timestamp x, Calendar cal) the specification above says that the time that should be stored on the database should be 12:00 (i.e. the UTC time).

All of the following databases behave in the way describes above in the specification:-
- Microsoft SQL server
- Oracle DB Server
- Derby DB Server

(haven't tested DB2, but would guess it also complies with the specification).

I'm afraid I cannot get mysql to behave in this way and I have tried a number of the different combinations of options described at:

https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.ht...

including:-
- useLegacyDatetimeCode
- useTimezone
- serverTimezone
- useJDBCCompliantTimezoneShift

If I don't set any options on the database connection url (i.e. useLegacyDatetimeCode stays as the default value of true) then:-
- If the client sends a time to the server of 04:00 PST with a Calendar set to UTC using setTimestamp(int parameterIndex, Timestamp x, Calendar cal) the specification above says that the time that should be stored on the database should be 12:00 (i.e. the UTC time) - but instead MySQL ignores the Calendar object and stores 04:00.

If I set useLegacyDatetimeCode=false on the connection url then storing UTC dates works but:-
- If the client sends a time to the server of 04:00 PST using setTimestamp(int parameterIndex, Timestamp x) the specification above says that the time that should be stored on the database should be 04:00 (i.e. the client Los Angelese time) - but instead MySQL used the server's time zone to store the time and so it is stored as 07:00.

I understand that this may be seen as am improvement over the JDBC spec because it means that the times from different client time zones are stored in the time of the server so that they are all consistent (I have read the explanation at https://bugs.mysql.com/bug.php?id=71084) but I am trying to write a system which uses the JDBC library and I hope to get it to work the same with all of the different databases - including the values stored in the databases.

Could you please let me know if there is a setting I can set that will give the behaviour described in the JDBC specification? If not then maybe it would be a good idea to provide a connection url option for switching on the JDBC compliant timestamp behaviour vs. leaving it as you have it now (which may be better, but is not compliant). This option could be called e.g. complyWithJDBCTimestampSpecification and default to false (current behaviour) and be over-riddable to true to make it work like other databases.

I am using MySql Server:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@@version' at line 1
mysql> select @@version;
+-------------------------+
| @@version |
+-------------------------+
| 5.5.47-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql>

and client mysql-connector-java-5.1.36-bin.jar

How to repeat:
With useLegacyDatetimeCode=true (i.e. default) send from LA configured client to NY configured server time of 04:00 PST using PreparedStatement.setTimestamp with Calendar set to UTC.
Expected time stored in DB = 12:00 (i.e. UTC time)
Actual time stored in DB = 04:00 (i.e. LA time)
TEST FAILS

With useLegacyDatetimeCode=false send from LA configured client to NY configured server time of 04:00 PST using PreparedStatement.setTimestamp without a Calendar.
Expected time stored in DB = 04:00 (i.e. LA time)
Actual time stored in DB = 07:00 (i.e. NY time)
TEST FAILS

Suggested fix:
Create new complyWithJDBCTimestampSpecification database connection url option which defaults to false (current behaviour) and if set to true makes above tests pass and means JDBC specification is followed accurately.

Users of Metabase (https://github.com/metabase/metabase) are experiencing this issue as well. While debugging Metabase to try and figure out why incorrect times were being specified as query parameters, what I found what a bug in how the timestamps are being shifted (the same setTimestamp call referenced in this ticket).

I think the bug is in this method: https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/TimeUtil.ja.... The time is being converted to the target calendar correctly I think. The last call though https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/TimeUtil.ja... calls getTime on a date, which I think is wrong. That call will convert the Date back to UTC (https://docs.oracle.com/javase/7/docs/api/java/util/Date.html#getTime()).

Users that have "useJDBCCompliantTimezoneShift=true" will always have UTC as their sessionCalendar (that happens higher up in the callstack, PreparedStatement.setTimestampInternal). So that code is taking a timestamp, setting it to a UTC calendar, converting it from the UTC calendar to the calendar the user provided (in my example, a calendar in the America/Chicago timezone) and then calling targetCalendar.getTime().getTime() just converts it from America/Chicago back to UTC. That UTC timestamp is then assumed to be in the correct timezone for the database. It converts it to a string (leaving off the timezone part) and sends it to the database https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/PreparedSta....

This issue has been open for a while. Would a patch be accepted that fixes this?

Users of Metabase (https://github.com/metabase/metabase) are experiencing this issue as well. While debugging Metabase to try and figure out why incorrect times were being specified as query parameters, what I found what a bug in how the timestamps are being shifted (the same setTimestamp call referenced in this ticket).

I think the bug is in this method: https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/TimeUtil.ja.... The time is being converted to the target calendar correctly I think. The last call though https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/TimeUtil.ja... calls getTime on a date, which I think is wrong. That call will convert the Date back to UTC (https://docs.oracle.com/javase/7/docs/api/java/util/Date.html#getTime()).

Users that have "useJDBCCompliantTimezoneShift=true" will always have UTC as their sessionCalendar (that happens higher up in the callstack, PreparedStatement.setTimestampInternal). So that code is taking a timestamp, setting it to a UTC calendar, converting it from the UTC calendar to the calendar the user provided (in my example, a calendar in the America/Chicago timezone) and then calling targetCalendar.getTime().getTime() just converts it from America/Chicago back to UTC. That UTC timestamp is then assumed to be in the correct timezone for the database. It converts it to a string (leaving off the timezone part) and sends it to the database https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/PreparedSta....

This issue has been open for a while. Would a patch be accepted that fixes this?

I just filed a similar Bug #90150: getString() retrieves bad DATETIME value when client, server time zones differ.  Based on my research so far, I think this bug (Bug #80201) may actually represent a misreading of the JDBC specification. 

The reporter of this bug (Stephen Halsey) states that the time zone conversion is to be skipped if no calendar is specified.  My reading is that the conversion is always to be done: the only question is which calendar is used.  If a calendar is specified in the call to setTimestamp(), then that calendar is used for the conversion; otherwise, the calendar for the current time zone of the Java client is used for the conversion.  Under this reading, the issue reported here is not a bug, but correct behavior.

Perhaps the assignee can comment on the correct reading of the spec.

Hi,

First of all, my apologies for the time this report was left unanswered.

This is quite a sensitive issue and there's no right or wrong way of "fixing" it.

It was decided long time ago that the default behavior in Connector/J was to not do any time zone adjustments by default, even when Calendars are used. This, however, is also not entirely true since there are some corner cases where TZ adjustments are being made.

In the newer implementation (useLegacyDatetimeCode=false) it was decided to apply TZ adjustments all the time, even though it is not clear how those are made in some cases too.

So, there are definitely buggy behaviors in both cases and we will revise them entirely. As for now, I'll mark this report as verified but it is still not defined what the final behavior should be.

Thanks,

Hi guys,

This functionality was reworked in Connector/J 5.1.47 and Connector/J 8.0.13 under Bug#72609 fix. Could you check if the problem still exists?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I finally had a chance to test the suggestion of using noDatetimeStringSync=true and I can confirm that this does resolve the issue demonstrated in the test case for Bug #90150: getString() retrieves bad DATETIME value when client, server time zones differ:

https://bugs.mysql.com/bug.php?id=90150

Thank you for following up on this!

I should clarify that I did my test on the older version of the JDBC driver agains which I filed Bug #90150:

mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq )

The use of noDatetimeStringSync=true is a good workaround for the the related issue described in Bug #90150; I did not have a chance to test the behavior of the newer driver versions with prepared statements.

This functionality will be reworked under Bug#95644. Closed as a duplicate.