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
Chuck:
On Wed, May 1, 2019 at 6:56 PM Chuck Martin <clmartin@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.
