mark_postgres_user <mark.ikemoto@xxxxxxxxxxx> 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 (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
