From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laszlo Nagy <gandalf@shopzeus.com>
cc: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
In-reply-to: <501BAE33.4090102@shopzeus.com>
References: <501B8C6F.6040100@shopzeus.com> <501BAA84.9010306@shopzeus.com>
	<501BAE33.4090102@shopzeus.com>
Comments: In-reply-to Laszlo Nagy <gandalf@shopzeus.com>
	message dated "Fri, 03 Aug 2012 12:55:47 +0200"
Date: Fri, 03 Aug 2012 10:19:44 -0400
Message-ID: <16399.1344003584@sss.pgh.pa.us>

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