Difference in minutes from two time fields in MySQL

Question

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?

Thanks in advance.

question from:https://stackoverflow.com/questions/5070111/difference-in-minutes-from-two-time-fields-in-mysql

Answer 1

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)