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.
