Re: Patch for JDBC timestamp problems - Mailing list pgsql-patches

Bruce, please pass *any* JDBC related patches by Peter Mount
(peter@retep.org.uk) , unless you've recently become a JDBC expert?  AS
Thomas pointed out, not only did you miss the GMT implications, but you
also missed the threadsafe implications :(

On Sat, 13 Jan 2001, Barry Lind wrote:

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

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org