Timestamps and PostgreSQL
Timestamps and PostgreSQL
- Subject: Timestamps and PostgreSQL
- From: Paul Hoadley <email@hidden>
- Date: Fri, 3 Dec 2010 15:53:24 +1030
Hello,
I am trying to debug a timestamp issue. 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()); Postgres: '-c timezone=GMT'), and only use user-local timezones in the view layer. 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".
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)
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.
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. 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.
I hate timestamps.
--
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