Re: text -> time cast problem

Tom Lane writes:

> Brent Verner <brent@rcfile.org> writes:
> > I noticed an incorrect example in doc/src/sgml/func.sgml...
> > brent=# SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
> >  date_part
> > -----------
> >         28
> > (1 row)
>
> > The documentation says this should return 28.5.
>
> Historically we've made EXTRACT(SECOND) return integral seconds, with
> MILLISECOND/MICROSECOND field names for the fractional seconds.  So the
> docs are incorrect with respect to the actual code behavior.

Nope, the docs represent the behavior of the code at the time the docs
were written.  The code is now in error with respect to the documented
behaviour.  A quick check shows that PostgreSQL 7.0.2 agrees with
including the fractional part.  Probably this was broken as part of the
time/timestamp precision changes.  Definitely looks like a show-stopper to
me.

> BTW, this means that
>
> SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
>
> *should* return 28, because the TIME literal is implicitly TIME(0).
> But if it were written TIME(1) '17:12:28.5' or more precision, then

That appears to be what it does, but it's not correct.  I point you to
SQL92:
        16)The data type of a <time literal> that does not specify <time           zone interval> is TIME(P), where P
isthe number of digits in           <seconds fraction>, if specified, and 0 otherwise. The data           type of a
<timeliteral> that specifies <time zone interval>           is TIME(P) WITH TIME ZONE, where P is the number of digits
in          <seconds fraction>, if specified, and 0 otherwise.
 

In this "time literal" context, TIME does not take a precision value at
all.  The new code certainly has this wrong.

For details, I refer you to my Oct 5 message "Unhappiness with forced
precision conversion for timestamp", where we already discussed
essentially the same issue, but apparently we never did anything about it.

-- 
Peter Eisentraut   peter_e@gmx.net