如何在 SQL 中分配多天的差异(以分钟为单位)

Question

我目前正在制作一个表格来表示我们所有公司的系统停机时间。主要目标是让一个表格显示每个日期以及总上涨和下跌。我遇到的主要问题是，每当我们遇到持续超过 24 小时的事情时，我如何将适当的时间分配给适当的日期？

在我们的数据库中，我正在使用 4 个主要字段：DateDown、TimeDown、DateUp、TimeUp。我将时间连接在一起，然后找到两者之间的分钟差异。假设某事在 8/12/2019 9：00 下降，出现 8/15/19 14：00。我以几分钟为单位找到了 DATEDIFF，但现在面临着与一天相关的总停机时间，而不是分配给每一天。

有什么提示或指示吗？谢谢。

Answer 1

看看这个：

--begin row generator block - this generates a list of days over the past year
WITH r (today, tomorrow) AS (
-- Anchor member definition
SELECT CAST(CAST(GetDate() as DATE) as DATETIME) AS today, CAST(CAST(GetDate() as DATE) as DATETIME) + 1 AS tomorrow
UNION ALL
-- Recursive member definition
SELECT DATEADD(day, -1, today), DATEADD(day, -1, tomorrow)
FROM   r
WHERE  today > GetDate() - 365
),
-- end row generator
-- begin CTE that fixes your downtime table
downtimetable as (
SELECT
CAST('2019-07-01' as DATE) datedown, 
CAST('2019-07-01' as DATE) dateup, 
CAST('2019-07-01 12:00:00' as DATETIME) as datetimedown, -- fix this so your date and time down columns are a single datetime
CAST('2019-07-01 13:00:00' as DATETIME) as datetimeup,
'downupsameday' as descr
UNION ALL
SELECT
'2019-07-01',
'2019-07-02',
'2019-07-01 14:00:00',
'2019-07-02 08:00:00',
'downovermidnight_10h_8h'
UNION ALL
SELECT
'2019-07-01',
'2019-07-03',
'2019-07-01 20:00:00',
'2019-07-03 06:00:00',
'downovertwomidnights_4h_24h_6h'
)
--end CTE that fixes your downtime table 
SELECT 
CASE
--downtime starts today and ends today
WHEN d.datetimedown >= r.today AND d.datetimeup < r.tomorrow THEN DATEDIFF(minute, d.datetimedown, d.datetimeup)
--downtime started before today and ends today
WHEN d.datetimedown < r.today AND d.datetimeup < r.tomorrow THEN DATEDIFF(minute, r.today, d.datetimeup)
--downtime started today and ends after tomorrow
WHEN d.datetimedown >= r.today AND d.datetimeup >= r.tomorrow THEN DATEDIFF(minute, d.datetimedown, r.tomorrow)
--downtime started before today and ends after tomorrow
ELSE 1440
END as minutes,
*
FROM   
r
INNER JOIN downtimetable d ON r.today BETWEEN d.datedown and d.dateup
ORDER BY r.today
OPTION (MAXRECURSION 366) --this is needed because the default recursion for the row generator is 100

---

我忘了记下它是如何工作的。一旦生成器列出了过去一年的日期(每天午夜(，它就会用"between"连接到您的上下表。如果关闭日期在例如第 1 天，而启动日期在第 3 天，则连接条件将导致该下降/向上数据行重复，因此有 3 个实例，每天一个。然后，当只查看所涉及的日期时，这就是我们如何将您的起伏分解为"今天下降了多长时间(今天是行生成器的日期而不是上下表(">

现在您需要做的是：编辑"开始/结束修复停机时间表的 CTE"之间的部分：

您必须使用 CTE 重新调整停机时间表，以便其中的日期显示为服务关闭/上升的日期，以及服务关闭/上升的日期时间(我所说的日期时间是指结合了日期和时间的 sqlserver 数据类型。

理想情况下，当事情上升和下降时，您应该将日期存储为 DATETIME 类型，而不是有两个单独的列(我担心您将日期/时间存储为 varchar：不要这样做(。日期和时间应该在存储中组合，因为这是它们最有用的方式(对于数学/使用它们(。要从 DateTime 中剥离时间元素，请将其转换为日期 - 从日期时间转到日期比从日期 + 时间分离到日期时间要容易得多/干净得多。我发现一个时间本身很少那么有用