Hey there, let's work through this problem together. When you sum up time values stored in formats like '00:02:12', databases typically convert those values into an integer representing total time units (like total seconds) behind the scenes. Getting that integer back into a readable time format depends a bit on which database system you're using—here are solutions for the most common ones:

If your sum result is total seconds (which is the usual case when summing TIME values), you can use the built-in SEC_TO_TIME() function to convert it back. First, convert each time entry to seconds with TIME_TO_SEC(), sum them, then convert the total back to a time format:

SELECT SEC_TO_TIME(total_seconds) AS formatted_time
FROM (
    SELECT SUM(TIME_TO_SEC(your_time_column)) AS total_seconds
    FROM your_table
) AS time_sum;

This will return a string like '25:10:30' for totals exceeding 24 hours, which MySQL handles natively.

PostgreSQL handles time sums more natively with interval types. If your column is already a TIME type, summing it directly will return an INTERVAL which is human-readable:

SELECT SUM(your_time_column) AS formatted_time
FROM your_table;

If your time is stored as a string (e.g., VARCHAR), first cast it to TIME before summing:

SELECT SUM(CAST(your_time_column AS TIME)) AS formatted_time
FROM your_table;

If you have a total of seconds as an integer, convert it back using MAKE_INTERVAL():

SELECT MAKE_INTERVAL(seconds => total_seconds) AS formatted_time
FROM (
    SELECT SUM(EXTRACT(EPOCH FROM CAST(your_time_column AS TIME))) AS total_seconds
    FROM your_table
) AS time_sum;

SQL Server requires a bit more care, especially if your total time exceeds 24 hours (since DATEADD() will wrap around to the next day). Here are two approaches:

For totals under 24 hours #

Use DATEADD() to convert total seconds back to a time string:

SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, total_seconds, 0), 108) AS formatted_time
FROM (
    SELECT SUM(DATEDIFF(SECOND, 0, CAST(your_time_column AS TIME))) AS total_seconds
    FROM your_table
) AS time_sum;

For totals exceeding 24 hours #

Manually calculate hours, minutes, and seconds to avoid wrapping:

SELECT 
    CONCAT(
        FLOOR(total_seconds / 3600), ':',
        RIGHT('0' + CAST((total_seconds % 3600) / 60 AS VARCHAR), 2), ':',
        RIGHT('0' + CAST(total_seconds % 60 AS VARCHAR), 2)
    ) AS formatted_time
FROM (
    SELECT SUM(DATEDIFF(SECOND, 0, CAST(your_time_column AS TIME))) AS total_seconds
    FROM your_table
) AS time_sum;

Quick Notes #

• Always prefer storing time values as native TIME types instead of strings—this avoids parsing errors and makes database operations more efficient.
• If your sum is in minutes or hours instead of seconds, adjust the conversion functions accordingly (e.g., multiply minutes by 60 to get seconds before using SEC_TO_TIME() in MySQL).

内容的提问来源于stack exchange，提问作者Mustafa Nure Alam