Finding the Second (time) Interval within X of the current time

Question

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)

Answer 1

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);

Answer 2

A minor variation on Rob's answer - you don't really need to use date functions.

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

Answer 3

I would probably write this:

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