pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting Hour From a Time in Different Time Zone
2+ messages / 2 participants
[nested] [flat]
* Getting Hour From a Time in Different Time Zone
@ 2002-01-02 21:45 Tom Kreiner <tom_kreiner@hotmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Kreiner @ 2002-01-02 21:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Getting Hour From a Time in Different Time Zone
@ 2002-01-03 15:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Kreiner <tom_kreiner@hotmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-01-03 15:43 UTC (permalink / raw)
To: Tom Kreiner <tom_kreiner@hotmail.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-01-03 15:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-02 21:45 Getting Hour From a Time in Different Time Zone Tom Kreiner <tom_kreiner@hotmail.com>
2002-01-03 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox