Re: Query not producing expected result

On 5/1/19 11:39 AM, Julien Rouhaud wrote:

On Wed, May 1, 2019 at 6:27 PM Chuck Martin <clmartin@theombudsman.com> wrote:

I need help figuring out why a query is not returning the records I expect it to. I'm searching on a DateTime column (timestamp without time zone - not nullable). The query includes:
AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-1-2019'  AND event.EventDone < 1

This does not return a record that has a DateTime value "May 1, 2019 9:52 AM". If I modify the query to:
AND event.Primaryresp_fkey = 5000011 AND event.DateTime <= 'May-2-2019'  AND event.EventDone < 1

it does return the record in question. I assume I am not doing the data comparison correctly, but don't know why.

That's because it's using a timestamp comparison, not a date
comparison.  So 'May-1-2019' is casted to timestamp, which gives
2019-05-01 00:00:00.

If you want a comparison based on date instead of timestamp , you have
to ask to it explicitly.  For instance: event.DateTime::date <=
'May-1-2019'

Wouldn't that stop the query planner from using any index on event.DateTime?