CODEWITHSUNDEEP
mysql
Raees Khan
Raees Khan

Reputation: 369

how to get time difference in mysql

I have two times start_time and end_time, and I want to get the difference in seconds between start_time and end_time, means if start_time=1:1:0 and end_time=1:2:3 then I should get 63. Please tell what query of MySQL should I apply for this.

Upvotes: 0

Views: 140

Answers (2)

Voidmain
Voidmain

Reputation: 141

I would have just commented this, but just joined. In his requirements, the start time is in the past, but the result is a positive time difference. so

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time)...

should be

SELECT ABS(TIMESTAMPDIFF(SECOND, start_time, end_time))...;

Full example

SET @startDate = NOW() - INTERVAL 63 SECOND;
SET @endDate = NOW();

SELECT ABS(TIMESTAMPDIFF(SECOND, @startDate, @endDate)) AS time_difference;
-- or this
SELECT ABS(TO_SECONDS(@startDate) - TO_SECONDS(@endDate)) AS time_difference;

Upvotes: 0

user1864610
user1864610

Reputation:

TIMESTAMPDIFF() will give you what you want:

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) as timediff from `sometable` WHERE...

MySQL reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff

Upvotes: 1

Related Questions