Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I get the sum of multiple datetime values?

I have a query such that the query's result is:

SELECT CONVERT(VARCHAR(8),(MAX(END_TIME)-MIN(START_TIME)),108) as DURATION WHERE ... GROUP BY TITLE

For each title, DURATION is different. The query returns:

DURATION
00:16:14
00:00:00
00:01:30
00:16:25
00:09:34
00:00:01
01:04:04
00:00:28
00:00:12
00:06:11
00:26:38
00:31:44
00:02:16
00:03:22
00:09:39
00:03:20
00:03:43
00:09:33
00:08:05
00:06:58
00:25:29
01:55:30
00:03:02
00:00:18
00:06:09
00:07:26
00:25:43
00:00:16
00:26:58
02:09:38
00:57:56
00:00:45
00:00:00
00:07:24
00:00:54
00:00:27
00:01:28
00:07:14
00:00:19
01:43:25
00:58:23
00:02:29
02:19:48
00:09:06
05:12:15
02:27:15
00:56:47
00:02:24

I need the sum of these values; how can I get it?

like image 314
Mehmet Avatar asked Oct 17 '11 12:10

Mehmet


People also ask

How do I get the SUM of multiple column values in SQL?

The SQL AGGREGATE SUM() function returns the SUM of all selected column. Applies to all values. Return the SUM of unique values. Expression made up of a single constant, variable, scalar function, or column name.

Can we do SUM of date in SQL?

You cannot sum times. What you should do instead is use the DateDiff function with your start and end time using the seconds parameter. This will return an integer data type which you can SUM on. When you're done, convert it to a DateTime to format it like hours:minutes:seconds.


2 Answers

Convert minutes into seconds

SUM() the seconds

Convert back to minutes


The following will give you the SUM of seconds:

SET @Seconds = SELECT SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME]))

The following then turns that into a datetime object:

select convert(varchar(8), dateadd(second, @Seconds, 0),  108)

Or as 1 query:

SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME])), 0),  108)
like image 121
Curtis Avatar answered Sep 21 '22 18:09

Curtis


You cannot sum times. What you should do instead is use the DateDiff function with your start and end time using the seconds parameter. This will return an integer data type which you can SUM on. When you're done, convert it to a DateTime to format it like hours:minutes:seconds.

like image 37
George Mastros Avatar answered Sep 20 '22 18:09

George Mastros