Re: Anybody got experience with SQL, EXTRACT, and TIMESTAMP WITH TIME ZONE?

[David E. Wheeler]

On Aug 20, 2008, at 21:17, Jonathan Leffler wrote:

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;

From PostgreSQL 8.3:

try=# SELECT
try-# EXTRACT(HOUR  FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS  
ts_hour,
try-# EXTRACT(DAY   FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS  
ts_day,
try-# EXTRACT(MONTH FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS  
ts_month,
try-# EXTRACT(YEAR  FROM TIMESTAMP '1999-12-31 23:59:59-08:00') AS  
ts_year;
 ts_hour | ts_day | ts_month | ts_year
---------+--------+----------+---------
      23 |     31 |       12 |    1999
(1 row)

Adding the time zone support:

try=# SELECT
try-# EXTRACT(HOUR  FROM TIMESTAMPTZ '1999-12-31 23:59:59-08:00') AS  
ts_hour,
try-# EXTRACT(DAY   FROM TIMESTAMPTZ '1999-12-31 23:59:59-08:00') AS  
ts_day,
try-# EXTRACT(MONTH FROM TIMESTAMPTZ '1999-12-31 23:59:59-08:00') AS  
ts_month,
try-# EXTRACT(YEAR  FROM TIMESTAMPTZ '1999-12-31 23:59:59-08:00') AS  
ts_year;
 ts_hour | ts_day | ts_month | ts_year
---------+--------+----------+---------
       7 |      1 |        1 |    2000
(1 row)

HTH,

David