Re: [ADMIN] Messed up time zones - Mailing list pgsql-performance

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