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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
