Re: Unhappiness with forced precision conversion for

Tom Lane writes:

> regression=# select '2001-10-04 13:52:42.845985-04'::timestamp;
>       timestamptz
> ------------------------
>  2001-10-04 13:52:43-04
> (1 row)
>
> Throwing away the clearly stated precision of the literal doesn't
> seem like the right behavior to me.

That depends on the exact interpretation of '::'.

Recall that the SQL syntax for a timestamp literal is actually
   TIMESTAMP 'YYYY-MM-DD HH:MM:SS.XXX....'

with the "TIMESTAMP" required.  The rules concerning this are...
       18) The declared type of a <time literal> that does not specify           <time zone interval> is TIME(P)
WITHOUTTIME ZONE, where P is           the number of digits in <seconds fraction>, if specified, and           0 (zero)
otherwise.The declared 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 (zero) otherwise.
 

which is what you indicated you would expect.

However, if you interpret X::Y as CAST(X AS Y) then the truncation is
entirely correct.

You might expect all of

'2001-10-05 22:41:00'
TIMESTAMP '2001-10-05 22:41:00'
'2001-10-05 22:41:00'::TIMESTAMP
CAST('2001-10-05 22:41:00' AS TIMESTAMP)

to evaluate the same (in an appropriate context), but SQL really defines
all of these to be slightly different (or nothing at all).  This
difference is already reflected in the parser:  The first two are
"constants", the latter two are "type casts".

I think in a consistent extension of the standard, the first two should
take the precision as given, whereas the last two should truncate.

To make the TIMESTAMP in #2 be just a data type vs. meaning TIMESTAMP(0)
in #3 and #4, the grammar rules would have to be beaten around a little,
but it seems doable.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter