Re: Patch for JDBC timestamp problems - Mailing list pgsql-patches
From | Barry Lind |
---|---|
Subject | Re: Patch for JDBC timestamp problems |
Date | |
Msg-id | 3A60CBA5.33DBBA3C@xythos.com Whole thread Raw |
In response to | Re: Patch for JDBC timestamp problems (Bruce Momjian <pgman@candle.pha.pa.us>) |
Responses |
Re: Patch for JDBC timestamp problems
(The Hermit Hacker <scrappy@hub.org>)
|
List | pgsql-patches |
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
pgsql-patches by date: