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