From: | Chuck Martin <clmartin(at)theombudsman(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query not producing expected result |
Date: | 2019-05-01 16:55:48 |
Message-ID: | CAFw6=U3wV3NDt3c8sqHiE4QLRufy3s8d21BaLdkRLpPNUEh9ww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks, guys. It should have been obvious to me, but wasn't.
I found the correct result was returned with either
AND event.DateTime <= 'May-1-2019 24:00'
or
AND event.DateTime::date <= 'May-1-2019'
The latter seems best.
Chuck Martin
Avondale Software
On Wed, May 1, 2019 at 12:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Chuck Martin <clmartin(at)theombudsman(dot)com> writes:
> > 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".
>
> Well, no, since the implied value of the constant is 'May-1-2019 00:00'.
>
> If you only want 1-day precision of the comparison, maybe you should cast
> or truncate the timestamp down to date.
>
> regards, tom lane
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-05-01 17:05:57 | Re: Query not producing expected result |
Previous Message | Tom Lane | 2019-05-01 16:41:24 | Re: Query not producing expected result |