Re: Getting Hour From a Time in Different Time Zone

"Tom Kreiner" <tom_kreiner(at)hotmail(dot)com> writes:
> SELECT hour(timestamp(current_timestamp AT TIME ZONE INTERVAL '-05 hours'));

The AT TIME ZONE construct produces a text string, which you are
converting back into a timestamp, which is internally just GMT --- all
notion that it had anything to do with EST time is gone.

You can actually get the above to work in 7.2, though. It looks like
Thomas changed AT TIME ZONE to not emit timezone in the output string,
which avoids the rotation back to your own zone:

regression=# SELECT current_timestamp;
timestamptz
-------------------------------
2002-01-03 10:40:42.901964-05
(1 row)

regression=# SELECT current_timestamp AT TIME ZONE INTERVAL '-08 hours';
timezone
----------------------------
2002-01-03 07:40:49.452058
(1 row)

regression=# SELECT "timestamp"(current_timestamp AT TIME ZONE INTERVAL '-08 hours');
timestamp
----------------------------
2002-01-03 07:41:19.469686
(1 row)

regression=# SELECT extract(hour from "timestamp"(current_timestamp AT TIME ZONE INTERVAL '-08 hours'));
date_part
-----------
7
(1 row)

regards, tom lane