时间戳

首页 > 解决方案 > 如何更新表中所有行之间的日期时间差？

sql - 如何更新表中所有行之间的日期时间差？

问题描述

我有下表，其中包含一些数据：

CREATE TABLE schedules
(
    ID        INT PRIMARY KEY,
    StartTime DATETIMEOFFSET(4)
);

INSERT INTO schedules
VALUES (1, '2020-03-27 08:00:00.000 -05:00'),
       (2, '2020-03-27 09:00:00.000 -05:00'),
       (3, '2020-03-27 10:00:00.000 -05:00'),
       (4, '2020-03-27 11:00:00.000 -05:00');

目前，开始时间的间隔是每隔一小时。我想运行一个查询以将此间隔更改为每 45 分钟一次。我尝试了以下查询，但没有奏效。

UPDATE r2
SET StartTime = DATEADD(MINUTE, 45, r1.StartTime)
FROM schedules r1 JOIN schedules r2 ON r1.ID + 1 = r2.ID;

标签： sqlsql-server

解决方案

正如我在评论中所建议的那样，在这里重新填充表格会容易得多，特别是如果实际表格中有超过 4 行（我怀疑你这样做）。

为此，我将使用TRUNCATE然后重新填充一个 Tally：

TRUNCATE TABLE dbo.schedules;
GO

DECLARE @StartTime datetimeoffset(4) = '2020-03-27 08:00:00.000 -05:00',
        @EndTime datetimeoffset(4) = '2020-03-27 11:00:00.000 -05:00';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP ((DATEDIFF(MINUTE, @StartTime, @EndTime) /45) + 1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
    FROM N N1, N N2, N N3, N N4, N N5), --100,000 rows
Dates AS(
    SELECT I+1 AS ID,
           DATEADD(MINUTE,I*45,@StartTime) AS StartTime
    FROM Tally T)
INSERT INTO dbo.schedules (ID, StartTime)
SELECT ID, StartTime
FROM Dates;

同样值得注意的是，这将创建您需要的新行。例如，如果我们使用您拥有的示例行，则更改为 45 分钟将意味着在2020-03-27 11:00:00.000 -05:00. 另一方面，此方法创建 5 行，因为两个日期时间之间有 5 个 45 分钟间隔。

推荐阅读