MySQL Bugs: #16104: Need config and clear intructions on uniform dates (TZ) from getString/getTimes

Bug #16104	Need config and clear intructions on uniform dates (TZ) from getString/getTimes
Submitted:	30 Dec 2005 17:02	Modified:	31 Mar 2014 10:43
Reporter:	Ken Johanson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	any	OS:	Any (any)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
Using any driver version listed on the mysql-j site, and recommended config combinations of ?useTimezone=true and/or noDatetimeStringSync=true, I cannot get String and Timestamp values to be correct. (NOTE: The following test is with mysql-server system clock/TZ set to GMT, and JDBC client set to non-GMT):

"SELECT NOW(), LEFT(NOW(),50)"
rs.next();
System.out.println(rs.getString(1));
System.out.println(rs.getTimestamp(1));
System.out.println(rs.getTimestamp(1).getTime());
System.out.println(System.currentTimeMillis());
System.out.println();
System.out.println(rs.getString(2));
System.out.println(rs.getTimestamp(2));
System.out.println(rs.getTimestamp(2).getTime());
System.out.println(System.currentTimeMillis());

In all cases the above code should yield String values equal to GMT wall clock, and Timestamps whos internal millisecond value is equal to System.currentTimeMillis(); these expectations cannot be realized.

How to repeat:
Exec above code, with mysql server set to GMT, and client with non-GMT, using any combo of useTimezone=true and/or noDatetimeStringSync=true.

I did get a 2005-12-30 mysql-j build working, but still am having just one problem - that Timestamp.getTime()'s internal millis value is offset by (zero - the client timezone offset), instead of equaling System.currentTimeMillis()... this presumably was done (inadvertently) to make java.util.Date.toString() yield a date whos value matches GMT wall clock (except for the timezone).

I have also tried using serverTimezone=GMT in addition to useTimezone=true& and noDatetimeStringSync=true; no difference.

The following code:
ResultSet rs = "SELECT NOW(), LEFT(NOW(),50)";
rs.next();
out.println(rs.getString(1));
out.println(rs.getTimestamp(1).toString());
out.println(rs.getTimestamp(1).getTime()/1000);
out.println(System.currentTimeMillis()/1000);
out.println(new java.util.Date(rs.getTimestamp(1).getTime()));
out.println();
out.println(rs.getString(2));
out.println(rs.getTimestamp(2).toString());
out.println(rs.getTimestamp(2).getTime()/1000);
out.println(System.currentTimeMillis()/1000);

Currently yields:
2005-12-30 18:34:34
2005-12-30 18:34:34.0
1135996474
1135967674
Fri Dec 30 18:34:34 PST 2005

2005-12-30 18:34:34
2005-12-30 18:34:34.0
1135996474
1135967674

But should be (for a PST client):
2005-12-30 18:34:34
2005-12-30 18:34:34.0
1135967674
1135967674
Fri Dec 30 10:34:34 PST 2005

2005-12-30 18:34:34
2005-12-30 18:34:34.0
1135967674
1135967674

May I recommened, upon fixing this, adding a prominately displayed section to the docs that covers common uses, and discusses each of the three configs. 

Above all, it is IMPERATIVE to emphasize that a config or updated driver be used, that ALWAYS produces a Date object who's internal millis value is correct for the actual point in time, even if the user prefers a config whos String value reflects their timezone. However one should be encouraged to always config/regard String values as GMT since mysql currently isn't supporting fully qualified timezones; NOT treating string values as implicit GMT will cause tremendous legacy problems when the same or another programmer eventually has to recode an application to treat timezones/offsets correctly. (for example two different timezone clients connecting to one server, but storing atomic date values but with different values - VERY, VERY bad)

Also, PreparedStatement always applies the TZ offset, when it should not. 

Here is a more complete litmus test:

try {
	setData("CREATE TABLE testdatetime (dt datetime, ts timestamp)");
	setData("INSERT INTO testdatetime VALUES (NOW(), NOW())");
} catch (Exception ex) {
//table exists
}
java.util.Date DATE = new java.util.Date();
setData("UPDATE testdatetime SET dt=NOW(), ts=NOW()");
ResultSet rs;
rs = getData("SELECT NOW(), LEFT(NOW(),50), dt, ts FROM testdatetime");
rs.next();
out.println(rs.getString(1));
out.println(rs.getTimestamp(1).toString());
out.println(rs.getTimestamp(1).getTime()/1000);
out.println(System.currentTimeMillis()/1000);
out.println(new java.util.Date(rs.getTimestamp(1).getTime()));
out.println();
out.println(rs.getString(2));
out.println(rs.getTimestamp(2).toString());
out.println(rs.getTimestamp(2).getTime()/1000);
out.println(System.currentTimeMillis()/1000);
out.println();
out.println(rs.getString(3));
out.println(rs.getTimestamp(3).toString());
out.println(rs.getTimestamp(3).getTime()/1000);
out.println(System.currentTimeMillis()/1000);
out.println();
out.println(rs.getString(4));
out.println(rs.getTimestamp(4).toString());
out.println(rs.getTimestamp(4).getTime()/1000);
out.println(System.currentTimeMillis()/1000);

out.println();
//your wrapped prepared statment:
setData("UPDATE testdatetime SET dt=?, ts=?", new Object[]{DATE, DATE});
rs = getData("SELECT dt, ts FROM testdatetime");
rs.next();
out.println(rs.getString(1));
out.println(rs.getTimestamp(1).toString());
out.println(rs.getTimestamp(1).getTime()/1000);
out.println(System.currentTimeMillis()/1000);
out.println();
out.println(rs.getString(2));
out.println(rs.getTimestamp(2).toString());
out.println(rs.getTimestamp(2).getTime()/1000);
out.println(System.currentTimeMillis()/1000);

DISREGARD ABOVE:

I just verified that Time, Date, and Timestamp (which are concrete) have an internal toString impl who's normal behavior is to apply the TZ offset, just as does java.util.Date.toString()... though one could argue (off topic) that that spec should have used GMT values in absense of the TZ indicator which ISO recommends- or at least explicitly specified (the sepc makes no mention at all) that local offset will be implied.

So, the mysql-j driver IS working properly using useTimezone=true, except now apparenty, since Timestamp.toString() on a datetime value should return a local offset string value, which implies that ResultSet.getString() should also apply the same offset, which it currently does not.

In the above litmus test, NOW()->ResultSet.getString() is returning a GMT wallclock value.

PreparedStatement of a String value that will be applied against a datetime field is also not reverse applying an offset, though I dont know if you can pre-determine the target field type.

Sorry...I was unable to reproduce the my on my test case. Could you please provide sources of setData() and getData() methods.

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

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.