SQL查询以获取日期时间范围减去中断时间范围的持续时间？-解网

问：

在 SQL Server 2019 中，我有一个表，其中包含时间范围，其中开始日期时间列为 UTC，结束日期时间列为 UTC。Runs

我还有另一个表格，即休息时间范围，格式为本地开始时间到结束时间本地时间。BreakSchedule

给定一个时区作为 SQL 参数，我想从第一个表（运行）中选择多行，并获取从开始 UTC 日期时间列到结束 UTC 日期时间列的总持续时间（以分钟为单位）减去时区调整时间在该特定时间范围范围内的任何中断时间。

下面是一个示例：

create table Runs 
(
    id int identity(1,1) primary key,
    dtStartTimeUtc datetime not null,
    dtEndTimeUtc datetime not null,
    nExpectedMinutes int not null
);

create table BreakSchedule 
(
    tStartTimeLocal time not null,
    tEndTimeLocal time not null
);

insert into BreakSchedule (tStartTimeLocal, tEndTimeLocal)
values ('08:30:00', '08:40:00'), ('09:30:00', '10:30:00'), 
       ('17:00:00', '18:00:00'), ('23:30:00', '00:30:00');

/*
  Equivalent in UTC if local time zone is Central Standard Time
  13:30-13:40, 14:30-15:30, 22:00-23:00, 04:30-05:30
*/

insert into Runs (dtStartTimeUtc, dtEndTimeUtc, nExpectedMinutes)
values
  ('2023-10-02 12:00:00', '2023-10-02 13:30:00', 90),
  ('2023-10-02 13:00:00', '2023-10-02 13:35:00', 30),
  ('2023-10-02 13:35:00', '2023-10-02 14:00:00', 20),
  ('2023-10-02 14:15:00', '2023-10-03 05:15:00', 735),
  ('2023-10-02 14:30:00', '2023-10-02 15:00:00', 0)

查询的起点我不知道如何完成：

declare @tz varchar(100) = 'Central Standard Time';

select 
    *, 'should be same value as nExpectedMinutes' [actualminutes]
from  
    Runs

上面的代码也在这里用 SQL 小提琴： http://sqlfiddle.com/#!18/42fdd/11

谁能帮忙？

sql-server 日期时间

DECLARE @Runs TABLE (id int identity(1,1) primary key, dtStartTimeUtc datetimeoffset not null, dtEndTimeUtc datetimeoffset not null, nExpectedMinutes int not null);
DECLARE @BreakSchedule TABLE (tStartTimeLocal time not null, tEndTimeLocal time not null);
insert into @BreakSchedule (tStartTimeLocal, tEndTimeLocal) values 
('08:30:00', '08:40:00'), ('09:30:00', '10:30:00'), ('17:00:00', '18:00:00'), ('23:30:00', '00:30:00');

/*
  Equivalent in UTC if local time zone is Central Standard Time
  13:30-13:40, 14:30-15:30, 22:00-23:00, 04:30-05:30
*/

insert into @Runs (dtStartTimeUtc, dtEndTimeUtc, nExpectedMinutes) values
('2023-10-02 12:00:00', '2023-10-02 13:30:00', 90),  ('2023-10-02 13:00:00', '2023-10-02 13:35:00', 30),  ('2023-10-02 13:35:00', '2023-10-02 14:00:00', 20),  ('2023-10-02 14:15:00', '2023-10-03 05:15:00', 735),  ('2023-10-02 14:30:00', '2023-10-02 15:00:00', 0);

DECLARE @tz NVARCHAR(50) = 'Central Standard Time'

SELECT r.id, r.dtStartTimeUtc, r.dtEndTimeUtc, r.nExpectedMinutes, SUM(DATEDIFF(MINUTE,b.tStartTimeLocal, b.tEndTimeLocal)) AS breakMinutes
  FROM @Runs r
    LEFT OUTER JOIN @BreakSchedule b
      ON b.tStartTimeLocal BETWEEN CAST(r.dtStartTimeUtc AT TIME ZONE @tz AS TIME) AND CAST(r.dtEndTimeUtc AT TIME ZONE @tz  AS TIME)
 GROUP BY r.id, r.dtStartTimeUtc, r.dtEndTimeUtc, r.nExpectedMinutes;

感谢您提供 DDL/DML！我修改了您的数据类型，将 in Runs 更改为 .如果无法修改列，则可以将其作为内联强制转换来完成。DATETIMEDATETIMEOFFSET

有了 a，我们可以使用修饰符来调整日期时间的时区，使用标准时区名称。DATETIMEOFFSETAT TIME ZONE

从那里，我们可以联接 BreakSchedule 表，通过内联强制转换来收集休息分钟数。我总结了如果一次跑步跨越了不止一次的休息时间。TIME

编号	dtStartTimeUTC	dtEndTimeUTC	n预期分钟	breakMinutes
1	2023-10-02 12:00:00.0000000 +00:00	2023-10-02 13:30:00.0000000 +00:00	90	10
2	2023-10-02 13:00:00.0000000 +00:00	2023-10-02 13:35:00.0000000 +00:00	30	10
3	2023-10-02 13:35:00.0000000 +00:00	2023-10-02 14:00:00.0000000 +00:00	20
4	2023-10-02 14:15:00.0000000 +00:00	2023-10-03 05:15:00.0000000 +00:00	735
5	2023-10-02 14:30:00.0000000 +00:00	2023-10-02 15:00:00.0000000 +00:00	0	60

DATETIMEOFFSET https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetimeoffset-transact-sql?view=sql-server-ver16

AT TIME ZONE https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver16

公平地说，Stack Overflow 不是一个代码编写服务。我的目的是给你足够的解决具体问题，而不是:)提供一个完全充实的解决方案。要通过午夜休息时间，您可能需要将它们分成午夜之前和午夜之后的片段，因为它们是“本地的”，您可以在数据中执行此操作，也许吧？

上一个：将 ISO 格式的字符串日期时间转换为 ANSI

下一个：sp_get_query_template不适用于 ODBC 时间戳

SQL查询以获取日期时间范围减去中断时间范围的持续时间？

SQL query to get duration of datetime ranges minus break time ranges?

评论

评论