Skip site navigation (1) Skip section navigation (2)

Re: Query on DATETIME for a date (the whole day)

From: Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query on DATETIME for a date (the whole day)
Date: 2011-11-04 02:02:05
Message-ID: m34nyk1w82.fsf@passepartout.tim-landscheidt.de (view raw or flat)
Thread:
Lists: pgsql-novice
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.

Tim


In response to

Responses

pgsql-novice by date

Next:From: Gavin FlowerDate: 2011-11-04 09:14:23
Subject: Re: Query on DATETIME for a date (the whole day)
Previous:From: Tim LandscheidtDate: 2011-11-04 01:45:51
Subject: Re: Foreign key constraint violation when using table inheritance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group