Re: BUG #1618: EXTRACTing EPOCH from TIME WITH TIME ZONE is broken - Mailing list pgsql-bugs

On Sat, Apr 23, 2005 at 01:58:13AM -0400, Tom Lane wrote:

 >> Time zone component should be added, not subtracted.

 > Why?

Here's three different ways of looking at the question and
seeing that what Postgres is currently doing must be wrong.
Correcting the way time zone component is applied fixes the
picture observed from every one of these viewpoints.


1. Internal semantics
---------------------

The ->zone field is number of seconds that, when added to a
timezone-specific time, shifts it to the corresponding time in
GMT.  E.g., for +1200 it's -43200, for -0500 it's 18000.  When
going from anything to GMT, that value is to be added, when
going from GMT to anything, it's to be subtracted.  This is
exactly what's done in other places of Postgres source where
true (GMT-equivalent) time is calculated.

Subtracting its ->zone from any non-GMT time is has no practical
meaning, yielding nothing but time of day somewhere twice as far
from the Greenwich meridian as the original time locale.


2. Time equality
----------------

The times 15:00:00+12 and 03:00:00+00 are exactly the same
moment, expressed two different ways:

$ TZ=Greenwich date -Rd '15:00:00+1200'
Sat, 23 Apr 2005 03:00:00 +0000

I would demonstrate that even Postgres agreed to compare them as
same, were the bug #1617 fixed.

EXTRACTing EPOCH from these times of day on any specific day
produces equal results, e.g.:

SELECT
  EXTRACT(EPOCH FROM '1970-01-01 15:00:00+12'::TIMESTAMPTZ),
  EXTRACT(EPOCH FROM '1970-01-01 03:00:00+00'::TIMESTAMPTZ);
 date_part | date_part
-----------+-----------
     10800 |     10800
(1 row)

Doing that to these same times of day without specifying any
date, however, disagrees:

SELECT
  EXTRACT(EPOCH FROM '15:00:00+12'::TIMETZ),
  EXTRACT(EPOCH FROM '03:00:00+00'::TIMETZ);;
 date_part | date_part
-----------+-----------
     97200 |     10800
(1 row)

The epoch is defined through GMT, so times of day equal from the
point of view of GMT should not differ this way.


3. Documentation and definition
-------------------------------

About EXTRACTing EPOCH from TIME[TZ], the Postgres documentation
says nothing directly.  However, regarding doing that to DATE
and TIMESTAMP[TZ] values, it says: `the number of seconds since
1970-01-01 00:00:00-00'.  Any DATE or TIMESTAMP[TZ] value for
the purposes of EXTRACTing EPOCH is a distance from that moment.

Logically, for the same purposes TIME[TZ] values should also be
distances from a certain moment of time (let's call it X).
What is this X?

For GMT, X is its midnight the same day began:

SELECT EXTRACT(EPOCH FROM '03:00:00+00'::TIMETZ);
 date_part
-----------
     10800
(1 row)

SELECT '03:00:00+00'::TIMETZ - '10800 seconds'::INTERVAL;
  ?column?
-------------
 00:00:00+00
(1 row)

For, say, Belfast (one hour away), X, apparently, is one hour
before its midnight, which corresponds absolutely to the time
two hours before the X of Greenwich:

SELECT EXTRACT(EPOCH FROM '03:00:00+01'::TIMETZ);
 date_part
-----------
     14400
(1 row)

SELECT '03:00:00+01'::TIMETZ - '14400 seconds'::INTERVAL;
  ?column?
-------------
 23:00:00+01
(1 row)

For, in turn, Amsterdam (another hour eastward), X is two hours
before its previous midnight (four hours before the X of
Greenwich):

SELECT EXTRACT(EPOCH FROM '03:00:00+02'::TIMETZ);
 date_part
-----------
     18000
(1 row)

SELECT '03:00:00+02'::TIMETZ - '18000 seconds'::INTERVAL;
  ?column?
-------------
 22:00:00+02
(1 row)

There's no sense, obvious at least, in having the basis tied to
a particular moment in neither local, nor absolute time.

--
/Awesome Walrus <walrus@amur.ru>