[FRG-272] timestamps should not be converted to DateTimeUtil.defaultZone

[dynamobi-build]

[reporter="mberkowitz", created="Wed, 23 May 2007 12:39:11 -0500 (GMT-05:00)"] In some cases runtime routines convert a timestamp from UTC to a "default timezone",
using org.eigenbase.util14.DateTimeUtil.defaultZone, which is initialized to the java default
timezone. This affects AbstractResultSet and SqlDateTiimeWithoutTZ.

This behavior seems incorrect:

• sql timestamps are zoneless, so AbstractResultSet.getTimestamp(int col) should
  return a "zoneless value", which is naturally represented as a Timestamp whose
  getTime() matches the timestamp value as UTC (no zone shift).
  
  
• ResultSet.getTimestamp(int col, Calendar null) is spec'd to convert to the caller's default zone,
  which is more likely the zone of the client session,not the server.
  
  
• in a distributed system there is no single well-defined server timezone
  
  
• due to DST, the server timezone offset depends on the current date, not the value of the timestamp
    being shifted.
  
  Why not remove DateTimeUtil.defaultZone and always keep timestamps as UTC on the server?
  To support ResultSet.getTimestamp(int, Calendar null), need to provide the client timezone as a
  session attribute -- cannot assume it is the same as the server zone.
  
  
   

[dynamobi-build]

[author="jvs", created="Wed, 23 May 2007 13:08:20 -0500 (GMT-05:00)"] I'm going to ask John Pham to comment on this one; here's his doc:

http://docs.eigenbase.org/FarragoDatetimeTypes

John did a lot of research on SQL:2003 and JDBC to come up with the existing semantics. The option of keeping timestamps as UTC on the server was explicitly rejected for the standard TIMESTAMP type because it contradicts SQL:2003; hence zoneless. (See the end of the doc regarding "Timestamp with Local Time Zone".) Addressing this would be an enhancement as an optional extension type to the standard, as with Oracle (i.e. it's not a bug as is).

For the JDBC issues, I'm not quite sure about the state. John's doc shows the Java client dealing with a client time zone, but I don't think this is actually implemented by an existing Farrago JDBC client (i.e. it assumes client and server timezones are the same).

[dynamobi-build]

[author="mberkowitz", created="Mon, 28 May 2007 14:55:49 -0500 (GMT-05:00)"] I didn't mean to suggest that timestamp values be converted to UTC. On the contrary I want them not
to be converted at all, but stored as supplied: egwhen passed from jdbc, store the value of Timestamp.getTime(), and if passed as a literal, convert to an equivalent value (units: msecs since unix epoch)
with no timezone translation (as if the client timezone were UTC).

However, the current farrago code translates timestamp values to server default timezone and then back again
-- which can produce an odd result if the value straddles the transition from standard to summer time:

0: jdbc:farrago:> create schema foo;
0: jdbc:farrago:> set schema 'foo';
0: jdbc:farrago:> create table t (s varchar(32) primary key, d timestamp);
0: jdbc:farrago:> insert into t values
                ('1970-01-01 00:00:00', TIMESTAMP '1970-01-01 00:00:00');
0: jdbc:farrago:> insert into t values
                ('2007-03-11 01:00:00', TIMESTAMP '2007-03-11 01:00:00');
0: jdbc:farrago:> insert into t values
                ('2007-03-11 02:00:00', TIMESTAMP '2007-03-11 02:00:00');
0: jdbc:farrago:> insert into t values
                ('2007-03-11 03:00:00', TIMESTAMP '2007-03-11 03:00:00');
0: jdbc:farrago:> insert into t values
                ('2007-03-11 04:00:00', TIMESTAMP '2007-03-11 04:00:00');
0: jdbc:farrago:> select * from t;
'S','D'
'1970-01-01 00:00:00','1970-01-01 00:00:00.0'
'2007-03-11 01:00:00','2007-03-11 01:00:00.0'
'2007-03-11 02:00:00','2007-03-11 03:00:00.0' << ouch
'2007-03-11 03:00:00','2007-03-11 03:00:00.0'
'2007-03-11 04:00:00','2007-03-11 04:00:00.0'
5 rows selected (0.016 seconds)

This is timezone dependent, as there is no such time as 2am 3/11/1007 in PST (or any US timezone),
but not so in other countries. I claim farrago should store "2007-03-11 02:00" as is.
Hence I suspect all code that refers to DateTimeUtil.defaultZone, namely
ZonelessDatetime, SqlDateTimeWithoutTZ, and AbstractResultSet.

A related issue is that in AbstractResultSet, getTimestamp(int col) is equivalent to
getTimestamp(int col, Calendar null). getTimestamp(int col) should return the data as is, unshifted.
The jdbc javadoc suggests that getTimestamp(int, Calendar cal) interprets the value as if stored with the zone of cal; it's plausible that getTimestamp(int, null) means use the default Calendar.
I couldn't find this issue in the jdbc 1.4 spec, and it appears that various jdbc implementations differ:
[cf discussion in http://bugs.mysql.com/bug.php?id=7818]

[dynamobi-build]

[author="jvs", created="Mon, 28 May 2007 18:27:44 -0500 (GMT-05:00)"] Got it. At first I couldn't reproduce the TIMESTAMP literal problem, but that's because I didn't patch my OS correctly this year; I just tried it with April Fools' Day and the shift repros as expected.

For the JDBC API issue, since Sun has left us all wondering, it seems like we need a survey of as many major JDBC drivers as possible, with the behavior for both getTimestamp(int) and getTimestamp(int, null), plus whatever configuration parameters exist to control the behavior. Or maybe the JDBC TCK covers this case?

[dynamobi-build]

[author="jpham", created="Wed, 30 May 2007 23:49:16 -0500 (GMT-05:00)"] The problem seems to be in converting timestamps to strings.
As Mark mentioned, ZonelessTimestamp.toString() depends
on an intermediate Jdbc value. It should probably rely on a
GMT date formatter, along these lines:

    toString(DatetimeUtil.TIMESTAMP_FORMAT)
    // NOTE: alter format for correct precision when supported

ZonelessDate and ZonelessTime should probably be
updated too.