Hello, I have a table with a list of times. When the user provides my application with a desired time, I want to show them the 5 times from the table that are closest to their input. I expected to do this using abs() like such: select mytime from mytable order by abs(usertime-mytime) asc limit 5; However, the difference between times is an interval, and there appears to be no absolute value operator for those. My next thought was to convert the interval into integer like such: select mytime from mytable order by abs((usertime-mytime) / interval ‘1 minute’) asc limit 5; However… there is no operator for interval division either. The best solution I’ve come up with is to use a case statement… select mytime from mytable order by case when (usertime-mytime) < interval ‘0’ then (mytime-usertime) else (usertime-mytime) end asc limit 5; Is this ugly query really necessary for postgres? |
