This could be off-topic for this list, but it is moderately closely related...
I've been involved in some discussion that has concluded that the result
of (thinks hard, and crosses fingers that I've done my mental arithmetic
correctly -- if I'm wrong, I meant +08:00 instead):
EXTRACT(HOUR FROM TIMESTAMP '1999-12-31 23:59:59-08:00')
should be 7, that if you extract DAY, it should be 1, that if you extract
MONTH, it should be 1, and if you extract year, it should be 2000.
Apparently, the only way to EXTRACT, say, 31 for the DAY is to coerce
things so that the value ends up without a time zone, and then extract
from it.
Does anyone use a DBMS with TIMEZONE WITH TIME ZONE support, including
EXTRACT? If so, which DBMS is it, and what does it give for the
expressions:
SELECT UNIQUE
EXTRACT(HOUR FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS
"ts_hour",
EXTRACT(DAY FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS
"ts_day",
EXTRACT(MONTH FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS
"ts_month",
EXTRACT(YEAR FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS
"ts_year"
FROM SomeTable;
Where SomeTable should ideally be a table with a single row of data in it
(dual in Oracle, etc).
(In case you hadn't guessed, I'm not wholly in agreement with the
conclusion.)
--
Jonathan Leffler ([EMAIL PROTECTED])
STSM, Informix Database Engineering, IBM Information Management
4400 N First St, San Jose, CA 95134-1257
Tel: +1 408-956-2436 Tieline: 475-2436
"I don't suffer from insanity; I enjoy every minute of it!"
smime.p7s
Description: S/MIME Cryptographic Signature
