Calculate a Sum of Type Time Using SQL

How to sum up time field in SQL Server

SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
FROM dbo.table 
-- WHERE ...
GROUP BY EmployeeID;

You can format it pretty on the front end. Or in T-SQL:

;WITH w(e, mw) AS
(
    SELECT EmployeeID, SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
    FROM dbo.table 
    -- WHERE ...
    GROUP BY EmployeeID
)
SELECT EmployeeID = e,
  WrkHrs = RTRIM(mw/60) + ':' + RIGHT('0' + RTRIM(mw%60),2)
  FROM w;

However, you're using the wrong data type. TIME is used to indicate a point in time, not an interval or duration. Wouldn't it make sense to store their work hours in two distinct columns, StartTime and EndTime?

SUM total time in SQL Server

if the time in hh/mm/ss then ::

SELECT studentid,studentname,
         DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', mytime)), '00:00:00.000') as time
    FROM
         worknote

How to Sum (Time(n)) in Sql Server?

Convert the time to an integer value before you sum it (for example, seconds):

SELECT SUM(
        datediff(second, '00:00:00', [TimeCol])
    )
FROM
    ...

Replace [TimeCol] with the name of the Time(n) column. This gives you the total time in seconds, which you can then easily convert to minutes, hours, etc...

Need an SQL query to calculate the sum of user activity time in desired period

You can get the past activity time and calculate the difference. Think the query will work if you track the activity all the time.

query using lag - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9f6fea01dd9fdc3e91d1e446ec027927

Calculate the sum of time datatype in sql

Are you sure Total working time is given as Time datatype?
Your sample input is '32:23:00' which is not supported by 'time' Data type
Because its range is 00:00:00.0000000 through 23:59:59.9999999.

If it is Time datatype, then you can calculate the salary as juergen states:

select cast(cast (100.00 as money) * datediff(second, 0, '23:23:00') / (60.0 * 60.0) as money)

I cast the values into money data type to show that, its possible to multiply it with datediff() functions returned value.

How to get sum of times in mysql?

You can use SEC_TO_TIME and TIME_TO_SEC date time functions

select SEC_TO_TIME(sum(TIME_TO_SEC(hours))) from timesheet ;

sum of time datatype in sql

The time data type has a range of 00:00:00.0000000 through 23:59:59.9999999 so you can't cast to time. You have to calculate the hour, minute and second instead.

Something like this could work for you

select H.Val, M.Val, S.Val
from (
       --Your query goes here
       select dateadd(second, sum(datediff(second, 0, timeAttendanceHour)), 0) as sumtime
       from YourTable
     ) as T
  cross apply (select datedifF(hour, 0, T.sumTime)) as H(Val)
  cross apply (select datediff(minute, 0, dateadd(hour, -H.Val, T.sumTime))) as M(Val)
  cross apply (select datediff(second, 0, dateadd(hour, -H.Val, dateadd(minute, -M.Val, T.sumTime)))) as S(Val)

If you want the result as HH:MM:SS you can use this:

select cast(H.Val as varchar(10))+':'+right(M.Val+100, 2)+':'+right(S.Val+100, 2)

---

Related Topics