SQL - Break a start/end time into 15 minute records

Question

I've got a record set that consists of a start and end time in two separate fields:

id - Int
startTime - DateTime
endTime - DateTime

I'd like to find a way to query a record and return it as X records based on the number of 15 minute intervals found between the start and end times.

For example, let's say I have a record like this:

id, StartTime, EndTime
1, 1/1/2010 8:28 AM, 1/1/2010 8:47 AM

I would return 3 records, the first would represent the 8:15 interval, #2 for the 8:30 interval and then a 3rd for the 8:45 interval.

I realize this could be done using logic in an sproc, but we are trying to remain db neutral as we support multiple database engines.

`but we are trying to remain db neutral as we support multiple database engines.` that is a good intention, but working with dates will require database specific functions. since you need to pull rows out of thin air, sql server could use a CTE but I don't think firebird has CTE support, so look into using a [numbers table](http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table). — KM., May 27 '10 at 14:07

score 2 · Answer 1 · answered May 27 '10 at 14:06

2

Why don't you do the processing in a server-side language? It would be much easier and would definitely allow you to be db-neutral.

answered May 27 '10 at 14:06

Keith Rousseau

4,435
1
22
28

score 1 · Answer 2 · answered May 27 '10 at 14:46

There appear to be two basic approaches.
1. Iteratively caclulate each interval (either a loop or recursion)
2. Use a lookup table

As some the implentation of functions (for recursive calculation) and even the defintion for Loops will vary, the most generic would appear to be a lookup table. It is also likely to be very quick.

score 0 · Accepted Answer · answered May 28 '10 at 16:28

I agree with Keith that this is probably better done in an app. server and the cross join to the lookup table will get expensive for a large source table, but just for fun I worked up a quick sample.

   declare @QuarterHours table (
        QuarterHour time
    )

    declare @x time
    set @x = '00:00'

    insert into @QuarterHours
        (QuarterHour)
        values 
        (@x)
    set @x = DATEADD(minute, 15, @x)

    while @x <> '00:00' begin
        insert into @QuarterHours
            (QuarterHour)
            values 
            (@x)
        set @x = DATEADD(minute, 15, @x)
    end /* while */

    declare @test table (
        id int,
        starttime datetime,
        endtime datetime
    )

    insert into @test
        (id, starttime, endtime)
        values
        (1, '2010-01-01 08:28', '2010-01-01 08:47')

    select t.id, q.QuarterHour
        from @test t
            cross join @QuarterHours q
        where q.QuarterHour between cast(t.starttime as time) and cast(t.endtime as time)

SQL - Break a start/end time into 15 minute records

3 Answers3

Linked