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?