From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
Date: Fri, 03 Aug 2012 18:06:38 +0200
Message-ID: <501BF70E.4090903@shopzeus.com> (raw)
In-Reply-To: <501BEF7C.1030403@dhs-club.com>
References: <501B8C6F.6040100@shopzeus.com>
<501BAA84.9010306@shopzeus.com>
<501BAE33.4090102@shopzeus.com>
<16399.1344003584@sss.pgh.pa.us>
<501BED07.5070603@shopzeus.com>
<501BEF7C.1030403@dhs-club.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@shopzeus.com, pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
In-Reply-To: <501BF70E.4090903@shopzeus.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox