How to get the difference between two dates rounded to hours
SqlMysqlSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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 |
+-----------------------------------------------------------------------------+
Solution 2 - Sql
I think a more complete answer is
select time_format(timediff(onedateinstring,anotherdateinstring),'%H:%i:%s')
This allows you to see the hours, minutes, seconds, etc. that you wish to see in the format you want...
More information on time_format: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_time-format
Solution 3 - Sql
Use TIMESTAMPDIFF for exact number of hours :
SELECT
b.`Start_Date`,
b.`End_Date`,
TIMESTAMPDIFF(HOUR, b.`Start_Date`, b.`End_Date`) AS `HOURS`
FROM my_table b;
Solution 4 - Sql
You could try the following:
time_format(timediff(max(l.fecha), min(l.fecha) ),'%H:%m') //Hora y minutos
Solution 5 - Sql
Its a very old thread, but you can also try TIMESTAMPDIFF and give MINUTE, HOUR, SECONDS as the qualifier.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff
Solution 6 - Sql
MySQL get the number of hours between timestamps:
select timestampdiff(second,'2010-09-01 00:10:00', '2010-09-01 03:00:00')/3600;
Solution 7 - Sql
If you are using timestamp, this could be the solution in MySQL using SQL.
SELECT TIMESTAMPDIFF(HOUR, '2010-11-29 13:13:55', '2010-11-29 13:16:55') as newtable;
| newtable |
7
1 row in set (0.01 sec)
Solution 8 - Sql
SELECT ABS(TIME_TO_SEC(TIMEDIFF('2010-09-01 03:00:00', '2010-09-01 00:10:00')) / 3600)
> Result: 2.8333
Solution 9 - Sql
Using UNIX_TIMESTAMP:
SELECT (UNIX_TIMESTAMP(End)-UNIX_TIMESTAMP(Start))/3600
AS Difference_in_hours
FROM Table