Re: Timestamps and PostgreSQL
Re: Timestamps and PostgreSQL
- Subject: Re: Timestamps and PostgreSQL
- From: Paul Hoadley <email@hidden>
- Date: Sat, 4 Dec 2010 09:47:07 +1030
Hi Chuck,
On 04/12/2010, at 5:29 AM, Chuck Hill wrote:
> On Dec 2, 2010, at 9:23 PM, Paul Hoadley wrote:
>
>> Hello,
>>
>> I am trying to debug a timestamp issue.
>
> Run away to New Zealand. South Island. Assume a new identity. Save yourself while you still can!
Yeah, that's how I feel about this...
>> The larger problem is that I recently moved an application between servers whose local timezones are different by 5 hours. I thought I had taken care to set every layer to UTC (JVM: TimeZone.setDefault(TimeZone.getTimeZone("GMT")); WO: NSTimeZone.setDefaultTimeZone(NSTimeZone.getGMT());
>
> I am unsure if any of those really matter. NSTimestamp is always in GMT. The time zone should only affect formatting and parsing.
I wasn't sure either, though I've seen them suggested on the list over the years.
> If you are doing NSTimeZone.setDefaultTimeZone(NSTimeZone.getGMT()), then I assume you are using a use specific TZ when parsing user input / showing times:
>> and only use user-local timezones in the view layer
Yes.
>> Postgres: '-c timezone=GMT'),
>
> That I am less sure about. FrontBase stores everything in GMT, but if you do a select in FrontBaseManager, it will format the times into your local timezone. I am unsure of PG's handling of this.
Until last week, I thought Postgres (or psql in particular) was doing the same thing. But now I'm not convinced, as pg_dump dumps the timestamps _as displayed by psql_. That was the source of my larger problem, as the restore was into a different server-local timezone.
>> Inserting the same literal timestamp using psql:
>>
>> PBF=# insert into admission(admit_time, booking_id) VALUES ('2010-12-03 04:51:55.912', 1);
>> INSERT 0 1
>>
>> Adds the following row:
>>
>> 2 | 2010-12-03 04:51:55.912
>>
>> That is, the time in UTC as expected.
>
> So it looks like PG is not doing what FB does, so it looks like it is somehow altering the inserted data. Perhaps due to the "WITHOUT TIME ZONE" definition?
I don't _think_ so. I manually changed the column type to "TIMESTAMP WITH TIME ZONE", and it made no difference.
>> 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.
--
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