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. Chuck Martin Avondale Software On Wed, May 1, 2019 at 1:16 PM Francisco Olarte <fola...@peoplecall.com> wrote: > Chuck: > > On Wed, May 1, 2019 at 6:56 PM Chuck Martin <clmar...@theombudsman.com> > wrote: > > 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. > > The latter may prevent index usage, if you've got one. > > One think I've said before. Dates are integer-like ( they are > countable ), but timestamps are real-like ( they may be countable due > to finite precision, like float or doubles are, but you should not > count on it ). For real-like stuff it is normally better to work with > half-open ranges, which in your case would translate to to query for > > event.DateTime < 'May-2-2019' ( the minus-ininity <= DateTime > would for the other end ). > > The reason is you can cover the whole DateTime domain with > non-intersecting half-open ranges, but not with open or closed ones > and, as a side effect, the starting point of a range is the same as > the next one ( also, this does not need cast, better for the optimizer > ) ( If your input is an end date I normally pass this to timestamp > using ts < '$inclusive_end_date'::date + 1 ) ( I do lots of reports of > this kind, and using this helps a lot once you get the hang of it > after a couple tests ). > > ( I use half-open for dates to, for uniformity, and for being able to > use FEBRUARY= dateColumn >= "YEAR-02-01' and dateColumn < > "YEAR-03-01", no need to worry about leap years or remembering how > many days each month has. Generally they are easier, the only con I've > found is inability to use between ). > > Francisco Olarte. > > >