Bug #5874 Timezone conversion goes in the wrong direction
Submitted: 4 Oct 2004 9:19 Modified: 23 Nov 2004 18:18
Reporter: Philip Ross Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.0.15-ga, 3.1.4-beta OS:Windows (Windows XP)
Assigned to: Eric Herman CPU Architecture:Any

[4 Oct 2004 9:19] Philip Ross 
Description:
I have my JDBC URL set with useTimezone=true&serverTimezone=UTC.  The client is running in the timezone Europe/London. I am saving a Timestamp value in BST (GMT+1) using a PreparedStatement.  If I call setTimestamp with a timestamp representing 2004-10-04 09:00 (08:00 UTC), it gets saved to the database (appears in the generated SQL) as 2004-10-04 10:00.

The TimeUtil.changeTimezone function appears to be where the trouble lies.  This correctly determines an offsetDiff of one hour between fromTz and toTz, but then adds this to the time to be converted rather than subtracting it.

How to repeat:
Run with the local timezone set to something other than UTC.  Open a connection with useTimezone=true and serverTimezone=UTC.  Use a PreparedStatement to insert a Timestamp.  In converting the Timestamp to UTC, the offset will be added instead of subtracted.

Suggested fix:
Change the line:

toTime += offsetDiff;

in TimeUtil.changeTimezone() to:

toTime -= offsetDiff;
[23 Nov 2004 18:18] Mark Matthews 
As far as I we can tell, this isn't a bug, because it's consistent with what the JDBC spec requires, and with what other vendors do (the '+=' offset bit actually comes from another bug report, where we go in this direction to be JDBC compliant and like other vendors, see http://bugs.mysql.com/bug.php?id=3620)

If your case doesn't match BUG#3620 (please look at the entire thread, and determine if you agree or disagree with it's conclusion), or if you believe this conclusion to be in error, please re-open this bug.
[26 Nov 2004 16:34] Eric Herman 
Another quick note, when retrieving Timestamp values, it is usually best to use ResultSet's "getTimestamp" method:

        ResultSet rs = this.stmt.executeQuery("SELECT * from tableBug5874");
        while (this.rs.next()) {
            // correct:
            Timestamp retrTimestamp = this.rs.getTimestamp(1);
            // usually incorrect:
            String retrTimestampString = this.rs.getString(1);