I just couldn't let this go. Anyone not using PostgreSQL can tune out.
On 04/12/2010, at 9:47 AM, Paul Hoadley wrote:
>>> Displaying those times _without_ a formatter in the app shows the first
>>> timestamp as '2010-12-03 04:51:55 Etc/GMT' which is exactly as expected,
>>> but the second (that was entered from the console behind the app's back) as
>>> '2010-12-02 18:21:55 Etc/GMT'.
>>>
>>> Is it obvious to any other PostgreSQL users what is going on? It looks
>>> like the database server's machine-local timezone is being used to adjust
>>> the timestamp somewhere between WebObjects and the database.
>>
>> I agree.
>
> After some furious Googling, it turns out that the JDBC driver is affected by
> the 'user.timezone' system property. Launching the app with
> '-Duser.timezone=GMT' on my laptop (which otherwise gets set to
> 'Australia/Adelaide') results in the behaviour I was expecting. I assume
> this was the missing link in the "UTC from top to bottom" chain, as I can see
> from the logs that both app servers were picking up their respective local
> values for this property. Just to be clear, this doesn't actually matter too
> much if the app is just running indefinitely on the same server: the
> timestamps seem to be adjusted on the way in and back out of the database, so
> that everything Just Works from within the app. It was only when I dumped
> and restored the database somewhere else that it became a problem.
The JDBC driver is, indeed, adjusting the timestamp, and apparently this is a
feature. EOF calls PreparedStatement.setTimestamp(int, Timestamp) (implemented
in AbstractJdbc2Statement), which in turn calls
PreparedStatement.setTimestamp(int, Timestamp, Calendar) with a null Calendar.
To format the timestamp, TimestampUtils.toString(Calendar, Timestamp) is
called, again with a null Calendar. The null Calendar is replaced by a new
GregorianCalendar(), which is used to format the string sent to the database.
All of this is fine, _except_ that the GregorianCalendar's timezone turns out
to be the machine-local timezone, despite calling
TimeZone.setDefault(TimeZone.getTimeZone("GMT")) in the application's
constructor. (It's not obvious to me why this would be the case—does
TimeZone.setDefault() not apply JVM-wide?) In any case, setting the
user.timezone system property (to UTC) _is_ sufficient to cause that
GregorianCalendar to be constructed with its timezone set to UTC, and then the
JDBC driver doesn't perform any adjustments to timestamps on the way in or out.
The PostgreSQL JDBC driver's behaviour strikes me as somewhat
counter-intuitive, to say the least. I'm pretty sure it nails the issue of
keeping timestamps in UTC with Postgres, though.
--
Paul.
http://logicsquad.net/
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
