Re: Query not producing expected result

On 5/1/19 10:51 AM, Francisco Olarte 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

You will have to explain further as I am not seeing it:

test_(postgres)# select '2019-05-01 9:52' <= '2019-05-01 24:00'::timestamp;
?column?
----------
t

test_(postgres)# select '2019-05-01 24:00' <= '2019-05-01 24:00'::timestamp;
?column?
----------
t

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com