Storing Durations and Summing Time Past 24 Hours

A day's worth of calls should not roll back to midnight in the total. Summing time works reliably when durations are numeric seconds rather than clock values. Keep the number for calculations and format hours, minutes, and seconds only when you display it.

Separate Elapsed Duration From Clock Time

The time data type represents a time of day within one day. It cannot store an elapsed duration of twenty seven hours. Casting a total through a datetime and displaying its time portion loses the day component. A wrapped clock can look tidy while reporting a wrong total. Store durations using the unit the business actually measures.

I check the original meaning before converting an old column. A value showing ten thirty can mean a clock reading or ten hours and thirty minutes of elapsed work. Those are different facts. Which one does your application store? If the old system already discarded elapsed days, SQL cannot reconstruct them from the remaining time portion alone.

Store Nonnegative Whole Seconds

Use a bigint seconds column for these examples. An integer unit is straightforward to sum, compare, and index. The CHECK constraint rejects negative durations under this contract. If signed adjustments are required, model them explicitly instead of disabling a constraint without changing the reports. Use milliseconds or another fixed unit when the source needs fractional precision.

CREATE TABLE #Duration
(
    ActivityID int NOT NULL PRIMARY KEY,
    GroupName varchar(30) NOT NULL,
    DurationSeconds bigint NOT NULL CHECK(DurationSeconds>=0)
);
INSERT #Duration VALUES
    (1,'Training',90000),(2,'Training',3723),(3,'Support',1800),(4,'Support',1800);
SELECT ActivityID,GroupName,DurationSeconds FROM #Duration;

These values are chosen sample inputs, not measured activity durations. Run the temporary examples in the same connection. Keep the unit in the column name and API contract. An unqualified Duration column invites a later caller to send milliseconds while another caller reads seconds. That mismatch produces remarkably precise nonsense without triggering a type error.

Summing Time With an Aggregate That Fits

SUM over an int returns an int, so a large total can overflow even when each row fits. Cast int values to bigint before aggregating. This sample already uses bigint. Its total still needs to fit the bigint range. If the application can exceed that range, choose a tested decimal aggregate and formatting contract rather than assuming any type is unlimited.

SELECT GroupName,COUNT_BIG(*) AS ActivityCount,
       SUM(DurationSeconds) AS TotalSeconds,
       AVG(CONVERT(decimal(28,3),DurationSeconds)) AS AverageSeconds
FROM #Duration
GROUP BY GroupName;
SELECT SUM(CONVERT(bigint,DurationSeconds)) AS OverallSeconds
FROM #Duration;

I inspect the aggregate type as well as the source type. A valid row does not establish a valid lifetime total. Decide whether NULL means unknown, missing, or zero if your actual column permits it. SUM ignores NULL values. A report that quietly excludes unknown durations can understate workload, so include an unknown count or reject incomplete data upstream.

Format Hours Without Resetting at Midnight

Divide total seconds by thirty six hundred for whole hours. The remainder produces minutes and seconds. Hours remain an unbounded display component within the numeric type's range. Pad only minutes and seconds to two digits. Do not pad hours to two and then cut the string, because larger totals need all their digits. The Training total of 93,723 seconds displays as 26:02:03 instead of wrapping to 02:02:03.

WITH totals AS
(
    SELECT GroupName,SUM(DurationSeconds) AS TotalSeconds
    FROM #Duration GROUP BY GroupName
)
SELECT GroupName,TotalSeconds,
       CONCAT(CONVERT(varchar(30),TotalSeconds/3600),':',
              RIGHT('0'+CONVERT(varchar(2),(TotalSeconds%3600)/60),2),':',
              RIGHT('0'+CONVERT(varchar(2),TotalSeconds%60),2)) AS DurationDisplay
FROM totals;

Keep TotalSeconds in the result even when the screen displays only the formatted value. Sort by the number, not the string. Alphabetical sorting places an hours string according to its first digit, not its elapsed duration. A consumer doing another calculation needs the numeric value and unit. Display text should not become the next system's storage format.

Convert Legacy time Values Deliberately

When a legacy time value genuinely represents a duration shorter than one day, measure seconds since midnight. DATEDIFF_BIG returns a bigint count of second boundaries. For nonnegative time values measured from midnight, it discards fractional seconds under this whole second policy. Do not use a conversion that rounds fractions unless rounding is the approved rule.

CREATE TABLE #LegacyDuration
    (ActivityID int NOT NULL PRIMARY KEY,DurationClock time(7) NOT NULL);
INSERT #LegacyDuration VALUES
    (1,'01:02:03.9000000'),(2,'23:59:59.0000000');
SELECT ActivityID,DurationClock,
       DATEDIFF_BIG(second,CONVERT(time(7),'00:00:00'),DurationClock) AS WholeSeconds,
       DATEDIFF_BIG(millisecond,CONVERT(time(7),'00:00:00'),DurationClock) AS WholeMilliseconds
FROM #LegacyDuration;

If legacy values wrapped after a day, recover the original start and end timestamps or source duration records. A stored time of one hour cannot tell you whether the elapsed value was one, twenty five, or another day plus one. Document unrecoverable cases. Do not manufacture elapsed days to make migration totals resemble expectations.

Calculate Durations From Real Instants

When you have start and end instants, store them as instants and derive elapsed units. The example uses UTC datetime2 values and DATEDIFF_BIG. For fractional accuracy, calculate a sufficiently fine fixed unit and define rounding at presentation. Counting second boundaries differs from rounding a fractional elapsed amount, so match the calculation to your required precision.

DECLARE @started datetime2(7)='2026-09-01T08:00:00.0000000';
DECLARE @finished datetime2(7)='2026-09-02T11:00:00.0000000';
IF @finished<@started THROW 50001,'Finish precedes start.',1;
SELECT DATEDIFF_BIG(second,@started,@finished) AS ElapsedWholeSeconds,
       DATEDIFF_BIG(millisecond,@started,@finished) AS ElapsedWholeMilliseconds;

Local wall clock readings across daylight saving transitions need timezone interpretation before calculating elapsed work. datetimeoffset or a defined UTC conversion helps preserve instants. A video duration is different again: it is already elapsed media time and does not need a timezone. Keep these meanings distinct instead of sending every value through date arithmetic merely because it contains hours.

Test Summing Time at the Boundaries

Test zero, just below a minute, exactly an hour, exactly a day, and more than a day. The next query verifies that the components reconstruct the original total. Add cases for your maximum accepted duration and aggregate size. Ensure the input path rejects or handles fractional and negative values according to the same policy as storage.

WITH samples AS
(
    SELECT CONVERT(bigint,v) AS TotalSeconds
    FROM (VALUES(0),(59),(60),(3599),(3600),(86400),(90061)) AS x(v)
)
SELECT TotalSeconds,TotalSeconds/3600 AS FullHours,
       (TotalSeconds%3600)/60 AS Minutes,TotalSeconds%60 AS Seconds,
       (TotalSeconds/3600)*3600+((TotalSeconds%3600)/60)*60+TotalSeconds%60
           AS ReconstructedSeconds
FROM samples;

For a migration, compare per activity and grouped totals before changing readers. Keep original values until the reconciliation is accepted. Check exported spreadsheets and dashboard fields too. A downstream time cell can wrap even after SQL returns the right seconds. The database fix needs an output contract that continues to preserve elapsed totals through the reporting path.

Keep Summing Time Apart From Presentation

Store the numeric unit, aggregate that unit, and return both the total and its display text. Use application formatting when localization or accessibility requires it. For averages, decide whether fractional seconds remain useful. For billing, state the separate rounding rule and apply it at the agreed level, because rounding each call differs from rounding a group total.

I keep durations numeric until the last presentation step. That makes summing time predictable and keeps validation simple. Clock types remain useful for clock readings, while elapsed work deserves an elapsed unit. When a total passes twenty four hours, the report should show more hours rather than politely pretending a new day erased the earlier work.

Related reading on this blog: Convert Seconds to Hour : Minute : Seconds Format and Learning DATEDIFF_BIG Function in SQL Server 2016.

A duration is not a clock reading, it is an amount of elapsed time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.