johnnitro johnnitro - 1 year ago 89
SQL Question

The difference between datetime data in SQL

how can i find the difference between two dateTime store in a MySQL database
e.g the difference in hours between 2016-03-09 04:30:00 and 2016-03-10 03:00:00.
i have tried dateDiff() but it does not compare the hours that is need to see the difference between (2016-03-09 04:30:00) - (2016-03-10 03:10:00).
the order is year-month-day time

Structure of database

The output i need is the number of hours between these times also considering the time as well.

Answer Source

You can use TIMESTAMPDIFF to find the difference between two timestamps

SELECT TIMESTAMPDIFF(HOUR,'2009-05-18 10:00','2009-05-18 11:00'); 

If you want fraction(eg: 1.5 hrs) hours you can do like below

SELECT (UNIX_TIMESTAMP('2012-10-30 10:40')-UNIX_TIMESTAMP('2012-10-30 10:30'))/3600 hour_diff
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download