CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Jon Erickson
Jon Erickson

Reputation: 114826

Get the SUM of TIME datatypes (MSSQL08) from a table

I have the following table of TIME datatypes

clarification: I am representing hours/mins/seconds of videos and want to figure out how long the videos will be.

runtime
----------------
01:27:19.0000000
01:26:09.0000000
01:00:56.0000000
01:09:59.0000000
01:25:43.0000000
01:16:01.0000000
01:27:12.0000000
01:22:00.0000000
01:17:47.0000000
01:31:07.0000000
01:36:23.0000000
01:13:29.0000000
01:20:12.0000000
01:03:24.0000000
00:58:42.0000000

I want to get the SUM of all of these times.

Upvotes: 7

Views: 3823

Answers (3)

John M Gant
John M Gant

Reputation: 19308

The best way I can think of is to parse the time spans into hour, minute, second and millisecond components, convert it all into milliseconds (by adding and multiplying), then adding them and using modulos and subtraction to turn it back into a timespan.

For example

create function dbo.GetMilliseconds (@timespan varchar (16))
    returns int
as
begin
declare @ms int
declare @seconds int
declare @min int
declare @hour int
set @ms = cast(substring(@timespan, 10, 7) as int)
set @seconds = cast(substring(@timespan, 7, 2) as int)
set @min = cast(substring(@timespan, 4,2) as int)
set @hour = cast(substring(@timespan, 1, 2) as int)
return @ms + (@seconds * 1000) + (@min * 60000) + (@hour * 3600000)
end
go

create function dbo.ParseMilliseconds (@timespan int)
    returns varchar (16)
as
begin
declare @hour int
declare @min int
declare @seconds int
declare @ms int
declare @leftover int
set @hour = @timespan / 3600000 /* this will round down */  
set @leftover = @timespan % 3600000
set @min = @leftover / 60000
set @leftover = @leftover % 60000
set @seconds = @leftover / 1000
set @ms = @leftover % 1000
return 'You''ll have to the string conversion yourself'
end
go

And then SELECT dbo.ParseMilliseconds(sum(dbo.GetMilliseconds(timespan))) as added_timespan

I haven't tested all of this, and there may be simpler ways, but I know this will work.

Upvotes: 0

Tion
Tion

Reputation: 1480

some variation of

SELECT SUM(DATEDIFF(MINUTE, '0:00:00', TimeCol)) FROM TimeTable

will work but

you will have to normalize by subtracting the date part from each datetime and you will have to convert the total minutes to a time

Upvotes: 0

Tom H
Tom H

Reputation: 47444

I haven't used 2008 much yet and I don't have an instance handy where I am to check this, but something like this might work:

SELECT
     DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', my_time)), '00:00:00.000')
FROM
     dbo.My_Table

As le dorfier points out, adding times is a bit meaningless, because they are supposed to represent a time of day. I'm going under the assumption that you are using them to indicate hours/minutes/seconds.

Oh, and I have no idea what will happen with the above if it goes over 24 hours.

Upvotes: 6

Related Questions