On Wed, May 1, 2019 at 7:37 PM Chuck Martin <clmar...@theombudsman.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.
