Re: Timestamps awry with daylight savings time
Re: Timestamps awry with daylight savings time
- Subject: Re: Timestamps awry with daylight savings time
- From: Sacha Mallais <email@hidden>
- Date: Sun, 3 Apr 2005 15:15:36 -0700
On Apr 3, 2005, at 2:02 pm, Ken Anderson wrote:
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.
Yes, by changing to TIMESTAMP columns, this is effectively what you are
doing. The only caveat then is to be careful when converting times to
the local time zone, since time doesn't always go forward when you're
dealing with Daylight Saving Time...
sacha
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
--
Sacha Michel Mallais - 400 lb. chimp
Global Village Consulting Inc.: http://www.global-village.net/
The bird of paradise alights only upon the hand that does not grasp.
_______________________________________________
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