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