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 10:26:07 -0700
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:
This email sent to email@hidden