On Wed, May 1, 2019 at 6:27 PM Chuck Martin <clmar...@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'