CODEWITHSUNDEEP
mysqldatetime
vallete7
vallete7

Reputation: 123

Difference in hours and minutes between two datetimes MySQL

I've a query in MySQL that is returning the difference between two datetimes. The problem is that the query is giving only the difference in hours.

Example:

Datetime1 is 2015-12-16 08:00:00 and datetime2 is 2015-12-16 10:45:00

I want the difference to be returned as: 02:45:00 and not only 02:00:00

My query is:

$query1 = "SELECT X, 
                  time_format(SUM(abs(timediff(datetime2, datetime1))),'%H:%m:%s') as SUM 
           FROM table1, table2 
           WHERE table1.Y = table2.Y 
             and DATE(datetime1) >= '$datepicker' 
             AND DATE(datetime1) <= '$datepicker1' 
           GROUP BY table.Y";

Upvotes: 4

Views: 6992

Answers (2)

Amit Shah
Amit Shah

Reputation: 1380

Try this,

select timediff('2015-12-16 08:00:00','2015-12-16 10:45:00')

it is working.

Thanks Amit

Upvotes: 3

RiggsFolly
RiggsFolly

Reputation: 94642

As far as I can tell your query syntax for working out the time difference is correct except for the formatting in the time_format().

The format for minutes is %i and not %m so change to this and it should work as expected

time_format(SUM(abs(timediff(datetime2, datetime1))),'%H:%i:%s') as SUM 
                                                         ^^

Upvotes: 4

Related Questions