Обсуждение: Getting Hour From a Time in Different Time Zone

I'm working on a web interface that will be taking into account that the 
users will be in different time zones. In one of my queries, I need to 
extract the hour of a time that has been converted to the appropriate time 
zone. Everytime I do this, the hour function gives me he hour in the 
server's time zone, not in the time zone I'm trying to convert to.

For example, the server's time zone is -08. I'm in time zone -05. Assume 
that current_timestamp (for the server) is 9:00 AM. I know that:

SELECT current_timestamp AT TIME ZONE INTERVAL '-05 hours';

gives me 12:00 PM, which is the correct time for my time zone. However, if I 
do:

SELECT hour(timestamp(current_timestamp AT TIME ZONE INTERVAL '-05 hours'));

I get 9. The server is returning the hour for it's designated time zone. In 
essence, it's undoing the time zone change.

I need the hour for the converted time zone time. Has anyone else had this 
problem and found a solution for it? Thanks.

Tom Kreiner
tom_kreiner@hotmail.com

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx

"Tom Kreiner" <tom_kreiner@hotmail.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