Re: Quick Date/Time Index Question - Mailing list pgsql-general

From Tom Lane
Subject Re: Quick Date/Time Index Question
Date
Msg-id 12016.1316730674@sss.pgh.pa.us
Whole thread Raw
In response to Quick Date/Time Index Question  ("David Johnston" <polobo@yahoo.com>)
List pgsql-general
"David Johnston" <polobo@yahoo.com> writes:
> I have a database field that stores a timestamp to second+ precision;
> however, I want to search against it only to day precision.  If I leave the
> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I
> get no results (OK, fine) but then I cast the field to date "WHERE
> field::date BETWEEN date0 AND date0" and get the expected results.

Try "WHERE field BETWEEN date0 AND date0+1".  When comparing a date to a
timestamp, the date is considered to represent midnight of its day, so
you're testing for a zero-width range there.

            regards, tom lane

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Replication between 64/32bit systems?
Next
From: Jeff Davis
Date:
Subject: Re: Is 9.1 considered more stable/robust than 9.0.4 ?