时间戳

首页 > 解决方案 > SQL 计算总停机时间分钟

sql - SQL 计算总停机时间分钟

问题描述

我正在开发一个停机时间管理系统，该系统能够为数据库中的问题保存支持票，我的数据库具有以下列：

-ID
-DateOpen
-DateClosed
-Total

我想获得一天中的分钟总和，考虑到票可以是同时的，例如：

ID    |     DateOpen                    |        DateClosed       | Total 
1          2019-04-01 08:00:00 AM            2019-04-01 08:45:00    45
2          2019-04-01 08:10:00 AM            2019-04-01 08:20:00    10
3          2019-04-01 09:06:00 AM            2019-04-01 09:07:00    1
4          2019-04-01 09:06:00 AM            2019-04-01 09:41:00    33

有人可以帮我解决这个问题！！：C

如果我使用查询“SUM”，它将返回 89，但如果您查看日期，您会明白实际结果必须是 78，因为票 2 和票 3 是在另一个票正在工作时启动的......

DECLARE @DateOpen date = '2019-04-01'

SELECT AlarmID, DateOpen, DateClosed, TDT FROM AlarmHistory 
WHERE CONVERT(date,DateOpen) = @DateOpen

标签： sqlsql-serverdatetimesummarygaps-and-islands

解决方案

您需要做的是生成一个整数序列并使用它来生成一天中的时间。在您的开盘日期和结束日期之间加入该时间序列，然后计算不同时间的数量。

这是一个适用于 MySQL 的示例：

 SET @row_num = 0;

 SELECT COUNT(DISTINCT time_stamp)
         -- this simulates your dateopen and dateclosed table
   FROM (SELECT '2019-04-01 08:00:00' open_time, '2019-04-01 08:45:00' close_time
         UNION SELECT '2019-04-01 08:10:00', '2019-04-01 08:20:00'
         UNION SELECT '2019-04-01 09:06:00', '2019-04-01 09:07:00'
         UNION SELECT '2019-04-01 09:06:00', '2019-04-01 09:41:00') times_used
   JOIN (
         -- generate sequence of minutes in day
         SELECT TIME(sequence*100) time_stamp
           FROM (
             -- create sequence 1 - 10000
                 SELECT (@row_num:=@row_num + 1) AS sequence
                   FROM {table_with_10k+_records}
                  LIMIT 10000
                ) minutes
         HAVING time_stamp IS NOT NULL
          LIMIT 1440
       ) times ON (time_stamp >= TIME(open_time) AND time_stamp < TIME(close_time));

由于您只选择在结果中找到的不同时间，因此不会计算重叠的分钟数。

注意：根据您的数据库，可能有更好的方法来生成序列。MySQL 没有生成序列功能我这样做是为了展示可以轻松转换为与您正在使用的任何数据库一起使用的基本思想。

推荐阅读