From: Laszlo Nagy <gandalf@shopzeus.com>
To: Steve Crawford <scrawford@pinpointresearch.com>
To: pgsql-performance@postgresql.org
Subject: Re: [ADMIN] Messed up time zones
Date: Fri, 03 Aug 2012 19:21:08 +0200
Message-ID: <501C0884.1040106@shopzeus.com> (raw)
In-Reply-To: <501BFA4F.5010709@pinpointresearch.com>
References: <501B8C6F.6040100@shopzeus.com>
<501BAA84.9010306@shopzeus.com>
<501BAE33.4090102@shopzeus.com>
<16399.1344003584@sss.pgh.pa.us>
<501BED07.5070603@shopzeus.com>
<501BFA4F.5010709@pinpointresearch.com>
> All the above are the exact same point in time merely stated as
> relevant to each location. Note that given a timestamp with time zone
> and a zone, PostgreSQL returns a timestamp without time zone (you know
> the zone since you specified it).
Yes, I know the zone. But I don't know the offset from UTC.
Example:
template1=> set timezone to 'UTC';
SET
template1=> select ('2011-10-30 01:00:00'::timestamptz) at time zone
'Europe/Budapest';
timezone
---------------------
2011-10-30 02:00:00 -- Is it winter or summer time?
(1 row)
template1=> select ('2011-10-30 00:00:00'::timestamptz) at time zone
'Europe/Budapest';
timezone
---------------------
2011-10-30 02:00:00 -- Is it winter or summer time? What is the
offset from UTC here? Can you tell me when it was in UTC?
(1 row)
template1=>
What is more:
template1=> select (('2011-10-30 00:00:00'::timestamptz) at time zone
'Europe/Budapest') is distinct from (('2011-10-30
01:00:00'::timestamptz) at time zone 'Europe/Budapest');
?column?
----------
f
(1 row)
template1=>
Yeah, we know what time zone it is in, but we don't know when it was,
thanks a lot. :-( It would be unambiguous to store the UTC offset along
with the value. But it is not how it was implemented.
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, scrawford@pinpointresearch.com
Subject: Re: [ADMIN] Messed up time zones
In-Reply-To: <501C0884.1040106@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