MySQL Bugs: #92453: CURTIME() get inconsistent result with NOW()

Bug #92453	CURTIME() get inconsistent result with NOW()
Submitted:	17 Sep 2018 6:38	Modified:	20 Sep 2018 10:36
Reporter:	Daniel Qian Qian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.12	OS:	MacOS
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	timezone

Description:
Set JVM timezone to Europe/Paris.

Execute sql to get result of CURTIME() and NOW(). Result is inconsistent(notice the hour part): 

Test CURTIME()	: 07:30:04
Test NOW()		: 2018-09-17 08:30:04.0

Seems like CURTIME() get a UTC offset result and NOW() get a UTC DST offset result.

While this problem doesn't appear when using MySQL client:

SET time_zone = 'Europe/Paris';
select curtime(), now();

References:

List of tz database time zones][1]

[1]: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

How to repeat:
Here is a rough test code:

TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));
jdbcTemplate.queryForObject("select CURTIME()", Time.class);
jdbcTemplate.queryForObject("select NOW()", Date.class);

BTW, JVM OS is in Asia/Shanghai timezone.
MySQL server is in UTC timezone (run by docker).

Hi Daniel,

Thank you for taking the time to report this.

First mind that when you use the MySQL client, this is as if you were physically on the server running those queries, so consulting CURTIME() or NOW() will show you those values as observed from there, i.e., system time is adjusted to the time zone set in the server and, in your case, UTC.

As you know, the same kind of temporal data adjustments occur between a client using Connector/J and a MySQL server. But there is a significant difference on how Connector/J handles TIME values, i.e., while in the server CURTIME() just cuts the time part from the current instant (wall time), in the client this is just a TIME value (hh:mm:ss) and it is translated to as milliseconds elapsed since EPOCH (as per https://docs.oracle.com/javase/8/docs/api/java/sql/Time.html), making it impossible for the driver to know if that time value was taken from a date within DST or not.

Taking your case for example:

1. Since the client is in time zone Europe/Paris and the server in UTC, this tells me that the date and time in the server is "2018-09-17 06:30:04.0" (-2 hours when compared to time in Paris while observing DST).

2. The data in the wire returned from the query "SELECT CURDATE(), NOW()" would then be something like: "06:30:04|2018-09-17 06:30:04" (for simplicity just consider the char '|' as a field separator).

3. While getting the first value as TIME, the connector reads it as "1970-01-01 06:30:04 UTC" and gets the corresponding milliseconds since EPOCH to build a java.sql.Time object. When you print it, you see a value adjusted to the client timezone (Europe/Paris) on the corresponding date, i.e., on 1970-01-01, and it was UTC+1, so it shows "07:30:04".

4. While getting the second value as DATE, the connector reads it as "2018-09-17 06:30:04 UTC", gets corresponding milliseconds since EPOCH and builds a java.sql.Date object from it. But this time the date part tells the client the exact time zone rule to use in the adjustments when printing out the date, so the final value is shown as "2018-09-17 08:30:04.0", which corresponds to the adjustment of +02:00 that is observed in Paris on that date.

Having said that, you can't really trust on TIME data when it is generated on the server side. Sending TIME data from the client to the server and then retrieving it back works just fine because opposite adjustments are made in both data flows. But mind that using different clients in different time zones to save and fetch temporal data will result in different TZ adjustments.

Finally, you always have the option to cancel time zone adjustments by using the ResultSet methods that take a Calendar instance or by setting the connection option "serverTimezone". In this example you could use a Calendar instance for time zone "Europe/Paris" or set "serverTimezone=Europe/Paris" in the connection string. As result the temporal values would be interpreted as being this time zone instead of UTC, and no adjustments would be made.

I hope this clarifies the behavior you should expect from Connector/J.