Re: Timestamps and PostgreSQL
Re: Timestamps and PostgreSQL
- Subject: Re: Timestamps and PostgreSQL
- From: Chuck Hill <email@hidden>
- Date: Fri, 3 Dec 2010 10:59:09 -0800
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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________
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