From: Steve Crawford <scrawford@pinpointresearch.com>
To: Laszlo Nagy <gandalf@shopzeus.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [ADMIN] Messed up time zones
Date: Fri, 03 Aug 2012 11:25:53 -0700
Message-ID: <501C17B1.6020604@pinpointresearch.com> (raw)
In-Reply-To: <501C0884.1040106@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>
<501BFA4F.5010709@pinpointresearch.com>
<501C0884.1040106@shopzeus.com>
On 08/03/2012 10:21 AM, Laszlo Nagy wrote:
>
>> 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=>
>
I can not from the given information. Can you? The given information is
ambiguous as are all times during the hour of fall-back everywhere. That
leaves developers with a choice: choose an interpretation or throw an
error. PostgreSQL chooses to use an interpretation.
It would be nice if there were a specification as to how such ambiguous
data should be interpreted. Perhaps someone can point me to one and to
any relevant documentation detailing how PostgreSQL handles such data.
As it is, you need to be aware of how each part of your system deals
with such. For example (using my local time zone) using the date command
on Linux I see that
"date -d '2012-11-04 0130'"
returns
"Sun Nov 4 01:30:00 PDT 2012" (Still in Daylight Saving Time)
But given the same input, PostgreSQL interprets it as standard time
(offset -08):
select '2012-11-04 0130'::timestamptz;
timestamptz
------------------------
2012-11-04 01:30:00-08
> 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.
>
>
So you took two distinct points in time, threw away some critical
information, and are surprised why they are now equal? Then don't do
that. It's the equivalent of being surprised that www.microsoft.com is
the same as www.apple.com when comparing them on the short hostname
only. If you want to know if two points in time differ, just compare them.
Spending a couple hours reading
http://www.postgresql.org/docs/current/static/datatype-datetime.html
will be time well spent.
Cheers,
Steve
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: scrawford@pinpointresearch.com, gandalf@shopzeus.com
Subject: Re: [ADMIN] Messed up time zones
In-Reply-To: <501C17B1.6020604@pinpointresearch.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