SQL: Add multiple time(7) columns to get total duration-CodePudding

I have a table with five time(7) columns as below:

[Duration1] [time](7)
[Duration2] [time](7)
[Duration3] [time](7)
[Duration4] [time](7)
[TotalDuration] [time](7)

╔══════════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╗
║ Duration1        ║ Duration2        ║ Duration3        ║ Duration4        ║ TotalDuration    ║
╠══════════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╣
║ 00:07:10.0000000 ║ 00:15:47.0000000 ║ 00:00:14.0000000 ║ 00:13:31.0000000 ║ 00:00:00.0000000 ║
╚══════════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╝

How do I add Duration1, Duration2, Duration3 and Duration4 to get the total duration and update the TotalDuration column?

CodePudding user response：

Would this work for you?

   --seconds
   DATEADD(ss,datepart("ss",Duration1)   datepart("ss",Duration2)    datepart("ss",Duration3)    datepart("ss",Duration4) ,
   --minutes
   DATEADD(mi, datepart("mi",Duration1)   datepart("mi",Duration2)   datepart("mi",Duration3)   datepart("mi",Duration4), 
   --hours
   DATEADD(hh, datepart("hh",Duration1)   datepart("hh",Duration2)   datepart("hh",Duration3)   datepart("hh",Duration4)
   --add to 00:00:00.0000000 as starting point
   , convert(time,'00:00:00.0000000'))))

CodePudding user response：

You want to store durations. The appropriate data type for this would be an INTERVAL. SQL Server, however, does not have such a data type. So, you must find another way to store the durations. You could store a number of seconds for instance. You have decided to use the TIME interval instead. You cannot add up times, for what would 10 pm plus 9 pm even mean?

So, first convert your times into durations. Half past ten becomes ten hours and thirty minutes, or 37800 seconds. Then add up all those durations (seconds). In the end you can just show that duration, e.g. duration = 50000 seconds, or convert that back to a time (13:53:20).

select
  datediff(second, '00:00:00', duration1)  
  datediff(second, '00:00:00', duration2)  
  datediff(second, '00:00:00', duration3)  
  datediff(second, '00:00:00', duration4) as seconds
from mytable;

select
  cast(
    dateadd(second, 
            datediff(second, '00:00:00', duration1)  
            datediff(second, '00:00:00', duration2)  
            datediff(second, '00:00:00', duration3)  
            datediff(second, '00:00:00', duration4),
            '00:00:00')
    as time) as total_duration
from mytable;

Understand that SQL Server is really not the perfect tool to work with durations, and TIME may be a bad choice - not only because of the conversions needed, but for the fact that adding up hours can exceed 24 hours, which would be more than what you can show in a TIME.

In a DBMS that supports intervals like PostgreSQL, you'd store the durations in their appropriate data type INTERVAL and your query would look like this:

select duration1   duration2   duration3   duration4 as total_duration
from mytable;