Thread: now() and interval

Just curious, why does this date manipulation return the incorrect value
based on the order of the operation?

output from now() for reference for below:

mydb=> select now();
             now
------------------------------
 2002-04-03 10:02:23.52309-05
(1 row)



mydb=> select interval '1 month' + now();
      ?column?
---------------------
 2002-04-03 00:00:00
(1 row)

mydb=> select now() + interval '1 month';
           ?column?
-------------------------------
 2002-05-03 10:02:44.038337-04
(1 row)

I can only assume it's because now() overwrites some values?  Is this
supposed to work this way?

Travis

"Travis Hoyt" <thoyt@npc.net> writes:
> Just curious, why does this date manipulation return the incorrect value
> based on the order of the operation?
> mydb=> select interval '1 month' + now();
> mydb=> select now() + interval '1 month';

Looking in pg_operator (or "\do +" in psql) shows that there is a
"timestamp plus interval" operator, but no "interval plus timestamp"
operator.  So in the first case the system will pick one of the
available operators and then coerce your values to the appropriate
input datatypes.  I'm too lazy to work out which pair of datatypes is
being selected, but a good bet is that the timestamp from now() is being
cut down to a date.

            regards, tom lane