From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200512221601.jBMG1H807869@candle.pha.pa.us>
Subject: Re: Querying date_time for date only ?
In-Reply-To: <200512201459.07358.aarni@kymi.com>
To: aarni@kymi.com
Date: Thu, 22 Dec 2005 11:01:17 -0500 (EST)
CC: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=UNKNOWN-8BIT

Aarni Ruuhimäki wrote:
> Hello List,
> 
> I have a time stamp without time zone field, YYYY-MM-DD hh:mm:ss, in
> my table.  I want to also find something just for a particular day
> regardless of the time.
> 
> (Pg)SQL way to do this ?

Yes.  You can use date_trunc():

	test=> select date_trunc('day', '2004-01-04 04:02:03'::timestamp);
	     date_trunc
	---------------------
	 2004-01-04 00:00:00
	(1 row)

so it would be date_trunc('day', col) = '2004-01-05'.  The problem with
this is that you cannot use an index unless you create an expression
index on the date_trunc() function call.  Another option is to do
something like:

	WHERE col >= '2004-01-04' AND col < '2004-01-05'

If the date isn't a constant, you have to use date_trunc() on those, and
add one day to the second comparison:

	  WHERE col >= date_trunc('day', col2) AND 
		col < date_trunc('day', col2) + '1 day';

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073