Brian Candler wrote:
On Sun, Aug 01, 2004 at 01:00:16PM +0200, Michael Neumann wrote:
If we get this timestamp out of a database "2003-03-03 12:34:56". Which
timezone is it in?
Now that's a real can of worms you've opened.
This is handled so badly by SQL databases that generally I just use an
INTEGER field and put a utime value in it wherever possible.
Talking about Oracle, which is where I've looked the most, a column of type
DATE simply stores a value YYYYMMDDHHMMSS without any time zone information.
This might be a value in UCT, or it might be a value in the local time zone,
depending on the programmer who put it there. However in most cases, I think
you'll find the value is in local time; certainly that's what you'll get if
you do
update foo set mystamp = sysdate() where bar = 'baz';
A similar data type is TIMESTAMP(n), which is like DATE but stores n digits
of decimal precision of the seconds.
Then it seems Oracle realised there was a problem, and introduced the
TIMESTAMP WITH TIME ZONE data type, which as far as I can tell, stores both
the local time and the timezone offset. Here are some examples culled from
the Oracle SQL reference manual:
---------------------------------------------------------------------------
Two TIMESTAMP WITH TIME ZONE values are considered identical if they
represent the same instant in UTC, regardless of the TIME ZONE offsets
stored in the data. For example,
TIMESTAMP '1999-04-15 8:00:00 -8:00'
is the same as
TIMESTAMP '1999-04-15 11:00:00 -5:00'
That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern
Standard Time.
You can replace the UTC offset with the TZR (time zone region) format
element. For example, the following example has the same value as the
preceding example:
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
To eliminate the ambiguity of boundary cases when the daylight savings time
switches, use both the TZR and a corresponding TZD format element. The
following example ensures that the preceding example will return a daylight
savings time value:
TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT'
If you do not add the TZD format element, and the datetime value is
ambiguous, then Oracle returns an error if you have the
ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is
set to FALSE, then Oracle interprets the ambiguous datetime as standard
time.
---------------------------------------------------------------------------
Ugh!!
Ugh me too ;-)
Local or GMT? Problem with local is, that the
database might be in a different time-zone than the application, and if
it's not a "valid" Ruby Time, e.g. "1000-01-01 12:34:56+01", then it's
not easy to convert that into local time (day light saving in year 1000
?). That's the problem with local time. GMT doesn't has this problem.
Best thing would be to use the Date, Time and DateTime class of Ruby's
standard library, but then, class Time would break for dates before
1970.
I don't agree with your conclusion that Time would break for dates before
1970; a negative offset is fine. It seems to work for me anyway:
irb(main):002:0> Time.at(-360000)
=> Sat Dec 27 21:00:00 BST 1969
okay, but:
Time.local(1900) #=> time out of range
If all DateTimes were in GMT, or if you could specify an offset,
this would avoid converting to and from the local time.
I think there are two approaches:
(1) Make DBI::Timestamp just pass through whatever it gets from the SQL
database (separate values for YYYY MM DD HH MM SS as it has now, with an
optional timezone info attribute which defaults to nil), and let the
application worry about it. This is the "wash your hands" approach.
(2) Try to convert everything to Ruby Time.
The trouble with case (2) is you really do not know whether the value stored
was in local time, or as you point out, whether the local time when the
database entry was written is different from the local time where the
application is running. Worse, there is a boundary case for local times
switching from daylight-saving to non-daylight-saving where a time is
ambiguous and generating a Ruby Time object may have to make an arbitary
(wrong) decision.
I think (1) is the best solution we can provide. Then, something like a
conversion hook would be nice (in both directions), for example:
DBI.set_conversion { |obj|
case obj
when DBI::Timestamp
# convert to Ruby time
else
...
end
}
I've not investigated other databases in detail, but I suspect the same
issues arise.
Ultimately these problems derive from stupid design in the SQL Date type,
and misguided application writers who decide to use it anyway. Give me an
INTEGER any day :-)
I suppose what you could do is change the DBI::Timestamp constructor so that
you pass in an explicit indication of the timezone, and an explicit
indication of whether daylight savings was in force at that time (both of
hm, sounds like Ruby's Time? Except, that Time is not able to handle
dates before 1900.
Regards,
Michael
|
