On 5/1/19 10:15 AM, Francisco Olarte wrote:
Chuck:
On Wed, May 1, 2019 at 6:56 PM Chuck Martin <clmartin@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxx
