MySQL Bugs: #103144: TIME column problems with getObject() and setObject()

Bug #103144	TIME column problems with getObject() and setObject()
Submitted:	29 Mar 2021 13:15	Modified:	7 Apr 2021 10:12
Reporter:	Dario Menni	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Windows (10)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	connector, java

Description:
My local timezone has an offset of +02:00 in compared UTC due to daylight savings.

I already use getObject() and setObject() with OffsetDateTime to store and retrieve TIMESTAMPs on a server with UTC timezone, also specified in the connection url. It works fine, if the object has an UTC offset it's stored as is, while if the object has a local timezone offset it's converted and stored as UTC.

TIME however is problematic. If I use setObject(..., class) on an update where class is either OffsetTime or OffsetDateTime (with UTC zone offset) the time stored on the database is 1 hour less than my current local time and 1 hour more than UTC time (eg. local time is 15:00+02:00, on db it is 14:00, UTC is 13:00). 
When I retrieve the time using getObject(..., class) I get that the time is 14:00+01:00. 
If I use getObject(..., class) on now() I get 13:00+01:00, this is even more wrong.

I wanted to use LocalTime instead but I read of this bug: https://stackoverflow.com/questions/53725799/saving-a-localtime-in-mysql-time-column so I'm not really sure how I should proceed now 

How to repeat:
This is the java snipped I use

public class TestDBTime {
	
	public static void main(String[] args) throws SQLException {
		testOffsetDateTime();
	}
	
	private static void testOffsetDateTime() throws SQLException {
		Connection connection = DriverManager.getConnection(DB_Connection.url, DB_Connection.user, DB_Connection.password);

		Instant instant = Instant.now();
		
		// Offset date time
		OffsetDateTime offsetDateTime = OffsetDateTime.ofInstant(instant, ZoneId.systemDefault());
		System.out.println("Default offset date time from instant: " +offsetDateTime.toString());
		
		OffsetDateTime offsetDateTimeUTC = instant.atOffset(ZoneOffset.UTC);
		System.out.println("UTC.... offset date time from instant: " +offsetDateTimeUTC.toString());
		
		String update = "";
		try (PreparedStatement preparedStatement = connection.prepareStatement(update)){
			preparedStatement.setObject(1, offsetDateTimeUTC);
			preparedStatement.executeUpdate();
		}
		
		String query = "";
		try (PreparedStatement preparedStatement = connection.prepareStatement(query)){
			ResultSet resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			OffsetDateTime retrievedDateTime = resultSet.getObject("next_execution", OffsetDateTime.class);
			System.out.println("Offset date time retrieved: " +retrievedDateTime);
			System.out.println("Retrieved odt is equal to original UTC odt: " +retrievedDateTime.equals(offsetDateTimeUTC));
			
			OffsetDateTime retrievedNow = resultSet.getObject("now", OffsetDateTime.class);
			System.out.println("Retrieved now: " +retrievedNow);
		}
		System.out.println("**********************************");
		
		// Offset time
		OffsetTime offsetTimeUTC = OffsetTime.ofInstant(instant, ZoneOffset.UTC);
		System.out.println("Offset time UTC is: " +offsetTimeUTC);
		update = "";
		try (PreparedStatement preparedStatement = connection.prepareStatement(update)){
			preparedStatement.setObject(1, offsetTimeUTC);
			preparedStatement.executeUpdate();
		}
		
		query = "";
		try (PreparedStatement preparedStatement = connection.prepareStatement(query)){
			ResultSet resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			LocalTime retrievedLocalTime = resultSet.getObject("start_time", LocalTime.class);
			System.out.println("local time retrieved: " +retrievedLocalTime);
			
                        System.out.println("Offset time retrieved: " +resultSet.getObject("start_time", OffsetTime.class));

			OffsetDateTime retrievedOffsetTime = resultSet.getObject("start_time", OffsetDateTime.class);
			System.out.println("offset date time retrieved: " +retrievedOffsetTime);
			
			System.out.println("Now retrieved as offset time: " +resultSet.getObject("now", OffsetTime.class));
			System.out.println("Now retrieved as offset date time: " +resultSet.getObject("now", OffsetDateTime.class));
		}
	}
}

Console output is:
Default offset date time from instant: 2021-03-29T15:06:31.099+02:00
UTC.... offset date time from instant: 2021-03-29T13:06:31.099Z
Offset date time retrieved: 2021-03-29T13:06:31.099Z
Retrieved odt is equal to original UTC odt: true
Retrieved now: 2021-03-29T13:06:31Z
**********************************
Offset time UTC is: 13:06:31.099Z
Local time retrieved: 14:06:31
Offset time retrieved: 14:06:31+01:00
offset date time retrieved: 1970-01-01T14:06:31+01:00
Now retrieved as offset time: 13:06:31+01:00
Now retrieved as offset date time: 2021-03-29T13:06:31Z

You can see in the first part that OffsetDateTime is stored as TIMESTAMP and retrieved from a TIMESTAMP correctly.
In the second part however you can see that I set as object and UTC OffsetTime but then I retrieve time 1 hour between my local time and UTC time.
You can also see that retrieving now() as OffsetTime gives the correct UTC hour but with a wrong offset while OffsetDateTime retrieves now() correctly

I forgot to mention that if I store a LocalTime like this:
LocalTime localTimeUTC = LocalTime.of(offsetDateTimeUTC.getHour(), offsetDateTimeUTC.getMinute());
Then on the database the time stored is the correct UTC time (13:25), however when retrieved it still shows the wrong offset (13:25+01:00)

Hi Dario,

What is your Connector/J version? For c/J 5.1 series you could try to set noTimezoneConversionForTimeType=true, though I'm not sure it will work in this case.

The better solution would be to upgrade to c/J 8.0.23, we significantly reworked the date-time types support there. Please read https://insidemysql.com/support-for-date-time-types-in-connector-j-8-0/ about these changes.

You are right, I forgot to say the connector version, but I already am running version 8.0.23

Connector/J 8.0.23 considers that OffsetTime is not an instant date-time class because the date part is absent there. Also the MySQL TIME type is also considered a non-instant.
So no time zone preserving is attempted. OffsetTime value is converted to the local JVM time zone before sending to the server. When retrieved, it is constructed as LocalTime in a JVM time zone.

But, additionally, there is a specific with conversion of the OffsetTime value to the local JVM time zone. Since the date part is absent, there is no way to decide how to apply DST rules. We could base on the current date, but in this case the same OffsetTime value will be stored differently over the year. Currently implemented solution uses the raw offset of the JVM time zone, like the time belongs to 1970-01-01 date without DST applied. It also rises questions, for example you get the +01:00 shift instead of +02:00 because of that. But at least OffsetTime values are consistent over the year.

So, there is no universal solution about storing OffsetTime to MySQL at least until it does not support TIME_WITH_TIMEZONE SQL type. The question is what are the user expectation here. How do you expect an OffsetTime to be stored, according the current DST rules, or somehow else?

The working solution for OffsetTime is to use varchar column and call preparedStatement.setObject(1, offsetTimeUTC, MysqlType.VARCHAR). In this case the string value formatted with DateTimeFormatter.ofPattern("HH:mm:ss.SSSSSSSSSXXX") is stored to db and when restored with resultSet.getObject("start_time", OffsetTime.class)) it's parsed back to the original OffsetTime value.

I'd expect OffsetTime to work in one of these 2 ways:

1) like OffsetDateTime, the time in OffsetTime is converted to the server's timezone time. 15:00+02:00 is stored as 13:00

2) The time in OffsetTime is stored as is completely ignoring the offset. 15:00+02:00 is stored as 15:00

Right now the stored time isn't converted to the server's time but is converted to my local time but ignores daylight savings. When retrieving an OffsetTime the time I receive is the exact value as in the database but with a +1 offset (ignores daylight savings). Whereas OffsetDateTime respects daylight savings

Alternatively I'd open to completely give up on OffsetTime and use LocalTime, but I'd also need to receive confirmation that when storing LocalTime with setObject() and retrieving with getObject() the hours and minutes aren't converted at all but are kept exactly as they are

LocalTime is never converted between time zones when stored to TIME or (VAR)CHAR columns. The only possible issue could be if you retrieve the TIMESTAMP value as LocalTime because of server-side implicit TIMESTAMP conversion to the session time zone, but that's a corner case.

I leave this report as verified, we need to figure out the solution regarding OffsetTime <-> TIME conversion which better fits the user expectations.

Thanks!