Re: Messed up time zones

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Messed up time zones
Date: 2012-08-03 14:19:44
Message-ID: 16399.1344003584@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

Laszlo Nagy <gandalf(at)shopzeus(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laszlo Nagy 2012-08-03 15:23:51 Re: Messed up time zones
Previous Message Laszlo Nagy 2012-08-03 10:55:47 Re: Messed up time zones

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2012-08-03 14:33:31 Re: query using incorrect index
Previous Message Laszlo Nagy 2012-08-03 10:55:47 Re: Messed up time zones