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!
> In development I am using PostgreSQL 8.2.5 (8.4.4 in deployment), WO 5.4.3,
> PostgresqlPlugIn.framework, and postgresql-8.2-508.jdbc3.jar.
>
> 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. 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
> 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.
> Despite this, it looks like the move has botched some timestamps, moving them
> by 5 hours. The attributes in question use the 'dateTime' prototype from
> ERPrototypes, the externalType for which is "timestamp" which is interpreted
> by Postgres as "TIMESTAMP WITHOUT TIME ZONE".
That is what FB uses, perhaps PG should be using TIMESTAMP WITH TIME ZONE to
prevent the DB server from modifying the inserted data?
> In an effort to get to the bottom of this, I can't help concluding that the
> machine-local timezone for the database server is having an effect, which
> just doesn't seem right to me. My laptop's local timezone is +1030, and I'm
> running Postgres on it for development: everything is local on the one
> machine in the one timezone. I've just added an entity to the database which
> generates this SQL:
>
> INSERT INTO admission(admit_time, booking_id, amount, id, ward,
> discharge_time) VALUES (?::timestamp, ?::int4, NULL, ?::int4, NULL, NULL)"
> withBindings: 1:2010-12-03 04:51:55.912(admitTime), 2:1(bookingID), 3:1(id)>
>
> Local time here was 1521, so that looks exactly right: local current time
> converted to UTC. Using psql, we're in GMT:
>
> PBF=# SHOW TIME ZONE;
> TimeZone
> ----------
> GMT
> (1 row)
>
> Yet it appears that the time has been converted back to +1030:
>
> PBF=# select id, admit_time from admission;
> id | admit_time
> ----+-------------------------
> 1 | 2010-12-03 15:21:55.912
> (1 row)
You would see that in the FrontBaseManager, because the tool formats the data
for display in your TZ.
> 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?
> 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.
> Is there some final parameter I need to supply or variable I need to adjust
> to get the stack into UTC from top to bottom? I'm reasonably sure this was
> the cause of my larger problem, as pg_dump outputs the timestamps just as
> they're being displayed by psql, and then on restore they're 5 hours out from
> what would be expected.
Sorry, no specific help there.
> I hate timestamps.
I hate time.
http://www.youtube.com/watch?v=RwJNTZtjC7E
Chuck
--
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
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ 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
