> There is a problem with epoch as well that was not in the 7.1.3
Hmm. 7.1.x did not implement any date_part() functions for time types.
So the results were obtained from a conversion to interval before
calling date_part()!
7.2 implements date_part() for time with time zone, and converts time
without time zone to time with time zone when executing your query. The
behavior is likely to be somewhat different. But...
I think that your problem report now has two parts:
1) extract(epoch from time with time zone '00:00:34') should return
something "reasonable". I'll claim that it does that currently, since
(if you were trying that query) you are one hour away from GMT and get
3600+34 seconds back, which is consistant with same instant in GMT. If
the epoch is relative to GMT, then this may be The Right Thing To Do.
2) extract(epoch from time '00:00:34') should return something which
does not involve a time zone of any kind if it were following the
conventions used for timestamp without time zone. So we should have an
explicit function to do that, rather than relying on converting to "time
with time zone" before extracting the "epoch".
Unfortunately, I can't put a new function into 7.2.x due to the
long-standing rule of not modifying system tables in minor upgrades. So
solving (2) completely needs to wait for 7.3.
You can work around this mis-feature for now by patching 7.2.x,
replacing one of the definitions for date_part in
src/include/catalog/pg_proc.h, oid = 1385 with the following:
select date_part($1, cast((cast($2 as text) || ''+00'') as time with
time zone));
Or, it seems that you can actually drop and replace this built-in
function (I vaguely recall that there used to be problems with doing
this, but it sure looks like it works!):
thomas=# drop function date_part(text,time);
DROP
thomas=# create function date_part(text,time) returns double precision
as '
thomas'# select date_part($1, cast((cast($2 as text) || ''+00'') as time
with time zone));
thomas'# ' language 'sql';
CREATE
thomas=# select extract(epoch from time '00:00:34');date_part
----------- 34
In looking at this issue I did uncover a bug in moving time with time
zones to other time zones:
thomas=# select timetz(interval '01:00', time with time zone
'08:09:10-08'); timetz
----------------00:00:00.00+01
after repairing the offending code in timetz_izone() it seems to do the
right thing:
thomas=# select timetz(interval '01:00', time with time zone
'08:09:10-08'); timetz
-------------17:09:10+01
This last issue will be fixed in 7.2.1. And the function will be renamed
to "timezone()" in 7.3 to be consistant with similar functions for other
data types.
- Thomas
