Re: Unhappiness with forced precision conversion for - Mailing list pgsql-hackers

From Peter Eisentraut
Subject Re: Unhappiness with forced precision conversion for
Date
Msg-id Pine.LNX.4.30.0110052229150.654-100000@peter.localdomain
Whole thread Raw
In response to Unhappiness with forced precision conversion for timestamp  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Martín Marqués
Date:
Subject: Rules and missing inserts
Next
From: Peter Eisentraut
Date:
Subject: Re: Darwin 1.4 (OS X 10.1) Broken Compile, Snapshot and