Re: [PATCHES] Patch for JDBC timestamp problems

Let me answer the questions Thomas raises:

> I'm confused, as usual. How does forcing the client time zone to GMT fix
> the problem of mismatching client and server time zones? istm that you
> still have a problem if the server is not on GMT.

Java has an internal representation for a date the is a long storing
miliseconds since 1970, similar to unix. By using a date format object
you convert this long value into strings that make sense to humans.
Java also provides a SimpleDateFormat object that can format dates
according to the standard Gregorian calendar, but java also allows other
calendars/dateformat objects. Lets walk through a couple of examples:

Consider a java Date object with the underlying long value
of979419043128
Taking a SimpleDateFormat object and formatting this date to a String
with the format mask of "yyyy-MM-dd hh:mm:ss.SSSzzz" will result in:
2001-01-13 20:50:43.128GMT if the timezone setting for the
SimpleDateFormat object is GMT
2001-01-13 12:50:43.128PST if the timezone setting is PST
...

So back to the original bug, the original code was simply performing a
Timestamp.toString() which uses the following format mask to format the
date 'yyyy-MM-dd hh:mm:ss.SSS' (notice no timezone is included in the
format). So postgresql assumes the date value is in the DB server
timezone and essentially appends on that timezone. So looking further
at the example:
DB Server running with EST (GMT-05), jdbc running with PST (GMT-08)
given the date object above:
Timestamp.toString() would format it as '2001-01-13 12:50:43.128' the
server would receive this, see no timezone and assume the value was in
EST and treat it as '2001-01-13 12:50:43.128-05' which is not what the
client intended, and is the incorrect value, so when the client selects
back the date and converts it to PST it will have '2001-01-13
09:50:43.128PST', three hours different than it sent to the server.

So that was the problem, now to the solution. Java's SimpleDateFormat
object formats timezone information as either the three letter code
(i.e. PST) if the format mask has three or fewer z's in it, or as a full
name (i.e. Pacific Standard Time) if the format mask has four or more
z's in it. Whereas the ISO date format expected by postgres is of the
format +/-XX (i.e. -08 for PST). So there were two ways to code a
solution to this, a) figure out how to get from the java timezone object
the offset from GMT and append on that value for timezone, or b) append
on a known value (i.e. +00) for timezone, and set the timezone of the
SimpleDateFormat object to timezone corresponding to it (i.e. GMT). As
explained above setting the timezone on the SimpleDateFormat object
doesn't change the absolute value of the date object, just how it gets
formated. So going back to the above example:

a) would have resulted in formatting the date as '2001-01-13
12:50:43.128-08' (if I could have figured out how to implement this
easily)
b) results in formatting the date as '2001-01-13 20:50:43.128+00'
both a) and b) are simply different formats for the same underlying Date
value of 979419043128, and both are interpreted by postgresql as the
same timestamp value.

> Does creating a static variable affect the threadsafe capabilities of
> the driver? If so, it shouldn't be done, but perhaps this is a feature
> of the code already?

The Java core library objects are supposed to be thread safe unless
documented otherwise, and since SimpleDateFormat is a core library
object, it should be safe to use it from multiple threads
simultaniously. However, I just looked at the source code for
SimpleDateFormat and it is clearly *not* thread safe. So to work around
this java bug I will resubmit the original patch to take this into
account.

thanks,
--Barry

Thomas Lockhart wrote:
>
> > Oh, great, I see this patch fixes Mike Cannon's problem too. I will
> > apply it to the main tree now, and it will appear in 7.1. If you have
> > any more improvements, please send them over. Thanks.
>
> I'm confused, as usual. How does forcing the client time zone to GMT fix
> the problem of mismatching client and server time zones? istm that you
> still have a problem if the server is not on GMT.
>
> Does creating a static variable affect the threadsafe capabilities of
> the driver? If so, it shouldn't be done, but perhaps this is a feature
> of the code already?
>
> - Thomas