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 <fola...@peoplecall.com> wrote: > 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. > > >
