The following bug has been logged online:

Bug reference: 1618
Logged by: Dennis Vshivkov
Email address: walrus (AT) amur (DOT) ru
PostgreSQL version: 7.4.6, 8.0.1
Operating system: Debian GNU/Linux, kernel 2.4
Description: EXTRACTing EPOCH from TIME WITH TIME ZONE is broken
Details:

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

It should rather have returned 10800.

[8.0.1|src/backend/utils/adt/date.c] timetz_part():
else if ((type == RESERV) && (val == DTK_EPOCH))
{
#ifdef HAVE_INT64_TIMESTAMP
result = ((time->time / 1000000e0) - time->zone);
#else
result = (time->time - time->zone);
#endif
}

Time zone component should be added, not subtracted.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Dennis Vshivkov" <walrus (AT) amur (DOT) ru> writes:
Why?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Dennis Vshivkov <walrus (AT) amur (DOT) ru> writes:
OK, I'm convinced --- particularly by the point that no place else in
the backend thinks "timetz->time - timetz->zone" is meaningful.

Anyone not think that this should be not only changed, but back-patched
into older branches? It seems clearly a thinko.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"Dennis Vshivkov" <walrus (AT) amur (DOT) ru> writes:
Done.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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 (AT) amur (DOT) ru>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend