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 16:06:38
Message-ID: 501BF70E.4090903@shopzeus.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
> You could store the zone in a separate field and then create a VIEW on 
> the table that used a function to take both values and return the 
> timestamptz just as it was inserted.
>
Well no, it is not possible.  A timestamptz value is interpreted as UTC, 
regardless of your local timezone. A timestamp value is interpreted in 
your local time zone. This is the main difference between them. You can 
change *the interpretation* of these values with the "at time zone" 
expression. But you cannot convert between time zones at all! Time zone 
information is not stored in any way - it is a global setting.

I have intentionally chosen an example where the local time is changed 
from summer time to winter time (e.g. local time suddenly "goes back" 
one hour). It demonstrates that you cannot use "at time zone ...." 
expression to convert a timestamptz into a desired time zone manually.

The only case when time zone conversion occurs is when you format the 
timestamp/timestamptz value into a text. As Tom Lane pointed out, the 
only correct way to convert a timestamptz/timestamp value into a desired 
time zone is to use the "set time zone to ...." command. But that 
command has a global effect, and it does not actually change the zone of 
the stored value (because the time zone is not stored at all). It just 
changes the formatting of those values, and as a result, you will get a 
correct textual representation of the original timestamp value in the 
desired time zone. But you will *never* be able to get a correct 
timestamp value in a desired time zone. All you can get is text.

As far as I'm concerned, I'm going to set the system's clock to UTC, 
store everything in timestamp field (in UTC),  and use a program to 
convert fetched values before displaying them.

Regards,

    Laszlo


In response to

Responses

pgsql-performance by date

Next:From: Steve CrawfordDate: 2012-08-03 16:20:31
Subject: Re: Messed up time zones
Previous:From: Russell KeaneDate: 2012-08-03 15:57:50
Subject: Re: query using incorrect index

pgsql-admin by date

Next:From: Steve CrawfordDate: 2012-08-03 16:20:31
Subject: Re: Messed up time zones
Previous:From: Bill MacArthurDate: 2012-08-03 15:34:20
Subject: Re: Messed up time zones

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