Bug #84909 ODBC ts function getting time wrong
Submitted: 9 Feb 2017 13:58 Modified: 13 Mar 2017 13:17
Reporter: James Ots Email Updates:
Status: No Feedback Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:6.0.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[9 Feb 2017 13:58] James Ots
When I use the ODBC ts timestamp function, the time returned has 6 hours added to it, compared to using the TIMESTAMP function.

The global and session time zones are both set to SYSTEM. The local and server timezones are both set the UTC.

If I make the same queries directly on the server (using the mysql command line programmes), both 'ts' and 'TIMESTAMP' return the same values.

I also get the correct results when I use v5.1.40 of the connector.

(This is in a Spring 1.4.3 application, using a JdbcTemplate. If I query through the JdbcTemplate instead of directly though the connection, the 'TIMESTAMP' function subtracts 6 hours from the timestamp!).

How to repeat:
ResultSet results = connection.prepareStatement("select { ts '2016-02-01 00:00:00.0'}, TIMESTAMP'2016-02-01 00:00:00.0'").executeQuery();
results.getString(1) + ", " + results.getString(2)

Result: 2016-02-01 06:00:00.0, 2016-02-01 00:00:00.0
Expected Result: 2016-02-01 00:00:00.0, 2016-02-01 00:00:00.0

(Result using JdbcTemplate: 2016-02-01 06:00:00.0, 2016-01-31 18:00:00.0)
[13 Feb 2017 12:30] Chiranjeevi Battula
Hello James,

Thank you for the bug report.
Could you please provide us connection string(- please make it as private if you prefer) to confirm this issue at our end?

[13 Feb 2017 12:46] James Ots
I'm afraid the connection string is a very boring "jdbc:mysql://covdb1b/testdb".
[13 Feb 2017 13:17] Chiranjeevi Battula
Hello James,

Thank you for the feedback.
Please recheck once there must be a difference between server and client time zones and to avoid those inconsistencies you can use server time zone property in connection string.

[15 Mar 2017 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".