Chuck:
On Wed, May 1, 2019 at 7:23 PM Chuck Martin <clmartin@theombudsman.com> wrote:
>
> Thanks for the extra information. It is indeed an indexed column. I'll have to think some more about how to address
thisin a general way, as this issue can come up all over. I suppose using
>
> AND datetime <= 'May 1, 2019 24:00'
>
> would produce the same as
>
> AND datetime < 'May 2, 2019'
>
> wouldn't it? I'm not sure one is easier to implement than the other.
At first the <= seems easier, but it is deceiving. So deceiving it is
incorrect, you should use < in both.
I've made a test:
http://sqlfiddle.com/#!17/9eecb/29310
It basically says << select 'May 1 2019 24:00'::timestamp, 'May 2
2019'::timestamp >>
Gives the same result for both, << 2019-05-02T00:00:00Z >>.
So your first condition, using <= is wrong as it will select data just
at 00:00:00 of the next day.
This is why I was telling you to use half-open-ranges, and once you
use half-open ( < ) it is easier to see whats is going on using 'May
2 2019', or 'May 1 2019'::date+1, than remembering a 24:00:00 folds to
the next day due to the peculiarities of text to timestamp conversion
( which allows just this value, but advances the date ).
Also, see that even if you use 'May 1 2019'::date+1,and index can be
used, as the casting and conversions can be constant-folded.
I normally recommend everyone to get used to half-open for time
intervals and conditions, it is much easier to get right. Also,
24:00(:00.0000000) is just one above the limit for dates know, but
earth rotation is slowing, and it may be fixed by either putting more
seconds or more hours, so who knows. But ::date+1 will be correct for
as long as people keep maintaining postgres time arithmetic routines.
Francisco Olarte.
