Re: displaying UTC time in local time

mark_postgres_user <[hidden email]> writes:
> To recap:  So, because my timestamps are defined as timestamp without
> timezone data type and are stored in UTC, when I display them in their
> default form they'll be displayed in UTC.  I can use AT TIME ZONE to convert
> the displayed time to any timezone including my local timezone.

> So why is it that I can get the displayed time to be local only if I use "AT
> TIMEZONE 'UTC'" ?  Anything else will either be ignored or display UTC.

When applied to a timestamp-without-timezone, AT TIME ZONE means "convert
argument 1 to timestamp-with-timezone, assuming that it represents local
time in the timezone named by argument 2".  So if your stored values do
in fact represent UTC times, the *only* correct application of AT TIME
ZONE to them is "AT TIME ZONE 'UTC'"; anything else will generate a wrong
ts-with-tz value.

Once you have a correct value, the output function for datatype
timestamp-with-timezone automatically takes care of displaying it in
the zone defined by the timezone parameter (which I assume you've got
set to whatever you think local time is).

If you really want to do all this manually, you could do something like

   SELECT (ts-without-tz-value AT TIME ZONE 'UTC') AT TIME ZONE 'foo'

which will first convert the ts-without-tz value to ts-with-tz under the
assumption that it represents local time in UTC, and then convert the
ts-with-tz value back to ts-without-tz, producing the correct local time
in zone 'foo'.  Since it's now ts-without-tz, it'll be displayed as-is,
without any consultation of the timezone parameter.

But TBH it seems clear to me that you did this wrong.  The way you are
thinking about this column says that you should be storing it as
ts-with-tz.  Then it'll automatically be displayed in your local zone,
and if you want to see it in some other zone, a single application of
AT TIME ZONE will do that for you.

                        regards, tom lane


--
Sent via pgsql-admin mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

---