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 protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
