如何获得多个日期时间值的总和？ [英] How can I get the sum of multiple datetime values?

问题描述

我有一个查询，以便查询的结果是：

  SELECT CONVERT（VARCHAR（8），（MAX（END_TIME ）-MIN（START_TIME）），108）作为DURATION WHERE ... GROUP BY TITLE 
  

标题 DURATION 不同。查询返回：

  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 
  

我需要这些值的总和;如何获取？

解决方案

将分钟转换成秒

SUM（）秒

转回分钟

< hr>

以下内容将为您提供秒数：

  SET @Seconds = SELECT SUM（DATEDIFF（SECOND，[START_TIME]，[END_TIME]））
  

那个进入一个 datetime 对象：

  select convert（varchar（8） ，dateadd（second，@Seconds，0），108）
  

---

或作为1查询：

  SELECT convert（varchar（8），dateadd（second，SUM（DATEDIFF（SECOND， [START_TIME]，[END_TIME]）），0），108）
  

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?

解决方案

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)

这篇关于如何获得多个日期时间值的总和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！