CODEWITHSUNDEEP
mysqlsql
Krishneel Singh
Krishneel Singh

Reputation: 343

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.

Upvotes: 0

Views: 50

Answers (2)

sumit
sumit

Reputation: 15464

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

One simple method is to use to_seconds():

select to_seconds(dt1) - to_seconds(dt2)

This gives the difference in seconds. Then you can divide by 60*60 to get hours or 24*60*60 for days.

Upvotes: 0

Related Questions