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

Peter Eisentraut <peter_e@gmx.net> writes:
> 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 is the number of digits in
>             <seconds fraction>, if specified, and 0 otherwise. The data
>             type of a <time literal> 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.

I believe it is a reasonable extension for us to accept
    time(2) '17:12:28.123'

as producing '17:12:28.12'.  This accords with our general extension to
accept <any-type-name> <string-literal> as a typed constant, whereas I
believe that SQL92 only envisions certain specific type names being used
in this way.

But you are definitely right that
    time '17:12:28.123'

should not strip the fractional digits.  From this it is a small step
to asserting that
    '17:12:28.123'::time

shouldn't either; in general we'd like TYPE 'LIT' and 'LIT'::TYPE to
produce the same answers.

> 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.

I think you have put your finger on the heart of the problem.  Some
further research shows that it's not EXTRACT(SECOND) that is refusing
to produce a fractional part; the problem is with the time literal.

As an experiment, I made the attached patch to gram.y, which implements
the change I originally proposed in the older thread: time/timestamp
type names that don't explicitly specify a precision should get typmod
-1, which will mean no coercion to a specific precision.  This does not
follow SQL92's notion of having specific default precisions for these
types, but it does agree with our current handling of NUMERIC (no forced
default precision there either).  I make the following observations:

1. All the regression tests still pass.

2. The case I was unhappy about in October works nicely now:

regression=# select '2001-10-04 13:52:42.845985-04'::timestamp;         timestamptz
-------------------------------2001-10-04 13:52:42.845985-04
(1 row)

3. The cases Brent is unhappy about all pass:

regression=# SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');date_part
-----------     28.5
(1 row)

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

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


This needs further thought and testing before I'd dare call it a
solution, but it does seem to suggest the direction we should pursue.
        regards, tom lane

*** src/backend/parser/gram.y.orig    Thu Nov 15 23:08:33 2001
--- src/backend/parser/gram.y    Tue Dec  4 17:52:10 2001
***************
*** 4058,4064 ****                 {                     $$ = $1;                     if ($2 != -1)
!                         $$->typmod = ((($2 & 0x7FFF) << 16) | 0xFFFF);                 }         | ConstInterval '('
Iconst')' opt_interval                 {
 
--- 4058,4064 ----                 {                     $$ = $1;                     if ($2 != -1)
!                         $$->typmod = (($2 << 16) | 0xFFFF);                 }         | ConstInterval '(' Iconst ')'
opt_interval                {
 
***************
*** 4328,4337 ****                      * - thomas 2001-09-06                      */                     $$->timezone
=$2;
 
!                     /* SQL99 specified a default precision of six.
!                      * - thomas 2001-09-30
!                      */
!                     $$->typmod = 6;                 }         | TIME '(' Iconst ')' opt_timezone                 {
--- 4328,4334 ----                      * - thomas 2001-09-06                      */                     $$->timezone
=$2;
 
!                     $$->typmod = -1;                 }         | TIME '(' Iconst ')' opt_timezone                 {
***************
*** 4352,4361 ****                         $$->name = xlateSqlType("timetz");                     else
      $$->name = xlateSqlType("time");
 
!                     /* SQL99 specified a default precision of zero.
!                      * - thomas 2001-09-30
!                      */
!                     $$->typmod = 0;                 }         ; 
--- 4349,4355 ----                         $$->name = xlateSqlType("timetz");                     else
      $$->name = xlateSqlType("time");
 
!                     $$->typmod = -1;                 }         ; 
***************
*** 5603,5609 ****                     n->val.val.str = $2;                     /* precision is not specified, but
fieldsmay be... */                     if ($3 != -1)
 
!                         n->typename->typmod = ((($3 & 0x7FFF) << 16) | 0xFFFF);                     $$ = (Node *)n;
             }         | ConstInterval '(' Iconst ')' Sconst opt_interval
 
--- 5597,5603 ----                     n->val.val.str = $2;                     /* precision is not specified, but
fieldsmay be... */                     if ($3 != -1)
 
!                         n->typename->typmod = (($3 << 16) | 0xFFFF);                     $$ = (Node *)n;
  }         | ConstInterval '(' Iconst ')' Sconst opt_interval