Re: Timestamps and PostgreSQL
Re: Timestamps and PostgreSQL
- Subject: Re: Timestamps and PostgreSQL
- From: Chuck Hill <email@hidden>
- Date: Mon, 6 Dec 2010 11:43:31 -0800
Is there a Wiki page for EOF and Postgres? It strikes me as this is something that should be documented. I don't think we can "fix" it, as it would impact people with existing data.
On Dec 5, 2010, at 1:38 AM, Paul Hoadley wrote:
> 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 (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden