Efficiently Slicing 24 Hours with SQL Server: Part 1

Welcome to the first part of our two-part series on efficiently slicing 24 hours using SQL Server functions. In this series, we’ll explore a practical approach to divide a day into evenly spaced time intervals. The goal is to create a user-defined function (UDF) that generates time periods based on a specified interval. In this first installment, we’ll delve into the creation of the function and its core logic.

The Challenge

Imagine you need to split a day into equal time intervals, say every 60 minutes, and represent these intervals as start and stop times. This scenario is common in various applications such as scheduling, reporting, or data aggregation. The function dbo.udf_EvenlySlice24hours aims to tackle this challenge.

The Function

Let’s take a closer look at the function:

CREATE FUNCTION dbo.udf_EvenlySlice24hours (@Interval INT)
RETURNS @timePeriods TABLE (
    StartTime VARCHAR(8) NULL,
    StopTime VARCHAR(8) NULL
)
AS
BEGIN
    -- Function logic goes here
END

This function returns a table variable @timePeriods containing start and stop times. Users provide the desired time interval in minutes as a parameter. The function then generates time periods based on this interval.

Inside the Function

The function uses a loop to iterate through the day, creating time periods until the end of the day is reached. It employs a constant date as a reference point to avoid issues with changing time values. Here’s a breakdown of the core logic:

-- Initialization
DECLARE @start DATETIME, @stop DATETIME, @AnyConstantDate DATETIME
SET @AnyConstantDate = '1 Jan 2023'

-- Default time is 00:00:00
SET @start = @AnyConstantDate
SET @stop = dateadd(ss, 59, dateadd(mi, @Interval - 1, @start))

-- Loop to generate time periods
WHILE (dateadd(day, 0, datediff(day, 0, @stop)) = dateadd(day, 0, datediff(day, 0, @AnyConstantDate)))
BEGIN
    INSERT INTO @timePeriods
    SELECT convert(VARCHAR(8), @start, 108) 'StartTime',
           convert(VARCHAR(8), @stop, 108) 'StopTime'

    SET @start = dateadd(mi, @Interval, @start)
    SET @stop = dateadd(mi, @Interval, @stop)
END

This logic ensures that the resulting time periods cover the entire day, with each interval specified by the user.

Testing the Function

Uncomment the --select * from @timePeriods line at the end of the function to test and view the generated time periods.

-- Sample Usage
DECLARE @Result TABLE (
    StartTime VARCHAR(8),
    StopTime VARCHAR(8)
)

INSERT INTO @Result
SELECT StartTime, StopTime
FROM dbo.udf_EvenlySlice24hours(60)

-- Display the result
SELECT * FROM @Result

Assuming the function is created in your database, running the sample usage code above will populate the @Result table variable with the generated time periods and then display the result. The output might look something like this:

StartTime StopTime
--------- --------
00:00:00  00:59:59
01:00:00  01:59:59
02:00:00  02:59:59
...       ...
22:00:00  22:59:59
23:00:00  23:59:59

This result illustrates time intervals of 60 minutes throughout a 24-hour day. Adjust the interval parameter as needed for your specific use case. In Part 2 of the series, we’ll explore practical use cases and optimisations for this function. Happy coding!