Transact-SQL to sum up elapsed time

Question

I have a table in my database the records start and stop times for a specific task. Here is a sample of the data:

Start                       Stop
9/15/2008 5:59:46 PM        9/15/2008 6:26:28 PM
9/15/2008 6:30:45 PM        9/15/2008 6:40:49 PM
9/16/2008 8:30:45 PM        9/15/2008 9:20:29 PM
9/16/2008 12:30:45 PM       12/31/9999 12:00:00 AM

I would like to write a script that totals up the elapsed minutes for these time frames, and wherever there is a 12/31/9999 date, I want it to use the current date and time, as this is still in progress.

How would I do this using Transact-SQL?

Answer 1

The datediff function can display the elapsed minutes. The if statement for the 12/31/9999 check I'll leave as an excercise for the reader ;-)

Answer 2

--you can play with the datediff using mi for minutes

-- this give you second of each task select Start, Stop, CASE WHEN Stop = '9999-12-31' THEN datediff(ss, start,getdate()) ELSE datediff(ss, start,stop) END duration_in_seconds

from mytable

-- sum Select Sum(duration_in_seconds) from ( select Start, Stop, CASE WHEN Stop = '9999-12-31' THEN datediff(ss, start,getdate()) ELSE datediff(ss, start,stop) END duration_in_seconds

from mytable

)x

Answer 3

Datediff becomes more difficult to use as you have more dateparts in your difference (i.e. in your case, looks like minutes and seconds; occasionally hours). Fortunately, in most variations of TSQL, you can simply perform math on the dates. Assuming this is a date field, you can probably just query:

select duration = stop - start

For a practical example, let's select the difference between two datetimes without bothering with a table:

select convert(datetime,'2008-09-17 04:56:45.030') - convert(datetime,'2008-09-17 04:53:05.920')

which returns "1900-01-01 00:03:39.110", indicating there are zero years/months/days; 3 mins, 39.11 seconds between these two datetimes. From there, your code can TimeSpan.Parse this value.

Answer 4

Try:

Select Sum( DateDiff( Minute, IsNull((Select Start where Start!='9999.12.31'),GetDate()), IsNull((Select End where End!='9999.12.31'),GetDate()) ) ) from tableName

Answer 5

The following will work for SQL Server, other databases use different functions for date calculation and getting the current time.

Select  Case When (Stop <> '31 Dec 9999') Then 
          DateDiff(mi, Start, Stop) 
        Else 
          DateDiff(mi, Start, GetDate()) 
        End
From    ATable

Answer 6

SELECT  SUM( CASE  WHEN Stop = '31 dec 9999' 
                   THEN DateDiff(mi, Start, Stop)
                   ELSE DateDiff(mi, Start, GetDate())
             END ) AS TotalMinutes 
FROM    task

However, a better solution would be to make the Stop field nullable, and make it null when the task is still running. That way, you could do this:

SELECT  SUM( DateDiff( mi, Start, IsNull(Stop, GetDate() ) ) AS TotalMinutes 
FROM    task

Answer 7

Using help from AJ's answer, BelowNinety's answer and Nerdfest's answer, I came up with the following:

Select Sum(
    Case When End = '12/31/9999 12:00:00 AM' Then
         DateDiff(mi, Start, Getdate()) 
    Else 
         DateDiff(mi, Start, End) 
    End) As ElapsedTime 
From Table

Thanks for the help!

Answer 8

I think this is cleaner:

   SELECT  SUM(
               DATEDIFF(mi, Start, ISNULL(NULLIF(Stop,'99991231'), GetDate())
              ) AS ElapsedTime
   FROM Table