时间戳

首页 > 解决方案 > 如何在 TSQL 中的两个日期之间获得星期二凌晨 4 点有多少小时

sql - 如何在 TSQL 中的两个日期之间获得星期二凌晨 4 点有多少小时

问题描述

我有一个非常具体的统计数据，我需要从中获取信息。

这是这个问题的后续，它只询问凌晨 4 点，我现在还需要补充一点，那是星期二

如何获取 TSQL 中的两个日期之间的凌晨 4 点有多少小时

我需要计算出两个日期之间在一周中的某一天出现了多少次特定时间。

例如，我需要知道两个日期之间有多少个星期二凌晨 4 点。

例如，以下将给出结果 = 1，因为星期二有 1 次出现 4AM

declare @date1 datetime = '2019-10-01 00:00:00.000';
declare @date2 datetime = '2019-10-02 00:00:00.000';

例如，以下将给出结果 = 0，因为周二有 0 次凌晨 4 点

declare @date1 datetime = '2019-10-01 05:00:00.000';
declare @date2 datetime = '2019-10-02 00:00:00.000';

例如，以下将给出结果 = 2，因为已经有 2 个 4AM

declare @date1 datetime = '2019-10-01 03:00:00.000';
declare @date2 datetime = '2019-10-08 05:00:00.000';

例如，以下将给出结果 = 2，因为有 2 个凌晨 4 点，即使它们仅在两个星期二的凌晨 4:00 时间

declare @date1 datetime = '2019-10-01 04:00:00.000';
declare @date2 datetime = '2019-10-08 04:00:00.000';

我有一些代码可以获取两个日期之间一周中有多少天，以及两个日期之间有多少个凌晨 4 点，但没有合并。

这是我尝试过的代码，它适用于我的 4 个测试中的 3 个。

SET DATEFIRST 1
declare @HourOfDay int = 4

declare @tests table (
    date1 datetime,
    date2 datetime
)

INSERT INTO @tests VALUES
('2019-10-01 00:00:00.000', '2019-10-02 00:00:00.000'), -- returns 1 CORRECT
('2019-10-01 05:00:00.000', '2019-10-02 00:00:00.000'), -- returns 1 INCORRECT
('2019-10-01 03:00:00.000', '2019-10-08 05:00:00.000'), -- returns 2 CORRECT
('2019-10-01 04:00:00.000', '2019-10-08 04:00:00.000'); -- returns 2 CORRECT

select
date1,
date2,
DATEDIFF(DAY,DATEADD(SECOND,-1,DATEADD(HOUR, -1 * @HourOfDay, date1)),DATEADD(HOUR, -1 * @HourOfDay, date2)) / 7 + 1
from @tests

标签： sqlsql-servertsql

解决方案

我建议使用日历表，但您也可以使用临时计数/数字表

在子查询 B 中，我们正在创建一个从 2015 年 12 月 31 日开始的 10,000 天的动态日历表。 您可以调整或使动态

例子

Declare @YourTable table (Date1 datetime,Date2 datetime)
Insert Into @YourTable values
 ('2019-10-01 00:00:00.000','2019-10-02 00:00:00.000')
,('2019-10-01 05:00:00.000','2019-10-02 00:00:00.000')
,('2019-10-01 03:00:00.000','2019-10-08 05:00:00.000')
,('2019-10-01 04:00:00.000','2019-10-08 04:00:00.000')



Select A.*
      ,Cnt=count(D)
 From  @YourTable A
 Left Join  (
        Select D = DateAdd(DAY,N,'2015-12-31 04:00:00')
         From ( Select Top 10000 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) A
       ) B
 On DateName(WEEKDAY,D)='Tuesday'
   and D >=Date1 and D<=Date2
 Group By Date1,Date2

退货

Date1                    Date2                      Cnt
2019-10-01 00:00:00.000  2019-10-02 00:00:00.000    1
2019-10-01 05:00:00.000  2019-10-02 00:00:00.000    0
2019-10-01 04:00:00.000  2019-10-08 04:00:00.000    2
2019-10-01 03:00:00.000  2019-10-08 05:00:00.000    2

推荐阅读