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

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