use SUM function on TIME data type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

use SUM function on TIME data type

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-08-01 : 08:52:10

Hi,
this is my sample table:

declare @t table ([time] time)
insert @t values
('01:20'),
('02:50'),
('23:59')

I want to see the result like this:
result
------
28:9

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-01 : 08:56:08

[code]SELECT total/60,total%60, cast(total/60 as varchar(32))+':'+cast(total%60 as varchar(32))
FROM (SELECT SUM(DATEDIFF(mi,0,[time])) AS total FROM @t) s[/code]Y

sigmas
Posting Yak Master

172 Posts

Posted - 2013-08-01 : 09:12:28

Thank you, James K.
I like your code, it's very short.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-01 : 10:45:24

You are very welcome, sigmas. Glad to help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-01 : 15:16:52

[code]declare @t table ([time] time)
insert @t values
('01:20'),
('02:50'),
('23:59');

WITH cteSource(theMinutes)
AS (
SELECT SUM(60 * DATEPART(HOUR, [Time]) + DATEPART(MINUTE, [Time])) AS theMinutes
FROM @t
)
-- Wanted from poster
SELECT CAST(theMinutes / 60 AS VARCHAR(12)) + ':' + CAST(theMinutes % 60 AS VARCHAR(2))
FROM cteSource

UNION ALL

-- More visually appealing
SELECT CAST(theMinutes / 60 AS VARCHAR(12)) + ':' + RIGHT('0' + CAST(theMinutes % 60 AS VARCHAR(2)), 2)
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-08-03 : 08:20:55

thanks SwePeso.

Now adding the seconds...
How can I do that?

declare @t table ([time] time)
insert @t values
('01:09:50'),
('02:50:10'),
('03:00:00');

--The Result:
-- 7:00:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 10:55:55

[code]declare @t table ([time] time)
insert @t values
('01:20:11'),
('02:50:51'),
('23:59:02');

WITH cteSource(theSeconds)
AS (
SELECT SUM(3600 * DATEPART(HOUR, [Time]) + 60 * DATEPART(MINUTE, [Time]) + DATEPART(SECOND, [Time])) AS theSeconds
FROM @t
)
-- Wanted from poster
SELECT CAST(theSeconds / 3600 AS VARCHAR(12)) + ':' + CAST((theSeconds % 3600) / 60 AS VARCHAR(2)) + ':' + CAST(theSeconds % 60 AS VARCHAR(2))
FROM cteSource

UNION ALL

-- More visually appealing
SELECT CAST(theSeconds / 3600 AS VARCHAR(12)) + ':' + RIGHT('0' + CAST((theSeconds % 3600) / 60 AS VARCHAR(2)), 2) + ':' + RIGHT('0' + CAST(theSeconds % 60 AS VARCHAR(2)), 2)
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sigmas
Posting Yak Master

172 Posts

Posted - 2013-08-03 : 14:48:35

that's very good.
now adding the millisecond...
how can I do that?

look at the result carefully:
declare @t table ([time] time(1))
insert @t values
('01:20:11.7'),
('02:50:51.0'),
('23:59:02.3');

--result: 28:1:5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 16:42:02

How hard is it to continue on the line I've shown you?
Or at least provide all details from the beginning?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 16:57:09

[code]DECLARE @Sample TABLE
(
Data TIME(1)
);

INSERT @Sample
(
Data
)
VALUES ('01:20:11.7'),
('02:50:51.0'),
('23:59:02.3');

WITH cteSource(theData)
AS (
SELECT DATEADD(MILLISECOND, theData, '19000101')
FROM (
SELECT SUM(3600000 * DATEPART(HOUR, Data) + 60000 * DATEPART(MINUTE, Data) + 1000 * DATEPART(SECOND, Data) + DATEPART(MILLISECOND, Data)) AS theData
FROM @Sample
) AS D
)
-- Wanted from poster
SELECT CAST(24 * DATEDIFF(DAY, '19000101', theData) + DATEPART(HOUR, theData) AS VARCHAR(12)) + ':' + CAST(DATEPART(MINUTE, theData) AS VARCHAR(2)) + ':' + CAST(DATEPART(SECOND, theData) AS VARCHAR(2))
FROM cteSource

UNION ALL

-- More visually appealing 1
SELECT CAST(24 * DATEDIFF(DAY, '19000101', theData) + DATEPART(HOUR, theData) AS VARCHAR(12)) + RIGHT(CONVERT(CHAR(8), theData, 8), 6)
FROM cteSource

UNION ALL

-- More visually appealing 2
SELECT CAST(24 * DATEDIFF(DAY, '19000101', theData) + DATEPART(HOUR, theData) AS VARCHAR(12)) + RIGHT(CONVERT(CHAR(24), theData, 121), 11)
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jethrow
Starting Member

37 Posts

Posted - 2013-08-04 : 14:47:27

Here's another option using James K's approach:

declare @t table ([time] time(1))
insert @t values
	('01:20:11.7'),
	('02:50:51.0'),
	('23:59:02.4');

Select cast(total/3600000 as varchar(32))
	+':'+cast(total%3600000/60000 as varchar(32))
	+':'+cast(total%3600000%60000/1000 as varchar(32))
	+'.'+cast(total%3600000%60000%1000 as varchar(32)) TotalTime
From (Select SUM(DATEDIFF(ms,0,[time])) total FROM @t) s;

Question - why do we need the s at the end?

quote:
[James K:] Rules of T-SQL syntax requires it.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-04 : 20:29:18

quote:
Question - why do we need the s at the end?

It is an alias for the virtual table generated by the subquery.

...
From (Select SUM(DATEDIFF(ms,0,[time])) AS total FROM @t) AS s;

Rules of T-SQL syntax requires it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-05 : 03:03:52

quote:
Originally posted by jethrow

Here's another option using James K's approach:

declare @t table ([time] time(1))
insert @t values
('01:20:11.7'),
('02:50:51.0'),
('23:59:02.4');

Select cast(total/3600000 as varchar(32))
+':'+cast(total%3600000/60000 as varchar(32))
+':'+cast(total%3600000%60000/1000 as varchar(32))
+'.'+cast(total%3600000%60000%1000 as varchar(32)) TotalTime
From (Select SUM(DATEDIFF(ms,0,[time])) total FROM @t) s;

Question - why do we need the s at the end?
quote:
[James K:] Rules of T-SQL syntax requires it.

I wish you haven't posted this. Since the second argument for DATEDIFF only accecpts INT values, the largest date and time you can use without errors is "19000125 20:31:23.647". Anything else (with milliseconds) will result in an error.
So it's practically useless. Sorry.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-05 : 06:43:32

quote:
Originally posted by SwePeso

quote:
Originally posted by jethrow

Here's another option using James K's approach:

declare @t table ([time] time(1))
insert @t values
('01:20:11.7'),
('02:50:51.0'),
('23:59:02.4');

Select cast(total/3600000 as varchar(32))
+':'+cast(total%3600000/60000 as varchar(32))
+':'+cast(total%3600000%60000/1000 as varchar(32))
+'.'+cast(total%3600000%60000%1000 as varchar(32)) TotalTime
From (Select SUM(DATEDIFF(ms,0,[time])) total FROM @t) s;

Question - why do we need the s at the end?
quote:
[James K:] Rules of T-SQL syntax requires it.

I wish you haven't posted this. Since the second argument for DATEDIFF only accecpts INT values, the largest date and time you can use without errors is "19000125 20:31:23.647". Anything else (with milliseconds) will result in an error.
So it's practically useless. Sorry.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I am well aware of the limitations of DATEDIFF when it comes to small time increments. However, in this case, you are COMPLETELY WRONG. And here is why:

If you look at the opening post from the OP, he is using TIME data type. I am not inferring that from his sample data. Take a look at his create statement which is this: declare @t table ([time] time)

TIME data type can store only values less than 24 hours. http://technet.microsoft.com/en-us/library/bb677243.aspx So the issue of anything over 24 hours does not arise, let alone whatever is represented by "19000125 20:31:23.647". So the query I posted works and works correctly every time for the milliseconds calculation.

Please do point out that the query will fail for microseconds or nanoseconds. That I understand. But remember, the reply I posted was for calculating only up to the minute portions.

While it is ok to point out errors in a polite tone, or ask questions when you don't quite follow the rationale for something that another person has posted, may I respectfully submit that asserting that something is "practically useless" when such assertion is wrong adds no value to the discussions?

Such ill-advised assertions simply confuse people who are looking for answers. So I wish you would refrain from such assertions.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-05 : 06:46:28

You are absolutely right about the TIME datatype.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jethrow
Starting Member

37 Posts

Posted - 2013-08-05 : 10:08:50

Thank you SwePeso for the criticism, and thank you James K for the detailed reply. You may have noticed my post count as of now is 3 - which is an accurate representation of my T-SQL experience. My goal in answering posts is simply to familiarize myself with T-SQL rapidly, so I appreciate feedback.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources