Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> For a "timestamp with time zone" column, how do I find all rows where the value is within the beginning and ending of a single date?
> If you compare a date to a timestamp, the date is taken to mean midnight
> of its day. So you need something along the line of
> WHERE timestampcol BETWEEN dateval AND dateval+1
To be exact:
| WHERE timestampcol >= dateval AND timestampcol < dateval+1
because otherwise the start of the next day is included in
the previous and therefore potentially counted twice.
In response to
pgsql-novice by date
|Next:||From: Gavin Flower||Date: 2011-11-04 09:14:23|
|Subject: Re: Query on DATETIME for a date (the whole day)|
|Previous:||From: Tim Landscheidt||Date: 2011-11-04 01:45:51|
|Subject: Re: Foreign key constraint violation when using table inheritance|