How to Calculate the Sum of Time Intervals in MySQL?

Calculating and Summing Time Intervals in MySQL

When dealing with time-related data in MySQL, it's often necessary to calculate time intervals and sum them up. This arises frequently in scenarios where one needs to track total working hours or measure performance time differences.

To calculate the difference between two time values, MySQL provides the TIMEDIFF() function. However, in your case, you need to calculate the sum of multiple time intervals. The TIMEDIFF() function is not suitable for this task.

Instead, you can use MySQL's TIME_TO_SEC() and SEC_TO_TIME() functions in conjunction. The TIME_TO_SEC() function converts a time value into seconds, while the SEC_TO_TIME() function does the reverse.

To calculate the sum of time intervals in hours, you can use the following query:

SELECT
  SEC_TO_TIME( SUM( TIME_TO_SEC( time ) ) ) AS total_time
FROM
  time_table;

Here's how this query works:

1. The TIME_TO_SEC() function converts each time value in the time_table table into seconds.
2. The SUM() function adds up all the converted time values in seconds.
3. The SEC_TO_TIME() function converts the total seconds back into a time value in the desired format (hours:minutes:seconds).

This query will provide you with the total time in hours for the specified time intervals.

The above is the detailed content of How to Calculate the Sum of Time Intervals in MySQL?. For more information, please follow other related articles on the PHP Chinese website!