Re: Messed up time zones

Laszlo Nagy <gandalf@shopzeus.com> writes:
> So how do I create a query that results in something like:

>             a
> ------------------------------
>   Sun Oct 30 02:00:00 2011 +0500
>   Sun Oct 30 02:00:00 2011 +0600
> (2 rows)

Set the "timezone" setting to the zone you have in mind, and then just
print the values.  The reason there's no manual way to do rotation
across zones is that there's no need for one because it's done
automatically during printout of a timestamptz value.

I suspect that you have not correctly internalized what timestamptz
values actually are.  Internally they are just time values specified in
UTC (or UT1 if you want to be picky).  On input, the value is rotated
from whatever zone is specified in the string (or implicitly specified
by "timezone") to UTC.  On output, the value is rotated from UTC to
whatever the current "timezone" setting is.

            regards, tom lane