"David Johnston" <polobo(at)yahoo(dot)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