Order By Date Difference Between Two Date Columns

Question

I have a table that has two datetime columns (one for start time and one for end time).

I need to be able to select all entries and order them based on the time difference between these two columns (the period between the start and end time columns)

Answer 1

Try this::

select * from table order by TIMEDIFF(to, from)

Answer 2

SELECT ...
FROM ...
ORDER BY DATEDIFF(endDate, starDate);

[edit] The above query works with dates. As pointed out by Sashi Kant, it would ignore the time part of your DATETIME

On the other hand, TIMEDIFF fails if the difference is outside of the TIME range (-838:59:59 to 838:59:59).

A complete solution could be:

SELECT ...
FROM ...
ORDER BY
    DATEDIFF(endDate, starDate),
    TIMEDIFF(TIME(endDate), TIME(starDate));

Probably performs terribly, though... If such precision is required, and if the difference between the two dates may be outside of the TIME range, then splitting each of your DATETIME columns into two DATE and TIME columns would certainly perform better, assuming you would apply one index on each of the four resulting columns.