Chuck: On Wed, May 1, 2019 at 7:23 PM Chuck Martin <clmartin@xxxxxxxxxxxxxxxx> wrote: > > Thanks for the extra information. It is indeed an indexed column. I'll have to think some more about how to address this in 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.
