How to get sum of hours and minutes in sql
CREATE TABLE #Attendance(OutTime DateTime, InTime DateTime)
-- Sample Data
insert into #Attendance( InTime, outTime)
values ('2015-12-12 07:30', '2015-12-12 17:30'),
('2015-12-12 07:30', '2015-12-12 17:30'),
('2015-12-13 07:30', '2015-12-13 16:45'),
('2015-12-14 07:30', '2015-12-14 14:34'),
('2015-12-15 07:30', '2015-12-15 18:21')
-- Use DateDiff function to get total minutes between times to achieve greater accuracy then with 'hour'
-- Convert total minutes to hours after adding up total minutes
SELECT SUM(DATEDIFF(MINUTE, InTime , OutTime)) /60
FROM #Attendance
DROP TABLE #Attendance
Now with more LINQ:
public class Attendance
{
public DateTime InTime { get; set; }
public DateTime OutTime { get; set; }
}
[TestClass]
public class AttendanceUser
{
[TestMethod]
public void UseALambda()
{
var rand = new Random();
var attendances = Enumerable.Range(0, 10).Select(x =>
new Attendance { InTime = DateTime.Now.AddHours(-rand.Next(x)), OutTime = DateTime.Now.AddHours(rand.Next(x)) }).ToList();
var total = attendances.Sum(x => (x.OutTime - x.InTime).TotalMinutes) / 60;
}
}
SQL - Calculate Hours sum using rollup
I did have similar requirement and solved it like this:
select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
(
select type= ISNULL(type, 'Total'),
time= SUM(DATEDIFF(MINUTE, '0:00:00', time))
from time_test
GROUP BY ROLLUP(type)
) x
I believe this is more understandable and easier to trace
type time
A 1:25
B 2:30
C 5:56
D 0:50
Total 10:41
Edit: Updated the query for hour can be more then 24 hours
select type, RTRIM(time/60) + ':' + RIGHT('0' + RTRIM(time%60),2) from
(
select type= ISNULL(type, 'Total'),
time= SUM(DATEDIFF(MINUTE, '0:00:00',
DATEADD(day, SUBSTRING(time,0,CHARINDEX(':',time,0)) / 24,
DATEADD(hour, SUBSTRING(time,0,CHARINDEX(':',time,0)) % 24,
DATEADD(minute,SUBSTRING(time,CHARINDEX(':',time)+1,LEN(time)) +0, 0)) )))
from time_test
GROUP BY ROLLUP(type)
) x
Example Result:
A 1:25
B 2:30
C 5:56
D 70:50
Total 80:41
How to get the sum of hour, minutes, and seconds in 2 columns?
As mentioned in comments - if you're storing time values, you should really be using the TIME
datatype - not a VARCHAR
..... always use the most appropriate datatype - no exceptions.
Anyway - since you have VARCHAR
, the statement get a bit convoluted - but here you go:
SELECT
DATEADD(SECOND, DATEDIFF(SECOND, 0, CAST(NewTime AS TIME)), CAST(OldTime AS TIME))
FROM
dbo.YourTableNameHere
which would look a lot easier with TIME
columns, of course:
SELECT
DATEADD(SECOND, DATEDIFF(SECOND, 0, NewTime), OldTime)
FROM
dbo.YourTableNameHere
Related Topics
Detect If Value Is Number in MySQL
How to Convert Yyyy-Mm-Dd Hh:Mm:Ss to Mm-Dd-Yyyy Hh:Mm:Ss in SQL Server
How to in a Query Calculate the Sum of the Union
The MySQL Service on Local Computer Started and Then Stopped
Two Rows With the Same Id and Two Different Values, Getting the Second Value into Another Column
Insert Distinct Values from One Table into Another Table
Sql Query for Values Consisting of Only a Specific Character
How to Calculate Difference of Values in a Successive Rows
How to See Query History in SQL Server Management Studio
Splitting Date into 2 Columns (Date + Time) in SQL
Select Only Rows That Contain Only Alphanumeric Characters in MySQL
Subtract Values from Two Columns in SQL Query
Select Different Values from Same Column in a Table and Display It Under Different Columns
Laravel Update Multiple Records At Once