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

Re: Messed up time zones

From: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Messed up time zones
Date: 2012-08-03 15:23:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-performance
On 2012-08-03 16:19, Tom Lane wrote:
> 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.

majorforms=> set time zone 'Europe/Budapest';
majorforms=> select * from test;
  2011-10-30 02:00:00+02
  2011-10-30 02:00:00+01
(2 rows)


It works. Thank you!

So is it impossible to construct a query with columns that are different 
time zones? I hope I'm not going to need that. :-)

>    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 can come up with an example when it would be needed. For example, 
consider a company with two sites in different time zones. Let's say 
that they want to store time stamps of online meetings. They need to 
create a report that shows the starting time of the all meetings *in 
both zones*. I see no way to do this in PostgreSQL. Of course, you can 
always select the timestamps in UTC, and convert them into other time 
zones with a program so it is not a big problem. And if we go that 
route, then there is not much point in using the timestamptz type, since 
we already have to convert the values with a program...

> 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.
Oh I see. So actually they don't store the zone? I have seen that 
timestamptz and timestamp both occupy 8 bytes, but I didn't understand 

It also means that if I want to store the actual time zone (in what the 
value was originally recorded), then I have to store the zone in a 
separate field. Later I can convert back to the original time zone, but 
only with an external program.

Fine with me. I'm happy with this, just I did not understand how it works.



In response to


pgsql-performance by date

Next:From: Bill MacArthurDate: 2012-08-03 15:34:20
Subject: Re: Messed up time zones
Previous:From: Kevin GrittnerDate: 2012-08-03 14:33:31
Subject: Re: query using incorrect index

pgsql-admin by date

Next:From: Bill MacArthurDate: 2012-08-03 15:34:20
Subject: Re: Messed up time zones
Previous:From: Tom LaneDate: 2012-08-03 14:19:44
Subject: Re: Messed up time zones

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