From: | Tim Landscheidt <tim(at)tim-landscheidt(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Quick Date/Time Index Question |
Date: | 2011-09-22 22:43:43 |
Message-ID: | m3k4908ag0.fsf@passepartout.tim-landscheidt.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Yandong Yao | 2011-09-23 00:46:25 | PSQLException exception when dropping database and calling has_database_privilege: ERROR: database "test_1" does not exist |
Previous Message | Reuven M. Lerner | 2011-09-22 22:38:43 | Re: Speed of lo_unlink vs. DELETE on BYTEA |