Eimantas Gabrielius Eimantas Gabrielius - 6 months ago 15
SQL Question

MySQL ORDER BY combining two dates closest to today

I have a table

... | reservation_from | reservation_to | ...
1 | 2016-05-13 10:00:00 | 2016-05-21 10:00:00 | ...
2 | 2016-05-13 20:00:00 | 2016-06-29 14:00:00 | ...
3 | 2016-05-01 10:00:00 | 2016-05-13 16:00:00 | ...


now i have to order and get reservations for closest day to today.

For example now is 2016-05-13, so no matter if reservation is going to start or going to end, i have to get results closest to current time. I want to order that like that

... | reservation_from | reservation_to | ...
1 | 2016-05-13 10:00:00 | 2016-05-21 10:00:00 | ...
2 | 2016-05-01 10:00:00 | 2016-05-13 16:00:00 | ...
3 | 2016-05-13 20:00:00 | 2016-06-29 14:00:00 | ...


As you see, the second result
reservation_from
is 05-01 but its
reservation_to
is less than 3rd
reservation_from


I dont know if I'm clear with the question, if no, please comment it what should i specify more.

Answer

You want to order by the lesser time difference to now:

select *
from mytable
order by 
  least(abs(timestampdiff(second, now(), reservation_from)),
        abs(timestampdiff(second, now(), reservation_to)));

You can add futher criteria in order to deal with ties of course.

Comments