时间问题的处理

[ballatong]

请教，需要按userID汇总最后一列的合计时间，用时分秒表示，（最后一列目前是前两列时间的差）

userID stopstarttime stopendtime (No column name)
1 2019-07-01 14:49:55.130 2019-07-01 14:50:37.903 1900-01-01 00:00:42.773
1 2019-07-01 14:50:42.717 2019-07-01 14:52:07.670 1900-01-01 00:01:24.953
1 2019-07-01 14:52:17.160 2019-07-01 14:52:19.880 1900-01-01 00:00:02.720
1 2019-07-01 14:52:23.523 2019-07-01 15:01:13.510 1900-01-01 00:08:49.987
1 2019-07-01 14:28:41.997 2019-07-01 14:28:48.410 1900-01-01 00:00:06.413
1 2019-07-01 14:29:00.307 2019-07-01 14:49:43.390 1900-01-01 00:20:43.083
2 2019-07-01 14:29:55.470 2019-07-01 14:31:14.657 1900-01-01 00:01:19.187
2 2019-07-01 14:31:59.673 2019-07-01 14:35:00.807 1900-01-01 00:03:01.133
2 2019-07-01 14:35:32.280 2019-07-01 14:41:14.110 1900-01-01 00:05:41.830

[ballatong]

试过了结果不对

[RINK_1]

引用 2 楼 ballatong 的回复:

试过了结果不对

如果超过60秒、60分、24小时，要怎么显示呢

[ballatong]

60秒的向分进位，60分的向小时进位，小时就保留小时 比如 100 小时23分40秒

[insus]

参考：


https://www.cnblogs.com/insus/archive/2011/01/17/1937683.html

[insus]

或者可以参考C#的方法：
https://www.cnblogs.com/insus/p/7997623.html

[RINK_1]

引用 4 楼 ballatong 的回复:

60秒的向分进位，60分的向小时进位，小时就保留小时 比如 100 小时23分40秒

SELECT USERID,CAST(TOTAL/3600 AS VARCHAR)+'小时'+CAST((TOTAL%3600)/60 AS VARCHAR)+'分钟'+CAST((TOTAL%3600)%60 AS VARCHAR)+'秒'
FROM
(SELECT USERID,SUM(DATEDIFF(SECOND,STOPSTARTTIME,STOPENDTIME)) AS TOTAL
FROM TABLE  
GROUP BY USERID) AS A

[RINK_1]

不知道你要的时分秒要怎么显示，试试下面的

SELECT USERID,CAST(SUM(DATEDIFF(HH,STOPSTARTTIME,STOPENDTIME)) AS VARCHAR)+'小时'
+CAST(SUM(DATEDIFF(MINUTE,STOPSTARTTIME,STOPENDTIME)) AS VARCHAR)+'分钟'
+CAST(SUM(DATEDIFF(SECOND,STOPSTARTTIME,STOPENDTIME)) AS VARCHAR)+'秒'
FROM TABLE  
GROUP BY USERID

[唐诗三百首]

create table #t(userID int,stopstarttime datetime,stopendtime datetime,)

insert into #t
 select 1,'2019-07-01 14:49:55.130','2019-07-01 14:50:37.903' union all
 select 1,'2019-07-01 14:50:42.717','2019-07-01 14:52:07.670' union all
 select 1,'2019-07-01 14:52:17.160','2019-07-01 14:52:19.880' union all
 select 1,'2019-07-01 14:52:23.523','2019-07-01 15:01:13.510' union all
 select 1,'2019-07-01 14:28:41.997','2019-07-01 14:28:48.410' union all
 select 1,'2019-07-01 14:29:00.307','2019-07-01 14:49:43.390' union all
 select 2,'2019-07-01 14:29:55.470','2019-07-01 14:31:14.657' union all
 select 2,'2019-07-01 14:31:59.673','2019-07-01 14:35:00.807' union all
 select 2,'2019-06-01 14:35:32.280','2019-07-01 14:41:14.110'            -- 此处特意写06/01,以便测试小时计数.


select userID,
       dt=rtrim((dt1+cast(left(dt2,2) as int)))+'小时'+substring(dt2,4,2)+'分'+substring(dt2,7,2)+'秒'
 from (
select userID,
       dt1=sum(datediff(dd,stopstarttime,stopendtime)*24),
       dt2=convert(varchar,
                   dateadd(ms,
                           sum(datediff(ms,'00:00:00.000',cast(convert(varchar,stopendtime-stopstarttime,114) as time))),'1900-01-01 00:00:00.000'),
                   114)
 from #t
 group by userID) t

/*
userID      dt
----------- ----------------------------
1           0小时31分49秒
2           720小时10分02秒

(2 行受影响)
*/

[吃货乙]

create table #t(userID int,stopstarttime datetime,stopendtime datetime,) insert into #t select 1,'2019-07-01 14:49:55.130','2019-07-01 14:50:37.903' union all select 1,'2019-07-01 14:50:42.717','2019-07-01 14:52:07.670' union all select 1,'2019-07-01 14:52:17.160','2019-07-01 14:52:19.880' union all select 1,'2019-07-01 14:52:23.523','2019-07-01 15:01:13.510' union all select 1,'2019-07-01 14:28:41.997','2019-07-01 14:28:48.410' union all select 1,'2019-07-01 14:29:00.307','2019-07-01 14:49:43.390' union all select 2,'2019-07-01 14:29:55.470','2019-07-01 14:31:14.657' union all select 2,'2019-07-01 14:31:59.673','2019-07-01 14:35:00.807' union all select 2,'2019-06-01 14:35:32.280','2019-07-01 14:41:14.110' -- 此处特意写06/01,以便测试小时计数. select userID, dt=rtrim((dt1+cast(left(dt2,2) as int)))+'小时'+substring(dt2,4,2)+'分'+substring(dt2,7,2)+'秒' from ( select userID, dt1=sum(datediff(dd,stopstarttime,stopendtime)*24), dt2=convert(varchar, dateadd(ms, sum(datediff(ms,'00:00:00.000',cast(convert(varchar,stopendtime-stopstarttime,114) as time))),'1900-01-01 00:00:00.000'), 114) from #t group by userID) t /* userID dt ----------- ---------------------------- 1 0小时31分49秒 2 720小时10分02秒 (2 行受影响) */

[二月十六]

引用 9 楼 吃货乙 的回复:

create table #t(userID int,stopstarttime datetime,stopendtime datetime,) insert into #t select 1,'2019-07-01 14:49:55.130','2019-07-01 14:50:37.903' union all select 1,'2019-07-01 14:50:42.717','2019-07-01 14:52:07.670' union all select 1,'2019-07-01 14:52:17.160','2019-07-01 14:52:19.880' union all select 1,'2019-07-01 14:52:23.523','2019-07-01 15:01:13.510' union all select 1,'2019-07-01 14:28:41.997','2019-07-01 14:28:48.410' union all select 1,'2019-07-01 14:29:00.307','2019-07-01 14:49:43.390' union all select 2,'2019-07-01 14:29:55.470','2019-07-01 14:31:14.657' union all select 2,'2019-07-01 14:31:59.673','2019-07-01 14:35:00.807' union all select 2,'2019-06-01 14:35:32.280','2019-07-01 14:41:14.110' -- 此处特意写06/01,以便测试小时计数. select userID, dt=rtrim((dt1+cast(left(dt2,2) as int)))+'小时'+substring(dt2,4,2)+'分'+substring(dt2,7,2)+'秒' from ( select userID, dt1=sum(datediff(dd,stopstarttime,stopendtime)*24), dt2=convert(varchar, dateadd(ms, sum(datediff(ms,'00:00:00.000',cast(convert(varchar,stopendtime-stopstarttime,114) as time))),'1900-01-01 00:00:00.000'), 114) from #t group by userID) t /* userID dt ----------- ---------------------------- 1 0小时31分49秒 2 720小时10分02秒 (2 行受影响) */

sql区惊现大Y

[吃货乙]

引用 10 楼 二月十六 的回复:

[quote=引用 9 楼 吃货乙 的回复:] create table #t(userID int,stopstarttime datetime,stopendtime datetime,) insert into #t select 1,'2019-07-01 14:49:55.130','2019-07-01 14:50:37.903' union all select 1,'2019-07-01 14:50:42.717','2019-07-01 14:52:07.670' union all select 1,'2019-07-01 14:52:17.160','2019-07-01 14:52:19.880' union all select 1,'2019-07-01 14:52:23.523','2019-07-01 15:01:13.510' union all select 1,'2019-07-01 14:28:41.997','2019-07-01 14:28:48.410' union all select 1,'2019-07-01 14:29:00.307','2019-07-01 14:49:43.390' union all select 2,'2019-07-01 14:29:55.470','2019-07-01 14:31:14.657' union all select 2,'2019-07-01 14:31:59.673','2019-07-01 14:35:00.807' union all select 2,'2019-06-01 14:35:32.280','2019-07-01 14:41:14.110' -- 此处特意写06/01,以便测试小时计数. select userID, dt=rtrim((dt1+cast(left(dt2,2) as int)))+'小时'+substring(dt2,4,2)+'分'+substring(dt2,7,2)+'秒' from ( select userID, dt1=sum(datediff(dd,stopstarttime,stopendtime)*24), dt2=convert(varchar, dateadd(ms, sum(datediff(ms,'00:00:00.000',cast(convert(varchar,stopendtime-stopstarttime,114) as time))),'1900-01-01 00:00:00.000'), 114) from #t group by userID) t /* userID dt ----------- ---------------------------- 1 0小时31分49秒 2 720小时10分02秒 (2 行受影响) */

sql区惊现大Y [/quote] 2月最近忙啥呢 都没看出来说话

[二月十六]

引用 11 楼 吃货乙 的回复:

[quote=引用 10 楼 二月十六 的回复:] sql区惊现大Y

2月最近忙啥呢 都没看出来说话[/quote] 一直在加班