From: Bill MacArthur <webmaster@dhs-club.com>
To: pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
Date: Fri, 03 Aug 2012 11:34:20 -0400
Message-ID: <501BEF7C.1030403@dhs-club.com> (raw)
In-Reply-To: <501BED07.5070603@shopzeus.com>
References: <501B8C6F.6040100@shopzeus.com>
<501BAA84.9010306@shopzeus.com>
<501BAE33.4090102@shopzeus.com>
<16399.1344003584@sss.pgh.pa.us>
<501BED07.5070603@shopzeus.com>
On 8/3/2012 11:23 AM, Laszlo Nagy wrote:
>> 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 completely.
>
> 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.
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.
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: webmaster@dhs-club.com, pgsql-admin@postgresql.org
Subject: Re: Messed up time zones
In-Reply-To: <501BEF7C.1030403@dhs-club.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