MySQL Bugs: #7818: ResultSet.getTimeStamp().getTime() returns (GMT millis + TZ millis)

Bug #7818	ResultSet.getTimeStamp().getTime() returns (GMT millis + TZ millis)
Submitted:	11 Jan 2005 19:53	Modified:	18 May 2005 3:43
Reporter:	Ken Johanson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	mysql-connector-java-3.0-nightly-2004102	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any

Description:
The internal mills representaion in ResultSet.getTimeStamp() seems to have a TZ (the local machine's) offset applied to it.

How to repeat:
SELECT NOW();
long dif = ResultSet.getTimestamp(1).getTime() - System.currentTimeMillis();
Systemout.println(dif/1000); // = 28000, the PST timezone offset

Note that the Mysql server is even running in the GMT timezone.

Suggested fix:
I suspect this is related to needing to correctly format the Date-overidden toString() method; that an artifical millis-offset is being applied instead of using DateFormat(Timezone, Locale) to override Date's built-in, local-tz formating(?)

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Please try a released version (like 3.0.16). Also, if you can state what you _expect_ to retrieve it would be helpful.

Timezone information is not applied to dates/times unless you either set 'useTimeZone=true' in your JDBC URL, or you use the getTimestamp() methods that take calendars as an argument.

I would argue that this IS a bug - the internal millis/nanos representation should always be uniform no matter what Calendar arg is passed in, or driver args are used. In other words Calendar/driver args should be used influence what toString() returns, NOT what millis/nanos is... Their intent---and the nanos default --seems to be swapped.

Consider the implication of the current driver behavior on the following examples

*java.util.Date d = (java.util.Data)rs.getTimeStamp(1); when used in a compare or format function...
*rs.getTimeStamp(1).getTime()-System.currentTimeMillis(); //age of a record (this is a hypotheical but possible; correct way would be use NOW() Timestamp columns from server)

There is _no_ manipulation of the value for timestamp unless you tell the driver to useTimezone=true. It is always assumed to be in the local timezone for legacy reasons (because MySQL hasn't always supported timezones).

If you want the value to be consistent (which is what it sounds like what you are explaining in a round-about way), you need to use 'useTimezone=true' in your JDBC URL.

Since you're not posting a full testcase, only snippets of code, I can't tell how you're creating these timestamps you are retrieving, or what your connection parameters are, sorry.

useTimezone=true should fix this if you're using Connector/J 3.0 (I just tested it).

As an asside, would a little section in the manual about how the driver handles times/timezones been helpful, rather than the description of the configuration parameter that's in the documentation, but sans-context?

Mark, my configuration is pretty barebones; I'll list all the setups here.

The Mysql server is running on Linux Fedora with no special startup options related to zone; howeve the OS is fongured to be natively on UTC, so no offsets are being applied to its Result when NOW() is called. And the JDC URL contains no extra arsg beside the database and credentials.

The client machine, a webserver, *is* running on a local timezone (PST/LosAngeles/America), so presumably this is where the native database Object -> java.util.Date parsing is *effecting* the offset (inadvertently perhaps, by virtue of using a Calendar-type constructor without applying a Timezone? I say this without knowing of the Mysql underlying protocol passes ISO Strings (zone-less obviously), or epoch values).

As for your comment about the offset needing to remain for legacy reasons, it's too hard to argue against legacy userss needs, and for changing the default behavior. This will just remain a gotcha from user transitioning from other DBs I suppose. The question is how long :-)

Best,
Ken

Mark, I'm sorry _ I missed your second post, the one made 5 minutes after the 11 Jan 10:15pm one...

Yes, some added docs would be helpful, perhaps describing what the server's native protocol is would help understanding the tz limitations - For example right now, I'm only supposing that the server passes an ISO dt string (without zone) -- instead of passing an epoch/millis/nano value? And a paragraph about how the zone info is passed from the server (or is it only taken from the local server?), and perhaps touching on how a server and client in different zones would behave (okay or not). -k

Mark, I just tried the 'useTimezone=true' driver params, and now the offset is doubled from 28000 to 57600 -- instead of becoming 0.. This is my mostly plain vanilla setup where only the client and server have different timezones (server OS is in UTC and client is PST/-8000)

ResultSet rs = stmt.executeQuery("SELECT NOW()");
rs.next();
out.println(System.currentTimeMillis() - rs.getTimestamp(1).getTime()); -5,7600,000

Anything else I should try?

Mark, disregard my last!!! I tested without putting the latest build back in (I pulled it out after initial testing so that my dev server had the same driver as my productions do - that old version is the baseline that all the servers run on at the moment - it had fixed some other bugs (Field memeory leak) and I need it desparately at the time). Sorry!!!! k

Mark, using the latest production driver :-] and the useTimezone=true option, I now get the correct value from getTime(), but toString() is now showing a value that is *also* changed by the tz offset.

What I'd expect to see is if I call NOW() on the server, it should return the gmt-based iso formated string, and getTime() should return the corresponding millis value (1970-01-01 00:00:00 == 0 ms). This is especially true since the server is operating on a UTC OS, and since I'd normally expect to communicate string-Dates with the server using its native zone since the protocol does support zones. 

In other words clients would uniformly enter epoch as '1970-01-01 00:00:00', not '1969-12-31 16:00:00' or whatever their zone is. Maybe another way to think of it is that since passing a zone value is ignored, GMT (+0000) *has* to be the default interpretation (this is prob. the iso reccomenedation?). Zone values would only be a further qualification.

Thoughts,
ken

Correction "I'd normally expect to communicate string-Dates with the server using
its native zone since the protocol does support zones" -> "...the protocol doesn't support zones"

My apologies, this fix is in 3.0.17 which hasn't been published yet, and the nightly builds seem to be out of date (have to check script output). 

I've just pushed a new build of 3.0.17 into the nightly build system. Please try the following build to see if it corrects your problem:

http://downloads.mysql.com/snapshots/mysql-connector-java-3.0/mysql-connector-java-3.0-nig...

Here's an example that illustrates the problem with have toString()'s value rolled by the zone amount:

ResultSet rs = stmt.executeQuery("SELECT NOW()");
rs.next();
String s = rs.getTimestamp(1).toString();
stmt.executeUpdate("UPDATE tbl SET dateAccessed = '"+s+"'");

tbl.dateAccessed is then offset by the tz offset:
"SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(dateAccessed) FROM tbl" -> 28800

I haven't tested, but wonder if the above DML problem would also apply to PreperedStmts.

I tried the va-3.0-nightly-20050111 build; toString is still showing times on my zone's offset (not utc).

Ken,

Unfortunately the JDBC specification is pretty vague about what happens with time values and timezones when the database doesn't support timezones per time type (which MySQL amongst other databases doesn't). 

Many third-party libraries and applications expect there to be equality between getTime(someTimeColumn) and getString(someTimeColumn), which is why we have the behavior we do.

If we fix this it would have to be a configuration parameter, as there are some (most) applications that expect this to be the case.

If you don't want to wait for a fix, you should be able to do a new String(rs.getBytes()) if you want the string representation, _or_ select the value concat'd with an empty string (so the type is no longer TIME-ish, like TIMESTAMP/DATETIME/DATE/TIME), and the driver should act as you expect for your application.

Thanks!!! Your suggestion about using getBytes works when used in combo with useTimezone=true; getTimeStamp().getTime() is correct and the new String(getBytes()) provides the utc equivalent String.

Yes, I can wait for the fix/ new config option, so let me know when you'd like me to test it. Thanks!! k

Mark, can you re-open this bug, as I think we may both be agreeing that a zone-less timestamp should be regarded as UTC, and yet the translation is not working as expected. On the basis that you could add the config param to the driver (or the server adds zone support). Or, do you have build with the config added in that I can test?

I'm having a similar problem.  I'm using connector v3.1.8.

I'm using ResultSet.getTime().getTime() and noticing that the millis are off by the difference in my client time zone and UTC.

The server is not using time zones and the useTimezone is not set (default false).

My understanding is that the default interpretation of a TIME column is elapsed time.  Therefore, I would expect the client to use UTC when converting the field.  (I also agree with the Feb 4 statement that UTC should be assumed by default for getTimestamp() when the server does not specify a time zone.)

I followed the code path from the getTime() function in ResultSet.java through to some code in TimeUtil.java.  It is clearly using the client's default time zone instead of UTC.  As such, it is not possible to simply use the millisecond result as an elapsed time.  Unfortunately, changing the behavior could break previous code which might rely on the current behavior.

The work around for the moment seems to require me to pass a UTC-based calendar along with all calls to getTime(), getDate(), and getTimestamp().

Regarding my previous comment.  As I'm not using useTimezone I can't work around the problem using getTime() with a Calendar.

Dam it (Hoover Dam), each time I gp to this page I get the outdated version (Dev '04) - not the latest alpha/beta:

http://dev.mysql.com/downloads/connector/j/3.2.html

Who do we beg for this to get repaired? Seems like it's been a year like this... Off topic but poiniant.

I just tried build mysql-connector-java-3.2-nightly-20050518, problem still occurs.

Enless there is a driver-config option tha I can try, could someone please re-open this bug? (It should be re-opened according to the [11 Jan 21:09] comment).

here is the current behavior, with a Mysql server operting in UTC, and Client operating in, say, the PST timezone:

ResultSet rs = Databases.getResultSet("mysql","select now()"); 
/*now()'s millisecond representation should capabale of being switched 'on' to switched be epoch (which 0 is based on GMT), since this is the fully correct behavior (epoch + zone is WRONG)*/
rs.next();
long dif = rs.getTimestamp(1).getTime() - System.currentTimeMillis();
/* System.currentTimeMillis() is also a UTC based epoch, so subtraction should equal zero */
out.println(rs.getTimestamp(1).toString());
out.println(dif/1000); // = 28000, the PST timezone offset (wrong, should be 0)

With this build, both getTime() and the toString() track each other's offset by the dif between the DB and Client (even with useTimezone=true) - so a -1 TZ subtracts one hour from both the getTime and the toString().

If I correctly understand the intent of useTimezone=true, then the getTime() function should ALWAYS return epoch (without zone offset), irregardless of what zone the DB is in, or what zone the client is in... but toString should continue to be the legacy behavior: ISO date-strings in the client's zone (implicit zone offset, e.g. 2005-01-01 01:23:24 -0800 == 2005-01-01 01:23:24).

Perhaps we need another config that controls the toString offset, while useTimezone is used for getTime().

> My understanding is that the default interpretation of a TIME column is elapsed
> time.  Therefore, I would expect the client to use UTC when converting the
> field.  (I also agree with the Feb 4 statement that UTC should be assumed by
> default for getTimestamp() when the server does not specify a time zone.)

Russell,

I'm not sure where you've seen that the default interpretation of "TIME" is elapsed (i.e. wallclock) time, the JDBC spec certainly doesn't say so (although it's not explicitly clear, either):

"A thin wrapper around the java.util.Date class that allows the JDBC API to identify this as an SQL TIME value. The Time  class adds formatting and parsing operations to support the JDBC escape syntax for time values.

The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed. "

The SQL2003 specification refers to the TIME type as either "local time" or "UTC", but never _elapsed_ time.

Ken,

3.2.0 _is_ the current latest release. There hasn't been a lot going on in it, since all of the major new features in MySQL-5.0 are supported by the 3.1.x, and 3.1.x is being actively maintained with bug fixes.

  -Mark

Ken,

I'm going to have do a bit more digging in the SQL spec. I've polled the JDBC eg members (Sybase, IBM, Oracle), and everyone handles this differently (no surprise there, since the JDBC spec isn't tight enough with respect to how the "session" (i.e. client) timezone relates to values that are retrieved for any datetime values.

However, until that's sorted out, I think the configuration option you want (present in 3.1.8 and the nightlies of 3.2.1) is "noDatetimeStringSync=true", which shouldn't apply timezone offsets when you use ResultSet.getString() on a DATE/TIME/TIMESTAMP column.

Mark, to be sure, version 3.2-nightly-20050518 should support the noDatetimeStringSync=true option, and that option is a JDBC-URL config param, right? If so, then its not changing the value returned by Timestamp.toString()/RS.getString().. that value still shows the local time, not UTC (ie not local time + utc offset). And with it set, I still see Timestamp.getTime() and getString() tracking each other.