MySQL Bugs: #92153: Time not converted into the JVM's time zone when useLegacyDatetimeCode=false

Bug #92153	Time not converted into the JVM's time zone when useLegacyDatetimeCode=false
Submitted:	23 Aug 2018 12:13	Modified:	28 Sep 2018 15:12
Reporter:	Andrey Shcheglov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.46	OS:	Debian (Linux unit-725 4.9.0-5-amd64 #1 SMP Debian 4.9.65-3+deb9u2 (2018-01-04) x86_64 GNU/Linux)
Assigned to:	Filipe Silva	CPU Architecture:	x86 (x86_64)
Tags:	gettimestamp, settimestamp, time zone, timestamp, useLegacyDatetimeCode

Description:
MySQL Connector/J 5.1.46 doesn't convert time from the server time zone to the JVM's time zone if useLegacyDatetimeCode=false.

The attached JUnit test case covers two scenarios:

1. Local time is stored to the database as a TIMESTAMP, and then only the time fraction is read from the database (as a TIME). The time fraction read is expected to be converted to the time zone of the JVM.

2. Server time (current_time()) is read from the database (as a TIME). The time fraction read is expected to be converted to the time zone of the JVM.

This doesn't happen (there's no time zone conversion) unless useLegacyDatetimeCode=true is set (the workarounds are described below).

How to repeat:
1. Set the JVM's time zone to UTC (already present in the Java code)
2. Set the server time zone (in my.cnf) to MSK (Europe/Moscow), which is UTC+3
3. Make sure MySQL server's and JVM's clocks are synchronized (either use NTP or run them on the same machine).
4. Run the attached JUnit test case.

Java 1.8, JUnit 4 and MySQL Connector/J 5.1.46 are the only requirements.

Suggested fix:
There're two workarounds:

- either upgrade to MySQL Connector/J 8.0 (it passes the test just fine),
- or use the following combination of connection properties with 5.1.46:

  useLegacyDatetimeCode=true
  useTimezone=true
  noTimezoneConversionForDateType=false
  noTimezoneConversionForTimeType=false

  (this is the only combination proven to fully support different time zones in 5.1.46).

The JUnit 4 test covering the scenarios described

Attachment: TimeZoneTest.java (text/x-java), 9.83 KiB.

Eclipse JUnit runner with test results

Attachment: eclipse-junit.png (image/png, text), 25.58 KiB.

Hi Andrey,

Thank you for taking the time to report this.

Please try with latest Connector/J 5.1 (version 5.1.47). This version contains a fix for Bug#72609 where the code that handles this was updated. It should now do what you are expecting.

A warning, though. Mind that time zone adjustments are very complex and it's easy to make mistakes, especially when mixing data types with different temporal precision. For example, in the scenario you are exploring, suppose that the client's time zone is UTC and the server is in Europe/Paris. Storing temporal data as a TIMESTAMP adjusts it by +2 or +1 hours depending on if the value is within the DST window or not. When reading the same data with `getTimestamp()` the opposite adjustments are made, but reading the same with `getTime()` adjusts the tz -1 hour always, because that's the offset on 1970-01-01 in Europe/Paris. When doing the same operation with TIME data, the adjustments are +1 and -1 only because they are both made as if the time value was taken from 1970-01-01.

Please confirm if this matches your expectations.

Thanks,

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".