Thread: Doing INTERVAL with NOW() versus casted timestamp

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.

--
Wells Oliver
wellsoliver@gmail.com

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°

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.
>

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