Sum Time in Sql

Question

Sum Time in Sql

AT-2016

Mr or Mrs. 500

Points: 570
More actions
September 4, 2016 at 5:10 am

#317059

I am trying to sum up time and almost did it. Now I would like to sum up top 2 time as follows:
SELECT TOP 2*,
ISNULL((RIGHT('00' + CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, FromTime, ToTime)) / 60), 2)
+ ':' +
RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, FromTime, ToTime)) % 60), 2)
+ ':' +
RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(SECOND, FromTime, ToTime)) % 60), 2)), 0)
AS TotalTime FROM Demo GROUP BY ID, FromTime, ToTime
The output is as follows:
So is there any way or still possible to add the sum of the TotalTime column at the end? I would appreciate if any idea is shared. Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

Quick thought, the time calculation is not correct, the second result sould be 02:30:21 so there is an extra minute there in the results.

😎

Here are three different methods

USE TEEST;

GO

SET NOCOUNT ON;

;WITH DEMO(ID,FromTime,ToTime) AS

(

SELECT

ID

,CONVERT(DATETIME,FromTime,120) AS FromTime

,CONVERT(DATETIME,ToTime,120) AS ToTime

FROM (VALUES (1,'2016-09-03 18:14:47.000','2016-09-03 20:32:54.000')

,(2,'2016-09-03 12:35:45.000','2016-09-03 15:06:06.000')

) AS X(ID,FromTime,ToTime)

)

SELECT

D.ID

,D.FromTime

,D.ToTime

,CONVERT(TIME(0),D.ToTime - D.FromTime,0) AS TIME_DIFF_01

,DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF_02

,CONVERT(TIME(0),DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(DATETIME,0,0)),0) AS TIME_DIFF_03

,CONVERT(TIME(0),CONVERT(DATETIME,SUM(CONVERT(FLOAT,D.ToTime,0) - CONVERT(FLOAT,D.FromTime,0))OVER

(

PARTITION BY (SELECT NULL)

),0),0) AS TIME_SUM_01

,DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM_02

,CONVERT(TIME(0),DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

),CONVERT(DATETIME,0,0)),0) AS TIME_SUM_03

FROM DEMO D;

Output

ID FromTime ToTime TIME_DIFF_01 TIME_DIFF_02 TIME_DIFF_03 TIME_SUM_01 TIME_SUM_02 TIME_SUM_03

----------- ----------------------- ----------------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------

1 2016-09-03 18:14:47.000 2016-09-03 20:32:54.000 02:18:07 02:18:07 02:18:07 04:48:28 04:48:28 04:48:28

2 2016-09-03 12:35:45.000 2016-09-03 15:06:06.000 02:30:21 02:30:21 02:30:21 04:48:28 04:48:28 04:48:28

AT-2016 Mr or Mrs. 500 Points: 570 More actions · Answer 2

AT-2016

Mr or Mrs. 500

Points: 570

September 4, 2016 at 7:04 am

#1899116

Thanks a lot. Works perfectly 🙂 🙂

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

do you have time differences of greater than 24 hours?

eg

;WITH DEMO(ID,FromTime,ToTime) AS

(

SELECT

ID

,CONVERT(DATETIME,FromTime,120) AS FromTime

,CONVERT(DATETIME,ToTime,120) AS ToTime

FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

) AS X(ID,FromTime,ToTime)

)

SELECT

D.ID

,D.FromTime

,D.ToTime

,CONVERT(TIME(0),D.ToTime - D.FromTime,0) AS TIME_DIFF_01

,DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF_02

,CONVERT(TIME(0),DATEADD(SECOND,DATEDIFF(SECOND,D.FromTime,D.ToTime),CONVERT(DATETIME,0,0)),0) AS TIME_DIFF_03

,CONVERT(TIME(0),CONVERT(DATETIME,SUM(CONVERT(FLOAT,D.ToTime,0) - CONVERT(FLOAT,D.FromTime,0))OVER

(

PARTITION BY (SELECT NULL)

),0),0) AS TIME_SUM_01

,DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM_02

,CONVERT(TIME(0),DATEADD(SECOND,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

),CONVERT(DATETIME,0,0)),0) AS TIME_SUM_03

FROM DEMO D;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

J Livingston SQL (9/4/2016)
do you have time differences of greater than 24 hours?

Good point:pinch:, obviously the TIME data type isn't fit for purpose if either a single interval or the sum of intervals exceeds 24 hours.

😎

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 5

This code will return the interval in the format of DD:HH:MM:SS

😎

USE TEEST;

GO

SET NOCOUNT ON;

;WITH DEMO(ID,FromTime,ToTime) AS

(

SELECT

ID

,CONVERT(DATETIME,FromTime,120) AS FromTime

,CONVERT(DATETIME,ToTime,120) AS ToTime

FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

) AS X(ID,FromTime,ToTime)

)

,BASE_DATA AS

(

SELECT

D.ID

,D.FromTime

,D.ToTime

,DATEDIFF(SECOND,D.FromTime,D.ToTime) AS TIME_DIFF_SECOND

,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

) AS TIME_DIFF_SUM

FROM DEMO D

)

SELECT

BD.ID

,BD.FromTime

,BD.ToTime

,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SECOND,0)),0) + CHAR(58)

+ CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SECOND,0),0),120) AS DDHHMMSS

,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SUM,0)),0) + CHAR(58)

+ CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SUM,0),0),120) AS SUM_DDHHMMSS

FROM BASE_DATA BD

;

Output

ID FromTime ToTime DDHHMMSS SUM_DDHHMMSS

----------- ----------------------- ----------------------- --------------------- ---------------------

1 2016-09-03 23:59:59.000 2016-09-04 00:00:01.000 0:00:00:02 6:02:30:23

2 2016-09-01 12:35:45.000 2016-09-04 15:06:06.000 3:02:30:21 6:02:30:23

3 2016-09-01 12:35:45.000 2016-09-02 12:35:44.000 0:23:59:59 6:02:30:23

4 2016-09-01 12:35:45.000 2016-09-02 12:35:45.000 1:00:00:00 6:02:30:23

5 2016-09-01 12:35:45.000 2016-09-02 12:35:46.000 1:00:00:01 6:02:30:23

AT-2016 Mr or Mrs. 500 Points: 570 More actions · Answer 6

Thanks all for the answers. Just wondering why the below query didn't work using TOP 2 directly:

SELECT TOP 2

k.ID,

k.FromTime,

k.ToTime,

DATEADD(SECOND,DATEDIFF(SECOND, k.FromTime, k.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF,

DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM

FROM Demo k;

But in the below case, it almost creates a new table say 'Hello' to select the TOP 2 data or records:

;WITH Hello AS

(

SELECT TOP 1

ID, FromTime, ToTime

FROM Demo

)

SELECT TOP 2

k.ID,

k.FromTime,

k.ToTime,

DATEADD(SECOND,DATEDIFF(SECOND, k.FromTime, k.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF,

DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM

FROM Hello k;

Is there any reason behind it? Just want to know. Thanks.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 7

Simply add the grouping as the first CTE

😎

USE TEEST;

GO

SET NOCOUNT ON;

;WITH DEMO(ID,FromTime,ToTime) AS

(

SELECT

ID

,CONVERT(DATETIME,FromTime,120) AS FromTime

,CONVERT(DATETIME,ToTime,120) AS ToTime

FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')

,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')

,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')

,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')

,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')

) AS X(ID,FromTime,ToTime)

)

,FIRST_GROUP AS

(

SELECT

TOP(2)

DD.ID

,DD.FromTime

,DD.ToTime

FROM DEMO DD

ORDER BY DATEDIFF(SECOND,DD.FromTime,DD.ToTime) DESC

)

,BASE_DATA AS

(

SELECT

FG.ID

,FG.FromTime

,FG.ToTime

,DATEDIFF(SECOND,FG.FromTime,FG.ToTime) AS TIME_DIFF_SECOND

,SUM(DATEDIFF(SECOND,FG.FromTime,FG.ToTime)) OVER

(

PARTITION BY (SELECT NULL)

) AS TIME_DIFF_SUM

FROM FIRST_GROUP FG

)

SELECT

BD.ID

,BD.FromTime

,BD.ToTime

,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SECOND,0)),0) + CHAR(58)

+ CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SECOND,0),0),120) AS DDHHMMSS

,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SUM,0)),0) + CHAR(58)

+ CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SUM,0),0),120) AS SUM_DDHHMMSS

FROM BASE_DATA BD

;

Output

ID FromTime ToTime DDHHMMSS SUM_DDHHMMSS

----------- ----------------------- ----------------------- --------------------- ---------------------

2 2016-09-01 12:35:45.000 2016-09-04 15:06:06.000 3:02:30:21 4:02:30:22

5 2016-09-01 12:35:45.000 2016-09-02 12:35:46.000 1:00:00:01 4:02:30:22

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Eirikur Eiriksson (9/4/2016)
This code will return the interval in the format of DD:HH:MM:SS
😎
USE TEEST;
GO
SET NOCOUNT ON;
;WITH DEMO(ID,FromTime,ToTime) AS
(
SELECT
ID
,CONVERT(DATETIME,FromTime,120) AS FromTime
,CONVERT(DATETIME,ToTime,120) AS ToTime
FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.000')
,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.000')
,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.000')
,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.000')
,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.000')
) AS X(ID,FromTime,ToTime)
)
,BASE_DATA AS
(
SELECT
D.ID
,D.FromTime
,D.ToTime
,DATEDIFF(SECOND,D.FromTime,D.ToTime) AS TIME_DIFF_SECOND
,SUM(DATEDIFF(SECOND,D.FromTime,D.ToTime)) OVER
(
PARTITION BY (SELECT NULL)
) AS TIME_DIFF_SUM
FROM DEMO D
)
SELECT
BD.ID
,BD.FromTime
,BD.ToTime
,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SECOND,0)),0) + CHAR(58)
+ CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SECOND,0),0),120) AS DDHHMMSS
,CONVERT(VARCHAR(12),DATEDIFF(DAY,0,DATEADD(SECOND,BD.TIME_DIFF_SUM,0)),0) + CHAR(58)
+ CONVERT(CHAR(8),CONVERT(TIME(0),DATEADD(SECOND,BD.TIME_DIFF_SUM,0),0),120) AS SUM_DDHHMMSS
FROM BASE_DATA BD
;
Output
ID FromTime ToTime DDHHMMSS SUM_DDHHMMSS
----------- ----------------------- ----------------------- --------------------- ---------------------
1 2016-09-03 23:59:59.000 2016-09-04 00:00:01.000 0:00:00:02 6:02:30:23
2 2016-09-01 12:35:45.000 2016-09-04 15:06:06.000 3:02:30:21 6:02:30:23
3 2016-09-01 12:35:45.000 2016-09-02 12:35:44.000 0:23:59:59 6:02:30:23
4 2016-09-01 12:35:45.000 2016-09-02 12:35:45.000 1:00:00:00 6:02:30:23
5 2016-09-01 12:35:45.000 2016-09-02 12:35:46.000 1:00:00:01 6:02:30:23

thanks Eirikur .. seems sound.. a keeper for me.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sergiy SSC Guru Points: 110208 More actions · Answer 9

There is a much simpler way to find time difference between 2 datetime values:

TimeDifference = TimeTo - TimeFrom

THis will allow to count milliseconds too:

;WITH DEMO(ID,FromTime,ToTime) AS

(

SELECT

ID

,CONVERT(DATETIME,FromTime,120) AS FromTime

,CONVERT(DATETIME,ToTime,120) AS ToTime

FROM (VALUES (1,'2016-09-03 23:59:59.000','2016-09-04 00:00:01.100')

,(2,'2016-09-01 12:35:45.000','2016-09-04 15:06:06.020')

,(3,'2016-09-01 12:35:45.000','2016-09-02 12:35:44.003')

,(4,'2016-09-01 12:35:45.000','2016-09-02 12:35:45.400')

,(5,'2016-09-01 12:35:45.000','2016-09-02 12:35:46.050')

) AS X(ID,FromTime,ToTime)

)

SELECT

DD.ID ,DD.FromTime ,DD.ToTime,

Duration = DD.ToTime - DD.FromTime ,

TotalDuration = CONVERT(DATETIME, TotalDuration) ,

SUM_DDHHMMSS = CONVERT(VARCHAR(10), FLOOR(td.TotalDuration)) + ':'

+ CONVERT(VARCHAR(20), CONVERT(DATETIME, td.TotalDuration), 114)

FROM DEMO DD

CROSS JOIN (

SELECT SUM( CONVERT(FLOAT, ToTime - FromTime)) TotalDuration

FROM DEMO

) td

ORDER BY (ToTime - FromTime) DESC

_____________
Code for TallyGenerator