MySQL - How to get EXACT difference of hours between two dates

Question

I'll illustrate what I would like to get in the following example:

'2010-09-01 03:00:00' - '2010-09-01 00:10:00'

Using TIMEDIFF(), we get 2 as a result. This means, it's not considering the 50 minutes left.

In this case, what I'd like to get is: 50 (minutes) / 60 = 0.83 period. Therefore, the result should be 2.83 and not 2.

Answer 1

select time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600;

+-----------------------------------------------------------------------------+
| time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600 |
+-----------------------------------------------------------------------------+
|                                                                      2.8333 | 
+-----------------------------------------------------------------------------+

Answer 2

What version of MySQL are you using?

SELECT TIMEDIFF('2010-09-01 03:00:00','2010-09-01 00:10:00');

Returns: 02:50:00 for me I'm using 5.0.67 (an older version these days - time for an upgrade). That 2 is likely a cast or truncation by whatever you're using to output the data.

To convert that back into a floating point hour number:

SELECT TIME_TO_SEC(TIMEDIFF('2010-09-01 03:00:00','2010-09-01 00:10:00'))/3600;