Thread: change in now() and ago with 7.x

Pre-7.x, we used to use a line like: "  request.date_submitted > (now()
+ '24 hours ago')"
to pull data created in the past day (used against a timestamp).
Something changed with 7.x  that blows that line out.

I am looking for a work around for use with 7.x.  Currently I'm using
"select now()::DATETIME - '24 hours'::TIMESPAN" and using the return as
a variable to test against -- too much code.  I'd rather just fix the
one line rather than hack all the existing scripts.

So what suggestions do you have and could you point me to what changed
between 6.x and 7.x that broke my scripts (I've got to tell the boss
something :-) )

Thanks!

Dave Stokes

> Pre-7.x, we used to use a line like: "  request.date_submitted > (now()
> + '24 hours ago')"
> to pull data created in the past day (used against a timestamp).
> Something changed with 7.x  that blows that line out.

You will have to be more specific on what is not working for you. afaict
that line should still work:

lockhart=# select * from t1 where d < now() + '24 hours ago';
 i |                d
---+---------------------------------
 1 | Mon Jul 03 00:00:60.00 2000 PDT
...

(though my personal preference is for something like (timestamp 'now' -
interval '24 hours') ).

One thing that changed for 7.0 is that "timestamp" is the primary
date/time type. Make sure that you do everything using that type, rather
than using "datetime"; the latter is sort-of supported by the parser to
help with upgrades, but that support is not complete.