Re: Doing INTERVAL with NOW() versus casted timestamp - Mailing list pgsql-general

From John Shott
Subject Re: Doing INTERVAL with NOW() versus casted timestamp
Date
Msg-id 511FA988.6000702@stanford.edu
Whole thread Raw
In response to Doing INTERVAL with NOW() versus casted timestamp  (Wells Oliver <wellsoliver@gmail.com>)
Responses Re: Doing INTERVAL with NOW() versus casted timestamp
List 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.
>

pgsql-general by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: Doing INTERVAL with NOW() versus casted timestamp
Next
From: "Gauthier, Dave"
Date:
Subject: Re: Visual query builder for PosgreSQL?