Thread: Odd behavior of type coercion for datetime

With 6.4 or current sources, I find that coercing a datetime to float8
is a no-op:

treetest=> create table dt1 (t datetime);
CREATE
treetest=> insert into dt1 values('now');
INSERT 159593 1
treetest=> select t from dt1;
t
----------------------------
Sun Jan 24 18:28:50 1999 EST
(1 row)

treetest=> select t::float8 from dt1;
?column?
----------------------------
Sun Jan 24 18:28:50 1999 EST
(1 row)


I was expecting to get either some numerical equivalent to the date
(seconds since 1970 would do nicely, but I'll take the internal rep...)
or an error message saying "no such conversion available".  I was
certainly not expecting to find that the result was still a datetime,
but such it appears to be.  This is a bug, wouldn't you say?

What's even more curious is that coercing to int4 does produce
something numeric:

treetest=> select t::int4 from dt1;    int4
---------
-29464270
(1 row)

        regards, tom lane

> With 6.4 or current sources, I find that coercing a datetime to float8
> is a no-op:
> treetest=> select t::float8 from dt1;
> ----------------------------
> Sun Jan 24 18:28:50 1999 EST
> (1 row)
> I was expecting to get either some numerical equivalent to the date
> or an error message saying "no such conversion available".  I was
> certainly not expecting to find that the result was still a datetime,
> but such it appears to be.  This is a bug, wouldn't you say?

Sure, now that you bring it up. You are running into the code associated
with the following comment (remember that type coersions are done as
function calls):

/** See if this is a single argument function with the function* name also a type name and the input argument and type
name*binary compatible...* This means that you are trying for a type conversion which does not* need to take place, so
we'lljust pass through the argument itself.* (make this clearer with some extra brackets - thomas 1998-12-05)*/
 

If the operation stays internal, the result behaves correctly, but if
the coersion is going to an output routine turning it into a no-op is
not such a good idea. Actually, this code is hit only in the case that
the requested function/coersion does not exist at all, and is there as a
last-gasp effort to DTRT.

> What's even more curious is that coercing to int4 does produce
> something numeric

because int4 and datetime are *not* binary compatible (and don't claim
to be), you never hit this too-aggressive optimization.

There are probably a couple of problems here: equivalencing datetime and
float8 might be too much of a cheat, and the "drop the function call"
optimization breaks down if the output representation of the two types
is different. Not sure if I can force the apparent type of the column
for purposes of output, but that would help.

We shouldn't allow this behavior to persist into v6.5, though I'm not
certain what the best solution is yet. I conveniently ignored the fact
that there are reserved values for datetime which give astoundingly
non-intuitive results if interpreted as float8; for example, "now" is a
floating point number near, but not at, zero, as is "current". So I
should probably remove the float8 == datetime equivalence as a start...
                     - Tom

Have we dealt with this?


> With 6.4 or current sources, I find that coercing a datetime to float8
> is a no-op:
> 
> treetest=> create table dt1 (t datetime);
> CREATE
> treetest=> insert into dt1 values('now');
> INSERT 159593 1
> treetest=> select t from dt1;
> t
> ----------------------------
> Sun Jan 24 18:28:50 1999 EST
> (1 row)
> 
> treetest=> select t::float8 from dt1;
> ?column?
> ----------------------------
> Sun Jan 24 18:28:50 1999 EST
> (1 row)
> 
> 
> I was expecting to get either some numerical equivalent to the date
> (seconds since 1970 would do nicely, but I'll take the internal rep...)
> or an error message saying "no such conversion available".  I was
> certainly not expecting to find that the result was still a datetime,
> but such it appears to be.  This is a bug, wouldn't you say?
> 
> What's even more curious is that coercing to int4 does produce
> something numeric:
> 
> treetest=> select t::int4 from dt1;
>      int4
> ---------
> -29464270
> (1 row)
> 
> 
>             regards, tom lane
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Have we dealt with this?
>> With 6.4 or current sources, I find that coercing a datetime to float8
>> is a no-op:

With current sources I get

regression=> select t from dt1;
t
----------------------------
Mon Mar 15 09:56:01 1999 EST
(1 row)

regression=> select t::float8 from dt1;
ERROR:  Bad float8 input format 'Mon Mar 15 09:56:01 1999 EST'
regression=>

which seems to be reasonable behavior.  (I believe Tom made this
happen by removing binary equivalence between datetime and float8.)
        regards, tom lane