SQLTeam.com | Weblogs | Forums

Sum with time data


#1

Hello,

I need help to sum time data like this:
Name|Time
A|00:00:05
B|00:01:00

That data i import from flat file data (txt).

The result using group by is 00:01:05

If standard query can use group by and show error that varchar cannot used sum.

Can anyone help for summing data like this?


#2

try this

create table #temp
(
ttime time
)

insert into #temp values ('00:01:00'),('00:00:05')

SELECT
DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', ttime)), '00:00:00.000') as time
FROM
#temp


#3

the result is 1900-01-01 00:01:05.000

I mean that result is 00:01:05

If i import from textfile and insert into to temp table the result is:
Conversion failed when converting date and/or time from character string.

Can you help me?


#4

Convert the time into a duration - sum up the total duration and then convert that duration back into a time. The duration could be set to the number of seconds since midnight - then your SUM is simply summing an integer column...converting back would be a simple calculation dividing the seconds by 60 to get minutes, etc...


#5

Can you help me how to convert to duration and convert again to time again?

Thank you


#6

TIME has a max value of 23:59 - I've fallen into the trap of SUM'ing time before (e.g. staff timesheet) and the resulting total being more than 24 hours :frowning:

I now SUM in, say, minutes as an integer and then display as Hours : Minutes using Modulo arithmetic


#7

Not sure how you are importing the data from a text file - but if the data is formatted as 00:00:00 then you can import that data into a column of TIME(0) data type.

Then - you can do something like this:

   With testData
     As (
 Select cast(t.TestName As char(1)) As TestName
      , cast(t.TestTime As time(0)) As TestTime
   From (
 Values ('A', '00:00:05')
      , ('B', '00:01:00')
        ) As t(TestName, TestTime)
        )
 Select sum(datediff(second, '00:00:00', TestTime)) % 60 As 'Seconds'
      , sum(datediff(second, '00:00:00', TestTime)) / 60 As 'Minutes'
      , sum(datediff(second, '00:00:00', TestTime)) / 60 / 60 As 'Hours'
      , timefromparts(sum(datediff(second, '00:00:00', TestTime)) / 60 / 60 
                    , sum(datediff(second, '00:00:00', TestTime)) / 60 
                    , sum(datediff(second, '00:00:00', TestTime)) % 60, 0, 0)  As TotalTime
   From testData;

As Kristen pointed out - if your duration can exceed 24 hours then you won't be able to use the timefromparts function to convert to a time. Not a big deal if all you want is the total duration...