Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-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

pgsql-performance by date

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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group