SQL-将开始/结束时间分成15分钟的记录 [英] SQL - Break a start/end time into 15 minute records

问题描述

我有一个记录集，其中包含两个单独字段中的开始时间和结束时间:

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

id - Int
startTime - DateTime
endTime - DateTime

我想根据在开始时间和结束时间之间发现的15分钟间隔，找到一种查询记录并将其作为X记录返回的方法.

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

我将返回3条记录，第一个代表8:15间隔，#2代表8:30间隔，然后第三个代表8:45间隔.

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.

我意识到可以使用sproc中的逻辑来完成此操作，但是由于我们支持多个数据库引擎，因此我们试图保持数据库中立.

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.

推荐答案

我同意Keith的观点，认为这可能是在应用程序中更好的方法.对于大型源表，服务器和交叉连接到查找表将变得昂贵，但只是出于娱乐目的，我编写了一个快速示例.

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-将开始/结束时间分成15分钟的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！