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