CODEWITHSUNDEEP
sqlmysql
Brian Roisentul
Brian Roisentul

Reputation: 4750

How to get the difference between two dates rounded to hours

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.

Upvotes: 69

Views: 108184

Answers (9)

Syed Tabish Ali
Syed Tabish Ali

Reputation: 313

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

Result: 2.8333

Upvotes: 5

Paul Chris Jones
Paul Chris Jones

Reputation: 2919

Using UNIX_TIMESTAMP:

SELECT (UNIX_TIMESTAMP(End)-UNIX_TIMESTAMP(Start))/3600
AS Difference_in_hours 
FROM Table

Upvotes: 1

spacedev
spacedev

Reputation: 1190

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)

Upvotes: 3

fgimenez
fgimenez

Reputation: 138

You could try the following:

time_format(timediff(max(l.fecha), min(l.fecha) ),'%H:%m') //Hora y minutos

Upvotes: 13

karthik
karthik

Reputation: 7559

MySQL get the number of hours between timestamps:

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

Upvotes: 5

SafeGuy
SafeGuy

Reputation: 369

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;

Upvotes: 24

riahc3
riahc3

Reputation: 878

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

Upvotes: 21

BSingh
BSingh

Reputation: 451

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

Upvotes: 9

Matthew
Matthew

Reputation: 48304

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 | 
+-----------------------------------------------------------------------------+

Upvotes: 125

Related Questions