Re: Query not producing expected result - Mailing list pgsql-general

On 5/1/19 10:15 AM, Francisco Olarte wrote:
> 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 ).

Would daterange help/simplify?:

create table dt_test(id integer, dt_fld date);

insert into dt_test values (1, '2019-02-03'), (2, '2019-02-26'), (3,
'2019-03-01');

select dt_fld from dt_test where  dt_fld  <@  daterange('2019-02-01',
'2019-03-01');
    dt_fld
------------
  2019-02-03
  2019-02-26

>
> Francisco Olarte.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com