Doing INTERVAL with NOW() versus casted timestamp

Wells Oliver <wellsoliver@gmail.com> wrote:

Why does this give me two different results? 'created' is a date field:

SELECT * FROM foo WHERE created >= '2013-02-16 00:00:00'::timestamp - INTERVAL
'24 hours'

test=# select '2013-02-16 00:00:00'::timestamp - INTERVAL '24 hours';
?column?
---------------------
2013-02-15 00:00:00
(1 row)

and

SELECT * FROM foo WHERE created >= NOW() - INTERVAL '24 hours'

test=*# select now() - INTERVAL '24 hours';
?column?
-------------------------------
2013-02-15 16:38:51.362674+01
(1 row)

First returns the 12 rows I expect where the 'created' field is 2012-02-15,
second returns only one.

You can see the difference?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Wells:

The now() function returns not only the current date, but the current
time. So, now() - 24 hours returns yesterday at this time. But,
yesterday at 00:00:00 is not greater than or equal to yesterday at the
current time (unless, of course, you happened to run this at precisely
00:00:00 ...).

I believe that if you use the current_date function, instead of now(),
and subtract 24 hours from that, then you will get the behavior that you
expect.

Good luck,

John

On 2/16/2013 7:25 AM, Wells Oliver wrote:

Why does this give me two different results? 'created' is a date field:

SELECT * FROM foo WHERE created >= '2013-02-16 00:00:00'::timestamp -
INTERVAL '24 hours'

and

SELECT * FROM foo WHERE created >= NOW() - INTERVAL '24 hours'

First returns the 12 rows I expect where the 'created' field is
2012-02-15, second returns only one.

Thank you.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John Shott <shott@stanford.edu> writes:

I believe that if you use the current_date function, instead of now(),
and subtract 24 hours from that, then you will get the behavior that you
expect.

If you're working with dates, rather than timestamps, it's even easier
than that: just add or subtract an integer.

regression=# select current_date;
date
------------
2013-02-16
(1 row)

regression=# select current_date - 1;
?column?
------------
2013-02-15
(1 row)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general