Thread: timestamp_part() bug?
I see following in the manual: ------------------------------------------------------------------- The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT EXTRACT(MILLISECONDS FROMTIME '17:12:28.5'); Result: 28500 ------------------------------------------------------------------- And I see: test=# select current_timestamp,extract(milliseconds from current_timestamp); timestamptz | date_part -------------------------------+-----------2002-02-27 14:45:53.945529+09 | 945.529 (1 row) Apparently there's an inconsistency among manuals, timestamp(tz)_part and timetz_part. Does anybody know which one is correct? -- Tatsuo Ishii
On Wed, Feb 27, 2002 at 05:07:50PM +0900, Tatsuo Ishii wrote:
> I see following in the manual:
>
> -------------------------------------------------------------------
> The seconds field, including fractional parts, multiplied by
> 1000. Note that this includes full seconds.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
> Result: 28500
> -------------------------------------------------------------------
>
> And I see:
>
> test=# select current_timestamp,extract(milliseconds from current_timestamp);
> timestamptz | date_part
> -------------------------------+-----------
> 2002-02-27 14:45:53.945529+09 | 945.529
> (1 row)
>
> Apparently there's an inconsistency among manuals, timestamp(tz)_part
> and timetz_part. Does anybody know which one is correct?
I hope bug is in the manual -- for example minutes the "extract" returns without hours. Is any matter why returs
milliseconswith seconds?If somebody wants milliseconds with seconds:
# select extract(SECONDS from '14:45:53.945529'::time) * 1000; ?column?
------------------53945.5289999969
(1 row)
BTW, to_char() retuns milliseconds without seconds too:
test=# select to_char('2002-02-27 14:45:53.945529+09'::timestamp, 'MS');to_char
---------946
(1 row)
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
> I see following in the manual: > > ------------------------------------------------------------------- > The seconds field, including fractional parts, multiplied by > 1000. Note that this includes full seconds. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); > Result: 28500 > ------------------------------------------------------------------- > > And I see: > > test=# select current_timestamp,extract(milliseconds from current_timestamp); > timestamptz | date_part > -------------------------------+----------- > 2002-02-27 14:45:53.945529+09 | 945.529 > (1 row) > > Apparently there's an inconsistency among manuals, timestamp(tz)_part > and timetz_part. Does anybody know which one is correct? As far as I know, allowing MILLISECONDS etc. for the first arugument of EXTARCT is a PostgreSQL extention and we should decide what to do by ourselves. My proposal is fixing timestamp(tz)_part so that it returns "the seconds field, including fractional parts, multiplied by > 1000. Note that this includes full seconds" as the manual stats, since this would keep the consistency and also have the least impact for existing applications. Opinion? -- Tatsuo Ishi
> > I see following in the manual: > > > > ------------------------------------------------------------------- > > The seconds field, including fractional parts, multiplied by > > 1000. Note that this includes full seconds. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); > > Result: 28500 > > ------------------------------------------------------------------- > > > > And I see: > > > > test=# select current_timestamp,extract(milliseconds from current_timestamp); > > timestamptz | date_part > > -------------------------------+----------- > > 2002-02-27 14:45:53.945529+09 | 945.529 > > (1 row) > > > > Apparently there's an inconsistency among manuals, timestamp(tz)_part > > and timetz_part. Does anybody know which one is correct? > > As far as I know, allowing MILLISECONDS etc. for the first arugument > of EXTARCT is a PostgreSQL extention and we should decide what to do > by ourselves. > > My proposal is fixing timestamp(tz)_part so that it returns "the > seconds field, including fractional parts, multiplied by > 1000. Note > that this includes full seconds" as the manual stats, since this would > keep the consistency and also have the least impact for existing > applications. Fix committed into both current and 7.2-stable. -- Tatsuo Ishii
There is a problem with epoch as well that was not in the 7.1.3 7.1.3# select extract(epoch from '00:00:34'::time), now(); 7.1.3# 34 2002-03-05 22:13:16 +01 7.2# select extract(epoch from '00:00:34'::time), now(); 7.2# 3634 2002-03-05 22:13:16 +01 7.2# select extract(epoch from '00:00:34'::time without time zone), now(); 7.2# 3634 2002-03-05 22:13:16 +01 Is that a bug or I didn't understand the new date/time types ?
> There is a problem with epoch as well that was not in the 7.1.3
> 7.1.3# select extract(epoch from '00:00:34'::time), now();
> 7.1.3# 34 2002-03-05 22:13:16 +01
> Is that a bug or I didn't understand the new date/time types ?
Looks like a bug (or at least it looks like it behaves differently than
I would expect). Thanks for the report; I'll look at it asap.
- Thomas
> 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