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) WITHOUT TIME 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(at)gmx(dot)net http://funkturm.homeip.net/~peter