Oftentimes you’ll need to generate a table of time slots to use in much the same way as a Calendar table. You might want to use this to do aggregations for reports, such as the amount of trading by the time of day, and for doing this a table of time-slots within a day makes it easy, and more flexible. You can, of course, do this without any Time-Slot tables until things get more complicated, as when the business only runs two shifts every twenty-four hours, and you only need the time slots from 05:00 to 20:00 (sixteen time slots with eight hours in each shift), or when you need to generate time slots of over an hour (like every four hours) or minutes, etc.

“Generate them when you need them.”
— SQL MVP Jeff Moden on time slots

Indeed, since we’re all probably familiar with Tally tables, whether in a physical table or one that is generated in-line, this is relatively easy to accomplish.

Using Time-slots: A Real World Example

Suppose we run a manufacturing business that has three machines producing widgets. Each machine produces a discrete quantity of widgets, but spits out that discrete quantity at irregular intervals. Let’s create some sample data for this.

Note that the WHERE filter creates some gaps simulating a case where MachineID=1 was not operating on 2015-04-02 and MachineID=2 was not operating on 2015-04-01.

Now suppose we’d like to track our widgets production every six hours (our time slot increment) for the three days we’ve created our data, including those time slots where a machine produced nothing.

A troubling aspect, to me at least, of the above code is that we have essentially hard-coded our time slots. However it does produce the results we have asked for. In the above, we’ve created a Cartesian product of machines by days by time slots, and then retrieved the total widgets created in each time slot.

Let’s suppose one of our pesky business users comes along and tells us he’d like to change the time slot duration to six hours. Since we hard-coded that the first time, we’d now like to anticipate that he may change it again, and let’s also add something just a bit more complex, such as a running total by machine by day. So we’ll add a local variable that specifies the time slot duration, and use it to dynamically generate our time slots within the TimeSlotsOffsets Common Table Expression (CTE). Other than adding the running total by day, we’ll still calculate the same Cartesian product of machines by days by time slots and we’re good to go.

If you run the setup data and the query, it should produce a results set that looks something like the following. Of course your results will vary due to the random test data that we set up, however you should at least see the same gaps in production due to the machine outages we introduced into the test data.

This query generates a total of 36 rows (3 days x 3 machines x 4 time slots per day).

In this article, we’re going to show you how to remove a whole bunch of extraneous code, such as the two CTEs, and minimize the effort to create the Cartesian product across any duration of time slot using a function I’ll call CalculateTimeSlots, which will be introduced just a bit later.

An In-line Tally Table for Generating Time Slots

To generate time slots of any duration, even down to the second, we can use an in-line Tally table such as the one below.

Our Tally table has been specifically designed to return exactly 86,400 rows, which just happens to correspond to the number of seconds in a day. It is a zero-based Tally table, so we can use it directly as a time offset.

Time Slots – Hours or Minutes in a Day

By adding a TOP clause to our Tally table along with a little DATEADD logic, we can quite easily create any number of time slots that are based on hours, minutes or seconds. The TOP clause will minimize the actual number of rows returned by the Tally table, to just what we need for our time slot duration.

Notice how we’ve adjusted the TOP clause in each case to the number of time slots we want to generate, and then used the return value from the Tally table to add either hours or minutes to midnight.

We could just as easily have adjusted both the base time and the number of time slots (in TOP) to give us our two eight hour shifts that run from 05:00 to 20:00.

Creating Time Slots across Multiple Days

If we combine our time slot generator with a Calendar table, we can generate our time slots across multiple calendar days. In the next example, I use an in-line, Table Valued Function (iTVF) that appears in the blog entry I linked to earlier about Calendar tables. It is also included in the resources file that appears with this article in case you’d like to follow along and try some of the examples here.

My GenerateCalendar FUNCTION returns [Date] (actually a DATETIME) as one of its columns. It is necessary to convert the time slot to a time offset in hours using DATEDIFF, and then add this back to the [Date] column to get our time slot within that specific day. The query above returns 48 rows (some omitted for brevity).

Another Example: Fifteen Minute Time Slots

Suppose we want to generate time slots that are precisely fifteen minutes apart. We know that there are 1,440 (24 x 60) minutes in a day, so there should be one-fourth this number of time periods if we want them spaced 15 minutes apart (so 96 time slots).

We’ve had to do two things to our query: 1) modify the TOP clause to return precisely the correct number of 15 minute time slots we calculated earlier and 2) multiply our Tally return column by that same 15 minutes. The results are as we expect, once again abbreviated for clarity.

Clock Arithmetic is for Geeks, so let’s just Do It Once

I don’t know how all of my readers feel about this, but personally I hate clock arithmetic. All of those magic numbers – seconds in a day, minutes in a day, etc. are just something I hate having to remember all the time. So while Jeff may be right, that you can easily generate time slots of any sort when you need them, I’d prefer to only have to do it once so I don’t have to remember where to divide by 1,440, or do anything else that requires me to think about this for more than a microsecond.

Perhaps it might be possible to construct a nice, tidy little iTVF to satisfy the most common time slot needs. After a bit of fiddling around, I came up with this, which can also be found in the attached resources file.

We’ve created the arguments to this function based on the premise that your time slots are either in hours, minutes or seconds. You’ll need to do a bit of work if you want to extend it to milliseconds or microseconds.

Let’s describe each of the arguments, including the last two which I call “extended arguments” because of the additional value they add.

• @SlotType (VARCHAR(6)) is passed as “second” for seconds, “minute” for minutes and “hour” for hours. It also supports the standard abbreviation for these “dateparts” as defined for the DATEADD and DATEDIFF T-SQL functions, in the hopes that this makes them easier to remember.
• @SlotDuration (SMALLINT) is the duration you want for each time slot. For example, time slots that are in hours where you want 24 of them in a day are each one hour in duration. @SlotDuration must be specified as an integer that equally divides into the number of units of @SlotType that are present in the next larger time unit. For example, in a minute there are sixty seconds, so if you specify @SlotType = ‘second’ then @SlotDuration must be either 1, 2, 3, 4, 5, 6, 10, 12, 15 or 30 (a number that divides into 60 with no remainder).
• @BaseDate (DATETIME) is the first of two extended arguments that can be used if you want your time slots to include not just a time component but also a date component. Coupled with additional return columns, this can be quite useful in cases where you need your time slots generated across many days.
• @NumberOfDays (INT) is the second of two extended arguments that (in conjunction with @BaseDate) specifies the number of days or dates for which you want the time slots enumerated.

We’ll clarify each of these arguments along with the returned columns from the function as we look at some examples, starting with an easy one. The comments in the function hopefully explain what the function does. Using the extended parameters allows you to generate a Cartesian product of days by time slots.

To return 24 time slots of one hour, any of these parameter combinations will work:

To handle our two eight hour shifts starting at 05:00, we simply add a WHERE filter.

You can try the alternate FROM combinations to see how an invalid slot increment gets translated to one.

To get our 96 time slots at 15 minute intervals, the call is equally simple.

We can generate our time slots across two days using our GenerateCalendar function like we did before:

Or we can simply use the extended call parameters to simplify the query even further. In this case, the four columns all return the same amounts (shown so you’ll understand just how much more simplified this query is).

To me that looks much easier on the eyes than what we did before! Even with the Timeslot1, TimeSlot2, TimeSlot3 variables which I included for illustration of you can use other columns returned by the FUNCTION.

Calculations vs. Lookups

“Since SQL is a database language, we prefer to do look ups and not calculations.”
– Joe Celko (prolific author of books on things SQL)

Ah yes, the on-going debate on whether we should do calculations or lookups has raised its ugly head yet again!

In that Calendar table link that I gave you earlier and also in another blog (An Easter SQL), we showed how you could do either calculations (if you prefer) or lookups if that’s where your preferences are.

Time slot lookups pose a slightly more challenging conundrum than do holiday tables though. Let’s look at some of the ways we could construct them.

Hourly or By Every Fifteen Minutes (Separate Time Slot Tables)

We could create a separate table for every time slot combination we need for the use-cases in our application.

That seems a little redundant to me but if it floats your boat, go for it!

Hourly or By Every Fifteen Minutes (A Single Time Slot Table)

An alternative could be to create a generic time slot lookup that handles multiple time slot types, like this:

Then it just becomes a matter of remembering all of the SlotTypes you defined.

You could of course use two time slot definition columns as part of the PRIMARY KEY, for example where they are identical to the arguments to the CalculateTimeSlots FUNCTION. We’ll let you decide if that is better than using the FUNCTION directly.

Various Time Slots in Different Columns of a Single Time Slots Table

Another way perhaps, is something like the following where your table rows correspond to time slots by second (the PRIMARY KEY), and other necessary time slots are created as additional columns that are sparsely populated.

Now I’m not saying I recommend this, but at least with the indexing defined when you do a query such as the following:

You will get a NONCLUSTERED INDEX SEEK in the execution plan.

But this does mean you’d have quite a few INDEXes!

So the choice is up to you: 1) calculate them on the fly with home grown code for each case, 2) use an iTVF to hide the magic of the time slot calculations or 3) choose one of the lookup table examples I’ve provided.

Using Time-slots: A Real World Example (Revisited)

All that is left now is to use our nifty new CalculateTimeSlots function to simplify our real world example.

Notice how both CTEs have been eliminated, and we simplified the section containing the Cartesian product. Obviously, additional local variables can be added or calculated to generate and pass the third and fourth parameters to our iTVF function call.

This is but one of many real world examples where I’ve found it necessary to generate varying durations of time slots across a varying number of days. Sometimes the time slots are by hours, other times by multiple (such as fifteen) minutes, etc. So this is a generalized tool for generating those time slots when you need them.

Conclusions

We’ve described three different ways you can generate fixed duration time slots within a day.

• Generate them with ad-hoc (or home grown) code for the particular case.
• Use the CalculateTimeSlots FUNCTION, which allows you to generate the Cartesian product of days by time slots if you need to.
• Use a lookup table, for which you can vary the content according to your needs.

While the debate will probably rage on, whether to use lookups vs. calculations, I think my preference in this case is going to be the second alternative (my CalculateTimeSlots FUNCTION).

We haven’t shown it but your time slots are easily combined with an existing Calendar table (dbo.Calendar) if you have one so that you can filter by date and/or by time slot.

Thanks for listening folks, and I hope that if you ever need to generate fixed duration time slots to support your application, you’ll find this article and embrace whichever technique suits your fancy.