Re: Query not producing expected result

On Wed, May 1, 2019 at 7:37 PM Chuck Martin <clmartin(at)theombudsman(dot)com> wrote:
>
> Something like daterange would be a solution in some circumstances, but this query is a user-generated one, and they don't have that much control over the query. It has to be modified as needed behind the scenes so that it produces the results they expect. In this instance, I'm now (given the advice received here) inclined to check the value entered when searching for a date, and if no time is entered, add '24:00' to the date.

What I normally do for that is, if the user must enter a date, use
$user_input::date+1 and always go to less than.

But anyway your solution with <= is incorrect. And you have made the
mistake probably because the 24:00 lead you to think postgres will
split the timestamp, compare
the date with may 1 and the time with 24:00, and that is not true. The
less-than option plus one-day add will not lead you to that error.

You can use <= with 23:59:59.9999999999999999999, will be good for
some years if leap-seconds do not bite you.

The problem is when the user enters a date, he wants a date search, so
cast(dateTime as date) <= limit. When he enters a time he does not
usually know what he is asking for ( normally when my users ask for
'May 1 23:15 they want to include up to 23:15:59, users think in
"truncate to my precision, then search inclusively" ). But they begin
to understand it when I ask "ok, twice a month bills, go 1..15 and
16..what ?", much easier to say [YYYY-MM-01 , YYYY-MM-16) and [
YYYY-MM-16, (YYYY-MM-01)+1month)

Francisco Olarte.