Re: text -> time cast problem - Mailing list pgsql-hackers

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.

But ...

The SQL92 spec appears to intend that EXTRACT(SECOND) should return
seconds *and* fractional seconds.  In 6.6 syntax rule 4,
        4) If <extract expression> is specified, then
           Case:
           a) If <datetime field> does not specify SECOND, then the data             type of the result is exact
numericwith implementation-             defined precision and scale 0.
 
           b) Otherwise, the data type of the result is exact numeric             with implementation-defined precision
andscale. The             implementation-defined scale shall not be less than the spec-             ified or implied
<timefractional seconds precision> or <in-             terval fractional seconds precision>, as appropriate, of the
       SECOND <datetime field> of the <extract source>.
 

It looks to me like 4b *requires* the fractional part of the seconds
field to be returned.  (Of course, we're blithely ignoring the aspect
of this that requires an exact numeric result type, since our version
of EXTRACT returns float8, but let's not worry about that fine point
at the moment.)

Don't think I want to change this behavior for 7.2, but it ought to be
on the TODO list to fix it for 7.3.


> Digging a bit, I
> noticed the following (discrepancy?).  Is this desired behavior?

> brent=# select "time"('12:00:12.5');
>     time     
> -------------
>  12:00:12.50
> (1 row)

> brent=# select '12:00:12.5'::time;
>    time   
> ----------
>  12:00:12
> (1 row)

> IMO, one of these needs to be fixed before RC1 is rolled.

I'm not convinced that's broken.  You're missing an important point
(forgivable, because Thomas hasn't yet committed any documentation
about it): TIME now implies a precision specification, and the default
is TIME(0), ie no fractional digits.  Observe:

regression=# select '12:00:12.6'::time(0);  time
----------12:00:13
(1 row)

regression=# select '12:00:12.6'::time(2);   time
-------------12:00:12.60
(1 row)

In the pseudo-function-call case, there is no implicit precision
specification and thus the value does not get rounded.

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
I believe SQL92 requires the EXTRACT result to include the fraction.
        regards, tom lane