Re: displaying UTC time in local time

mark_postgres_user <mark(dot)ikemoto(at)fireeye(dot)com> writes:
> In my Postgres 9.1.9 database, I'd like to display a field I created,
> start_time, in my misc database table. Ive defined this field as data
> type timestamp without time zone. Everything that I've read about
> Postgres says that that timestamp fields are stored in UTC format. So when
> these fields are displayed theyll be converted at that point into local and
> show up in local timezone time based on what my server is configured for
> (currently PDT).

Those statements apply to values of type timestamp WITH timezone.
A timestamp WITHOUT timezone is just what-you-see-is-what-you-get.

You can use AT TIME ZONE to rotate the value to another zone anyway,
but it's a bit difficult to wrap your head around what will happen.
IIRC, that operator will assume that the without-time-zone value is
expressed in the time zone you name as the other operand, and rotate it
from there to UTC, producing a value of timestamp WITH time zone. That's
okay so far, but remember that when you display the result, it'll get
rotated again (to whatever zone you have set in the "timezone" parameter).

Generally speaking, if you're storing values that represent well-defined
instants in time, you're best off using timestamp with timezone.
Timestamp without timezone has some use to represent concepts like
"the meeting is at 4pm local time Tuesday", where you're aware that
this isn't actually a well-specified instant without some context;
or if you really don't want the system doing *anything* for you time
zone wise.

regards, tom lane