Re: Query not producing expected result

Ok, I see that my assumptions were incorrect. In this instance, the use of
< date+1 will return what is expected, where my solution might not have.
For other circumstances, I want to explore tsrange.

And, no, I'm not in Avondale, CA, but Decatur, GA (a few miles from
Avondale Estates, where I once lived).

Chuck Martin
Avondale Software

On Wed, May 1, 2019 at 1:52 PM Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> 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.
>
>
>