Bug #74016 java.sql.Time improperly handling current_time
Submitted: 22 Sep 2014 15:57 Modified: 14 Feb 2016 10:41
Reporter: CHRISTOPHER Floersch Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.32 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: Time timezone

[22 Sep 2014 15:57] CHRISTOPHER Floersch
Description:
When using jdbc to execute the query select current_time() from a mysql system setup using US/Eastern timezone I get back a java.sql.Time object with a getTiem() value of 74247000. That of course directly reverts back to 20:37:27 if you assume it in UTC. So the Eastern timezone value comes out to be 15:37:27 (We are currently in DST).

The problem is that it is actually 11:37:27 EDT.

When a human readable time is converted into millis it needs to be done using the correct timezone for the human readable form. If that had been done the getTime() method would have returned 59847000 millis.

That number would properly represent the current time in milliseconds since midnight UTC. Then converting it back to human readable time (and applying eastern timezone) would have produced the proper time.

How to repeat:
Take any mysql machine with any of the 5.1.x connectors and run the following code

    public void testSqlTime() throws Exception
    {
        System.out.println("SQL Time");
        Connection conn = createConnection();
        try {
            Statement stmt = conn.createStatement();
            try {
                ResultSet rs = stmt.executeQuery("select current_time()");
                try {
                    if (rs.next()) {
                        java.sql.Time date = rs.getTime(1);
                        System.out.println(date.getTime()); 
                        // converts to human readable using EDT
                        System.out.println(date); 
                    }
                } finally {
                    rs.close();
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    }

The values returned will NOT reflect the current time but will be off by whatever zone offset you use.

In out case the server is set as EDT and the client is set as EDT.

There are several connection properties which appear to solve this problem when it comes to timestamps. However, those connection properties do NOT work with Time objects for whatever reason.

The one connection property that did seem to impact Time was useTimezone=true. However, in that case it came up with 10:37:27 which is still wrong but wrong in a different way.

Suggested fix:
I am not sure exactly what form the data is coming across the wire in. But it would appear at some point you are taking a human readable time and attempting to convert it to the millis since X necessary for a java.sql.Time object.

The process needs to take into account the timezone under which the original human readable time was computed to get the proper number of millis.
[23 Sep 2014 10:38] Filipe Silva
Hello Christopher,

Thank you for this bug report.

Without any (time/date/timezone related) Connection properties the connector/J works as if each time value is represented by its absolute value, i.e. hh:mm:ss, and not in terms of milliseconds from 0:00:00 UTC.
Which is the same to say that if CURRENT_TIME() at server returns 15:37:27, then the client will display 15:37:27 too, no matter what time zones are set in client or server. This evidently means that the java.sql.Time.getTime() result depends on the time zone the client is (different milliseconds from 0:00:00 UTC in different time zones to get the same hh:mm:ss value).

But, if 'useTimezone=true' is used, then the driver computes the differences between server and client time zones offsets and uses it to adjust the time value.
Suppose for example two clients (A and B). Client A has a +2 hours difference from server (server is west of client A) and Client B has a -1 hour difference from server (server is est of client B). If at some instant in client A machine, say 10:30:00 AM, someone stores the local time in the server (using PreparedStatemens for example), it will be seen in the server as 8:30:00 AM. And if client B retrieves this record he will get 7:30:00 AM.
The same happens for your example. If at server's time zone it is 10:30:00 AM, then running a 'SELECT CURRENT_TIME()' query in client A will return 12:30:00 AM (+2 hours relative offset) and in client B 9:30:00 AM (-1 hour relative offset).

Nevertheless, if both client and server are at same time zone, then it is the same as if queries were run directly in the server machine from a mysql command line client, meaning that the same values should be seen as the same in both places, no matter if or what properties are set (except those that override time zone settings, e.g. 'serverTimezone').

Now, as for your report, things seem not to be working exactly as I described, but my guess is that you may be mixing up some settings I can't see from here. Can you check it out if there is something that may be influencing this? Or share your connection settings and your mysql server time zone settings with us if you need more help.

Thank you.
[24 Oct 2014 1:00] Bugs System
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".
[6 Jan 2016 18:07] Florian Beckmann
I have a simliar problem. Not sure if this is the same issue or even a bug.

I'm using JPA 2.1/JEE 7 and try to persist instances of java's Duration class  using an Attribute Converter, which converts to java.sql.Time  instances (which in turn are mapped to a MySQL column of type time by JPA/Hibernate) and vice versa. 
For a Duration of 5 minutes a value of "01:05:00" is stored in the DB. I don't have any special properties set regarding timezone.
MySQL System variables time_zone is set to "SYSTEM" and system_timezone to "CET".
I don't understand why conversion is happening here. 

Duration set for entity:
...
config.setDuration(Duration.ofMinutes(5));
...

The code for the converter:

package com.example

import java.sql.Time;
import java.time.Duration;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class DurationPersistenceConverter implements AttributeConverter<Duration, Time> {

	@Override
	public Time convertToDatabaseColumn(Duration duration) {
		return new Time(duration.toMillis());
	}

	@Override
	public Duration convertToEntityAttribute(Time time) {
		Duration duration = Duration.ofMillis(time.getTime());
		return duration;
	}

}
[14 Jan 2016 10:41] Filipe Silva
Hi Florian,

I need some more detail to be able to help you. Which versions of Connector/J and MySQL are you using? Which connection properties are you setting in your connection?

Thanks,
[15 Feb 2016 1:00] Bugs System
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".