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.

--
Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On 08/03/2012 10:21 AM, Laszlo Nagy wrote:

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

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-performa...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Well, I did not want to throw away any information. The actual
representation could be something like:

"2012-11-04 01:30:00-08 in Europe/Budapest, Winter time"

and

"2012-11-04 01:30:00-08 in Europe/Budapest, Summer time".

It would be unambiguous, everybody would know the time zone, the UTC
offset and the time value, and conversion back to UTC would be
unambiguous too.

I presumed that the representation is like that. But I was wrong. I have
checked other programming languages. As it turns out, nobody wants to
change the representation just because there can be an ambiguous hour in
every year. Now I think that most systems treat ambiguous time stamps as
if they were in standard time. And who am I to go against the main flow?
I'm sorry, I admit that the problem was in my head.

--
Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance