SQL Server Query – Finding Second Interval Within X of Current Time

datetimequeryrecursivesql server

I have a need to find, for a given current second and upper and lower boundary, find all of the seconds that would fall within that, wrapping at the 59 second mark.

With that word salad out of the way, here are some example inputs and outputs.

  • If the Input is 58 seconds and the interval is +/- 5 seconds, then I
    want to return 58, 59, 0, 1, 2, 3 AND 57, 56, 55, 54, 53
  • If the Input is 22 and the same interval, then I want to return 22,
    23, 24, 25, 26, 27 AND 21, 20, 19, 18, 17
  • If the Input is 5 and the same interval, then I want to return 0, 1,
    2, 3, 4, 5 AND 5, 7, 8, 9, 10.

I've been playing with the TIME datatype to try and use the built-in datemath to handle wrapping around the 59 second mark, but I've had limited success. Included here is my full non-working version.

My apologies for the state of the code, I've been working on it for a while now and I'm opting to leave in all my bits and bobs to fully illustrate what I've tried. If I can't find a more elegant solution, I'm going to have to build up a tally table with the "correct" answer for our initial interval value, but I'd like to have more flexibility than that if possible.

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

To be absolutely clear, the above does NOT do what I want it to do (except give me a list of ID values from 0-06)

Best Answer

On the assumption that you want the range of seconds returned in a single resultset (not an above group, plus a below group), the following might do what you need.

For the examples given it returns the expected(*) results.

(*) I think your third example has a typo, if it follows the same pattern as the other two examples, I believe it should be "If the Input is 5 and the same interval, then I want to return 0, 1, 2, 3, 4, 5 AND 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);
Related Question