Re: displaying UTC time in local time

On Oct 29, 2013, at 8:05 AM, mark_postgres_user <mark(dot)ikemoto(at)fireeye(dot)com> 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
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice