CODEWITHSUNDEEP
mysqltime
NeilG
NeilG

Reputation: 703

Difference in minutes from two time fields in MySQL

I set up my MySQL database with the field 'time'

It is not HH:MM in the traditional sense, it is the time an event occurred, so an event with the value of 5:45 occurred with 5 minutes 45 seconds left in a game. 12:25 occurred with 12 minutes and 25 seconds left, etc.

I would like to be able to find out the total time elapsed, so if I have an event that occurred at 12:25 and the next event occurred at 5:45 I want to be able to get the difference, which would be equal to 6:40. Then, I would like to express this as a decimal, in this case 6.67.

Is this possible?

Upvotes: 55

Views: 132244

Answers (5)

Piyush Pranjal
Piyush Pranjal

Reputation: 584

If someone wants the result as an Integer:

SELECT TIME_TO_SEC(TIMEDIFF('12:25', '12:45')) DIV 60;

Or if only Positive Integer is required:

SELECT ABS(TIME_TO_SEC(TIMEDIFF('12:25', '12:45')) DIV 60);

Upvotes: 0

A T
A T

Reputation: 13826

For me this worked:

TIMESTAMPDIFF(MINUTE, T0.created, T0.modified)

Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff

Upvotes: 76

Shawn
Shawn

Reputation: 454

Just use 

TIMEDIFF(fromtime, totime):

SELECT TIMEDIFF("12:25", "5:45");

If you need a decimal, use TIME_TO_SEC()/3600 (it assumes you passed it seconds, but the format you're storing the times in is vague and SQL probably will interpret them as HH:MM - you can fix this with CONCAT("00:",MinuteSecondField) maybe?) - then you can use TIME_TO_SEC()/60, which is more correct)

Upvotes: 43

Alex Pakka
Alex Pakka

Reputation: 9706

I used UNIX_TIMESTAMP(event1)-UNIX_TIMESTAMP(event2), which gives you seconds between events. Divide it by 60.0 and you will get a decimal as per your requirement. This solution assumes, your columns are date-compatible values. It will work really fast if they are TIMESTAMPs.

UPDATE: This solution only works with timestamp data types, not time datatypes as in original question.

Upvotes: 4

artfulrobot
artfulrobot

Reputation: 21397

I needed similar. Two useful functions: TIME_TO_SEC and SUBTIME.

e.g. if your time fields were normal HH:MM times, then 

SELECT (TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))/60 AS `minutes`

In your case, as I understand it, the times are backwards, ie. 12:00<6:00 so your end_time and start_time would need swapping.

If you wanted the output in HH:MM:SS you could do

SELECT SUBTIME(end_time, start_time)

Upvotes: 23

Related Questions