Re: Quick Date/Time Index Question

Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> 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.

Dare I to say it? :-) Not quite true:

| tim=# SELECT t
| tim-#        FROM (VALUES ('2011-01-01 00:00:00'::TIMESTAMP),
| tim(#                     ('2011-01-02 00:00:00'::TIMESTAMP)) AS d (t)
| tim-#        WHERE t BETWEEN '2011-01-01'::DATE AND ('2011-01-01'::DATE + 1);
|           t
| ---------------------
|  2011-01-01 00:00:00
|  2011-01-02 00:00:00
| (2 Zeilen)

| tim=#

So you would have to assert that no timestamp will ever fall
on midnight.

Tim