Re: Timestamps awry with daylight savings time
Re: Timestamps awry with daylight savings time
- Subject: Re: Timestamps awry with daylight savings time
- From: Ken Anderson <email@hidden>
- Date: Sun, 03 Apr 2005 17:02:06 -0400
The solution I use is to always store times (and dates) in GMT.
There's never any issues like this, and your application will work well
in an international context.
On Apr 3, 2005, at 1:26 PM, Sacha Mallais wrote:
On Apr 3, 2005, at 5:29 am, Drew Thoeni wrote:
So this morning I wake up to daylight savings time here in the east
coast of the US. And just like clockwork, my application no longer
allows login. This, it turns out, is due to me having "TIMESTAMP WITH
TIME ZONE" in my EOModel for external types in Frontbase.
Reviewing the SQL log from WO, the update statement I execute when a
new user has logged in includes all the fields in the where clause
(as expected). And WO has read the time correctly from the database
but is changing the timezone on the fly. That is the timezone
recorded in the database is -5:00 (recorded prior to DST) but the
timezone WO sends in the SQL where clause is -4:00.
Example (shortened where clause)
UPDATE "T_USER"
SET "C_LAST_LOG_IN" = TIMESTAMP '2005-04-03 07:15:43.370-04:00',
"C_TIMES_LOGGED_IN" = 44
WHERE (
"C_USER_NUMBER" = 2723 AND
"C_ADMIN_LEVEL" = 1 AND
"C_SHORT_NAME" = 'alexs' AND
"C_SUFFIX" is NULL AND
"C_MEMBER_SINCE_DATE" = TIMESTAMP '2005-02-04 22:47:40.000-04:00' );
<----- problem
You can see the memberSinceDate is in February, which WO got from the
database and didn't change. But the database actually has -5:00 as
it's timezone for this column. But WO has changed the outgoing WHERE
clause to today's correct timezone (-4:00). This, of course, has the
effect of not finding a row to update, as the timestamps don't match.
How is this best handled? Not keeping timezones in the database? Not
using these columns for update checking?
Unfortunately, the date classes used in Java (and NSTimestamp, which
is based on Java's date classes) do not store time zone, preferring
instead to calculate everything from GMT. This works fine most of the
time, but when you change time zones, things get pooched.
Unfortunately, the only real solution is to store time zones
separately from the date and use that to convert all your dates. You
should not use TIMESTAMP WITH TIMEZONE: use TIMESTAMP instead.
<shameless plug>Also see pp 230-231 of Practical WebObjects</shameless
plug> ;-)
sacha
--
Sacha Michel Mallais - 800 lb. gorilla
Global Village Consulting Inc.: http://www.global-village.net/
Bottom posting is your friend:
http://www.river.com/users/share/etiquette/
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
email@hidden
This email sent to email@hidden
_______________________________________________
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