Re: Query not producing expected result

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 <folarte(at)peoplecall(dot)com>
wrote:

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