From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Chuck Martin <clmartin(at)theombudsman(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query not producing expected result |
Date: | 2019-05-01 16:40:23 |
Message-ID: | CAKFQuwYH8-zkGPOPrE9zRQ_z_PPjS5db8i6Vw9iX_BLYaJn7vQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, May 1, 2019 at 9:27 AM Chuck Martin <clmartin(at)theombudsman(dot)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.
>
>
The field is a timestamp - which means there is a time involved. If you
don't specify one explicitly that time is going to be midnight. 9:52AM on
the 1st is after midnight on the 1st so the first query doesn't return the
9:52AM record.
IOW, the DateTime field remains as-is and the comparator is turned into a
timestamp without time zone.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-05-01 16:41:24 | Re: Query not producing expected result |
Previous Message | Julien Rouhaud | 2019-05-01 16:39:22 | Re: Query not producing expected result |