Thanks, Scott, for your reply below. It helped clear up a lot of questions I had about timezone usage. Very helpful.
Mark
Mark
From: Scott Ribe-2 [via PostgreSQL] [ml-node+[hidden email]]
Sent: Tuesday, October 29, 2013 11:53 PM
To: Mark Ikemoto
Subject: Re: displaying UTC time in local time
Sent: Tuesday, October 29, 2013 11:53 PM
To: Mark Ikemoto
Subject: Re: displaying UTC time in local time
On Oct 29, 2013, at 8:05 AM, mark_postgres_user <[hidden email]> wrote:
> So, because my timestamps are defined as timestamp without
> timezone data type and are stored in UTC...
No, they're not stored as *any* time zone at all, not even UTC. They are timestamp *without* time zone.
> I can use AT TIME ZONE to convert
> the displayed time to any timezone including my local timezone.
No, you can use AT TIME ZONE to convert them to a timestamp with time zone and simultaneously specify the time zone they are relative to, in other words the time zone to which they were adjusted before being stored.
> P.S. Someone else owns the database I'm using so I can't change the
> timestamps to be timestamp with timezone.
OK, you're stuck with an incorrect data type. That's why it's not intuitive to work with. Because it's timestamp without time zone, you have to do the work that PG would do for you if the correct type were used--you have to make sure the timestamps are all adjusted to the same time zone before being stored, and you have to convert them to local time after retrieval.
--
Scott Ribe
[hidden email]
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
> So, because my timestamps are defined as timestamp without
> timezone data type and are stored in UTC...
No, they're not stored as *any* time zone at all, not even UTC. They are timestamp *without* time zone.
> I can use AT TIME ZONE to convert
> the displayed time to any timezone including my local timezone.
No, you can use AT TIME ZONE to convert them to a timestamp with time zone and simultaneously specify the time zone they are relative to, in other words the time zone to which they were adjusted before being stored.
> P.S. Someone else owns the database I'm using so I can't change the
> timestamps to be timestamp with timezone.
OK, you're stuck with an incorrect data type. That's why it's not intuitive to work with. Because it's timestamp without time zone, you have to do the work that PG would do for you if the correct type were used--you have to make sure the timestamps are all adjusted to the same time zone before being stored, and you have to convert them to local time after retrieval.
--
Scott Ribe
[hidden email]
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/displaying-UTC-time-in-local-time-tp5776169p5776339.html
______________________________________________________________________
This email and any attachments thereto may contain private, confidential, and/or privileged material for the sole use of the intended recipient. Any review, copying, or distribution of this email (or any attachments thereto) by others is strictly prohibited. If you are not the intended recipient, please contact the sender immediately and permanently delete the original and any copies of this email and any attachments thereto.
______________________________________________________________________
View this message in context: RE: displaying UTC time in local time
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
