I need to calculate the average completion time of an operation for something I’m working on at the moment. We’re using MariaDB.
The natural way to do this is:
|
1 |
SELECT AVG(TIMEDIFF(completed_at, started_at)) FROM operation; |
However, the values of the Time data type in MySQL can only range between ‘-838:59:59’ to ‘838:59:59’. This becomes an issue if you have to calculate time differences on two dates or timestamps.
The timediff() function returns values in Time data type, and thus the results are subject to the limitations described above. The above code will return a NULL value, if your total TIMEDIFF exceeds the value range.
A good alternative solution is to use the timestampdiff() function.
The following SQL statement will work properly in most cases:
|
1 |
SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, completed_at, started_at))) FROM operation; |
The sec_to_time() function will translate the seconds returned by the timestampdiff() function into a Time datatype. Please note that if the average time difference in seconds exceeds the Time data type range described above, your results will be limited to the max (or min) value of the Time data type. I’m using the sec_to_time() in a context that would never exceed the range limitations, so for me this solution will work well.