How to Sum HH:mm Time Values in MySQL?

Calculating Sum of Time Values in MySQL: HH:mm Format

When working with time data in SQL, it's common to encounter values in the HH:mm format. If you need to calculate the sum of such time values, you may face challenges due to their unique format. This question addresses how to overcome this obstacle in MySQL specifically.

The solution provided utilizes MySQL's SEC_TO_TIME() and TIME_TO_SEC() functions:

• SEC_TO_TIME(duration): Converts a duration in seconds to a time value in the HH:mm:ss format.
• TIME_TO_SEC(time): Converts a time value in the HH:mm:ss format to a duration in seconds.

Using these functions, the query presented is as follows:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`timeSpent`))) AS timeSum 
FROM YourTableName;

This query converts each timeSpent value to its corresponding number of seconds using TIME_TO_SEC(). It then accumulates these seconds using the SUM() function. Finally, SEC_TO_TIME() is used to convert the sum of seconds back into a time value in the HH:mm format.

The result of executing this query will be a single row containing a timeSum column that represents the total sum of the timeSpent values in the table. This value can be used for analytical purposes or further calculations involving time durations.

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