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

Reply via email to