主页 / dba / 问题 / 318861
Accepted
Jonathan Fite
Asked: 2022-10-28 12:21:20 +0800 CST

求当前时间 X 内的第二个（时间）区间

  • 772

对于给定的当前秒数和上下边界，我需要找到所有落在该范围内的秒数，并在 59 秒标记处结束。

不用说沙拉这个词，这里有一些示例输入和输出。

  • 如果输入为 58 秒且间隔为 +/- 5 秒，那么我想返回 58、59、0、1、2、3 和 57、56、55、54、53
  • 如果 Input 是 22 且间隔相同，那么我想返回 22、23、24、25、26、27 AND 21、20、19、18、17
  • 如果 Input 为 5 且间隔相同，那么我想返回 0、1、2、3、4、5 AND 5、7、8、9、10。

我一直在使用 TIME 数据类型来尝试使用内置的 datemath 来处理 59 秒标记附近的环绕，但我取得的成功有限。这里包括我的完整非工作版本。

我对代码的状态表示歉意，我已经研究了一段时间了，我选择留下所有的点点滴滴来充分说明我的尝试。如果我找不到更优雅的解决方案，我将不得不为我们的初始间隔值建立一个带有“正确”答案的计数表，但如果可能的话，我希望拥有比这更大的灵活性。

DECLARE @CurrentSecond SMALLINT = 22 -- DATEPART(SECOND, SYSUTCDATETIME()) 
DECLARE @LowerBoundary INT  = -5
DECLARE @UpperBoundary INT = 5

;WITH CTE_Parts AS
        (
        SELECT P.ID 
            , LowerBound = DATEPART(SECOND, DATEADD(SECOND, @LowerBoundary, CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), P.ID))))
            --, lowerBoundTime = DATEADD(SECOND, @LowerBoundary, CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), P.ID)))
            , UpperBound = DATEPART(SECOND, DATEADD(SECOND, @UpperBoundary , CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), P.ID)))) 
            --, UpperBoundTime = DATEADD(SECOND, @UpperBoundary , CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), P.ID)))
            , C.LowerBoundary_Current
            --, C.LowerBoundTime_Current
            , C.UpperBoundary_Current
            --, C.UpperBoundTime_Current
        FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
                    , (10), (11), (12), (13), (14), (15), (16), (17), (18), (19)
                    , (20), (21), (22), (23), (24), (25), (26), (27), (28), (29)
                    , (30), (31), (32), (33), (34), (35), (36), (37), (38), (39)
                    , (40), (41), (42), (43), (44), (45), (46), (47), (48), (49)
                    , (50), (51), (52), (53), (54), (55), (56), (57), (58), (59)
                    ) AS P (ID)
            OUTER APPLY (SELECT DATEPART(SECOND, DATEADD(SECOND, @LowerBoundary, CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), @CurrentSecond)))) AS LowerBoundary_Current
                                , LowerBoundTime_Current = DATEADD(SECOND, @LowerBoundary, CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), @CurrentSecond)))
                                , DATEPART(SECOND, DATEADD(SECOND, @UpperBoundary, CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), @CurrentSecond))))  AS UpperBoundary_Current
                                , UpperBoundTime_Current = DATEADD(SECOND, @UpperBoundary, CONVERT(TIME, '00:00:' + CONVERT(NVARCHAR(20), @CurrentSecond)))
                            ) AS C
        )
SELECT * 
    , Ignore = CASE WHEN @CurrentSecond + @UpperBoundary <= 59 AND @CurrentSecond + @LowerBoundary >= 0 AND ID BETWEEN LowerBoundary_Current AND UpperBoundary_Current THEN 1 
                    WHEN NOT(@CurrentSecond + @UpperBoundary <= 59 AND @CurrentSecond + @LowerBoundary >= 0)
                        AND ID >= LowerBoundary_Current
                        OR ID <= UpperBoundary_Current
                        THEN 1
                    ELSE 0 END
FROM CTE_Parts AS P 
ORDER BY P.ID

绝对清楚，上面没有做我想要做的事情（除了给我一个从 0-06 的 ID 值列表）

sql-server

3 个回答

  1. Best Answer

    假设您希望在单个结果集中（不是上面的组，加上下面的组）中返回秒的范围，以下可能会满足您的需要。

    对于给出的示例，它返回预期的（*）结果。

    (*) 我认为您的第三个示例有错字，如果它遵循与其他两个示例相同的模式，我认为应该是“如果输入为 5 且间隔相同，那么我想返回 0、1、2 , 3, 4, 5 和6 , 7, 8, 9, 10。 ”

    SET NOCOUNT ON;

DECLARE @CurrentSecond SMALLINT;
DECLARE @LowerBoundary INT;
DECLARE @UpperBoundary INT;

SET @LowerBoundary = -5;
SET @UpperBoundary = 5;
SET @CurrentSecond = 58;

;WITH CTE (Offset)
AS (SELECT TOP (60)
           -30 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.objects)
SELECT DATEPART(SECOND, DATEADD(SECOND, Offset + @CurrentSecond, 0)) AS OutputSecond
FROM CTE
WHERE Offset
BETWEEN @LowerBoundary AND @UpperBoundary
ORDER BY DATEADD(SECOND, Offset + @CurrentSecond, 0);
    • 2

  2. Rob 的回答略有不同——您实际上并不需要使用日期函数。

    DECLARE @CurrentSecond SMALLINT;
DECLARE @LowerBoundary INT;
DECLARE @UpperBoundary INT;

SET @LowerBoundary = -5;
SET @UpperBoundary = 5;
SET @CurrentSecond = 58;

;WITH CTE (Offset) as
(
    SELECT TOP (150) -60 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.objects
)
SELECT Offset % 60 AS OutputSecond
FROM CTE
WHERE Offset BETWEEN @CurrentSecond + @LowerBoundary AND @CurrentSecond + @UpperBoundary
ORDER BY Offset
    • 0

  3. 我可能会这样写：

    DECLARE @CurrentSecond SMALLINT, @LowerBoundary SMALLINT, @UpperBoundary SMALLINT

SET @CurrentSecond = 58
SET @LowerBoundary = -5
SET @UpperBoundary = 5

;WITH seconds AS (
SELECT @CurrentSecond+@LowerBoundary AS second, 0 AS id
UNION ALL
SELECT CAST(CASE WHEN second+1 >= 60 THEN second+1-60
                 ELSE second+1 END AS SMALLINT), id+1 FROM seconds
 WHERE id < @UpperBoundary  + ABS(@LowerBoundary)
)

SELECT second
  FROM seconds
    • 0

相关问题