MySQL Bugs: #5874: Timezone conversion goes in the wrong direction

Bug #5874	Timezone conversion goes in the wrong direction
Submitted:	4 Oct 2004 11:19	Modified:	23 Nov 2004 19:18
Reporter:	Philip Ross
Status:	Not a Bug
Category:	Connector/J	Severity:	S2 (Serious)
Version:	3.0.15-ga, 3.1.4-beta	OS:	Microsoft Windows (Windows XP)
Assigned to:	Eric Herman	Target Version:

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;

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.

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);