From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Query not producing expected result |
Date: | 2019-05-01 17:05:57 |
Message-ID: | 105c087b-71a1-b26a-1453-8320e8318a03@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5/1/19 11:39 AM, Julien Rouhaud wrote:
> On Wed, May 1, 2019 at 6:27 PM 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.
> 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?
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2019-05-01 17:15:34 | Re: Query not producing expected result |
Previous Message | Chuck Martin | 2019-05-01 16:55:48 | Re: Query not producing expected result |