locked
Split datetime records with 5 minute intervals RRS feed

  • Question

  • Hi I am rather new to tsql and have the following problem:

     

    I have a database with the following fields and records:

     

    Id   Startdate                          Enddate  

    1    01/01/2007 09:00:00     01/01/2007 09:10:00

    2    01/01/2007 09:15:00     01/01/2007 09:25:00

    3    02/01/2007 12:00:00     02/02/2007 12:30:00

     

    I'd like to split these up in records with 5 minute intervals for a datawarehouse solution. So the records would look as follows:

     

    1   01/01/2007 09:00:00    01/01/2007 09:05:00

    1   01/01/2007 09:05:00    01/01/2007 09:10:00

    2   01/01/2007 09:15:00    01/01/2007 09:20:00

    2   01/01/2007 09:20:00    01/01/2007 09:25:00

    3   02/01/2007 12:00:00    02/01/2007 12:05:00

    3   02/01/2007 12:05:00    02/01/2007 12:10:00

    3   02/01/2007 12:10:00    02/01/2007 12:15:00

    3  etc.

     

    I hope my problem is clear and that someone can come up with a solution. Thanx in advance.

     

    Greetings, P

     

    Monday, April 2, 2007 2:45 PM

Answers

  • Hi

     

    Here are a couple of solutions.  You can only use the common table expression solution if you have SQL Server 2005.


    Code Snippet
    DECLARE @TestTable table
    (
       RecID int,
       StartDate datetime,
       EndDate datetime
    )

    SET NOCOUNT ON
     
    INSERT INTO @TestTable Values ( 1, '2007-01-01 09:00:00', '2007-01-01 09:10:00' )
    INSERT INTO @TestTable Values ( 2, '2007-01-01 09:15:00', '2007-01-01 09:25:00' )
    INSERT INTO @TestTable Values ( 3, '2007-01-02 12:00:00', '2007-01-02 12:30:00' )
    INSERT INTO @TestTable Values ( 4, '2007-01-03 06:00:00', '2007-01-03 06:12:00' )

    -- USING A RECURSIVE COMMON TABLE EXPRESSION (CTE)
    --------------------------------------------------
    ;WITH DateList (RecID, StartDate, EndDate) AS
    (
        SELECT RecID, StartDate,
            CASE
                WHEN (DATEADD(mi, 5, startdate) <= enddate) THEN
                    DATEADD(mi, 5, startdate)
                ELSE EndDate
            END
        FROM @TestTable
    UNION ALL
        SELECT TT.RecID, DL.EndDate,
            CASE
                WHEN (DATEADD(mi, 5, DL.enddate) <= TT.enddate) THEN
                    DATEADD(mi, 5, DL.enddate)
                ELSE TT.EndDate
            END
        FROM @TestTable TT
            INNER JOIN DateList DL
                ON (TT.RecID = DL.RecID)
        WHERE (DL.enddate < TT.enddate)
    )
    SELECT *
    FROM DateList
    ORDER BY RecID, StartDate

    -- NON-CTE SOLUTION
    -------------------
    DECLARE @TimePeriods table
    (
       TimeDiff int
    )

    INSERT INTO @TimePeriods Values ( 5 )
    INSERT INTO @TimePeriods Values ( 10 )
    INSERT INTO @TimePeriods Values ( 15 )
    INSERT INTO @TimePeriods Values ( 20 )
    INSERT INTO @TimePeriods Values ( 25 )
    INSERT INTO @TimePeriods Values ( 30 )
    INSERT INTO @TimePeriods Values ( 35 )
    INSERT INTO @TimePeriods Values ( 40 )
    INSERT INTO @TimePeriods Values ( 45 )
    INSERT INTO @TimePeriods Values ( 50 )
    INSERT INTO @TimePeriods Values ( 55 )
    INSERT INTO @TimePeriods Values ( 60 )

    -- Multi join to Time Periods table
    SELECT A.RecID,
        StartDate = DATEADD(mi, B.TimeDiff - 5, A.StartDate),
        EndDate = DATEADD(mi, B.TimeDiff, A.StartDate)
    FROM (
            SELECT RecID, StartDate, PeriodLen = DATEDIFF(mi, StartDate, EndDate)
            FROM @TestTable
        ) A
        INNER JOIN @TimePeriods B
            ON (A.PeriodLen >= B.TimeDiff)
    ORDER BY A.RecID, B.TimeDiff

     

    The CTE expression will as the sample shows deal with period that are not an exact multiple of 5 minutes long.

     

    The non-cte version requires a table with a record for every increment of 5 minutes that a period can have (if they can be up to 4 hours long you need 5 to 240 in increments of 5 in it).  This is used to generate the records.  Remove the yellow marked lines (between the two comments) to get the non-cte version to work on SQL Server 2000

     

    Monday, April 2, 2007 3:46 PM
  • Ooops.

     

    Here's a modified version of the non-CTE query which will also deal with non-5 minute multiple intervals

     


    Code Snippet
    -- Multi join to Time Periods table
    SELECT A.RecID,
        StartDate = DATEADD(mi, B.TimeDiff - 5, A.StartDate),
        EndDate = CASE
            WHEN DATEADD(mi, B.TimeDiff, A.StartDate) <= A.EndDate
                THEN DATEADD(mi, B.TimeDiff, A.StartDate)
            ELSE
                A.EndDate
        END
    FROM (
            SELECT RecID, StartDate, EndDate,
                PeriodLen = DATEDIFF(mi, StartDate, EndDate)
            FROM @TestTable
        ) A
        INNER JOIN @TimePeriods B
            ON ((A.PeriodLen + 5) > B.TimeDiff)
    ORDER BY A.RecID, B.TimeDiff

     

    Didn't occur to me to update it to do that until after I had posted the other note.

     

    The queries do work with intervals which do not start on 5 minute boundaries (but the increments do not either).  It will have problems if the either there are two records with the same ID or the start time is later than the end time.

     

    If the start and end time are the same then the CTE will put one record in like that and the non-CTE version will put no record in.

    Monday, April 2, 2007 3:56 PM
  • Maybe a better approach in this case is to establish (1) a calendar table and (2) an interval table.  Information related to a calendar table can be found here:

     

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    I put together a mockup of a query using both an calendar and an interval table.  With this mockup I used a SMALL_ITERATOR table to populate these tables.  A definition of this table can be found here:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

     

    Here are the CALENDAR and INVERVAL tables that I used in the mockup:

     

    create table dbo.interval
       (    rid             int       not null
              constraint pk_interval primary key,
            fromTime        datetime,
            toTime          datetime
       )
    go

     

    insert into interval
    select iter,
           dateadd (mi, 5*(iter-1), ''),
           dateadd (mi, 5*iter,     '')
      from small_iterator (nolock)
    where iter <= 288

     

    create table dbo.calendar
       (    calDate         datetime
              constraint pk_calendar primary key
       )
    go

    insert into dbo.calendar
    select cast('12/31/2006' as datetime) + iter
      from small_iterator (nolock)
    where iter <= 365

    The query I used to test with is:

     

    declare @intervalTest table
       (    rid             int,
            loDateTime      datetime,
            hiDateTime      datetime
       )

    insert into @intervalTest
    select 1,    '01/01/2007 09:00:00',     '01/01/2007 09:10:00' union all
    select 2,    '01/01/2007 09:15:00',     '01/01/2007 09:25:00' union all
    select 3,    '02/01/2007 12:00:00',     '02/02/2007 12:30:00' union all
    select 4,    '02/03/2007 10:09:00',     '02/03/2007 10:16:00'

     

    select a.rid,
           calDate + fromTime as [From date/time],
           calDate + toTime as [To date/time]
      from @intervalTest a
     inner join calendar b
        on cast(floor(cast(a.loDateTime as float)) as datetime) <= b.calDate
       and a.hiDateTime >  b.calDate
     inner join interval c
        on b.calDate + fromTime >= loDateTime
       and b.calDate + fromTime <  hiDateTime

     

    /*
    rid    From date/time             To date/time 
    ------ -------------------------- -----------------------
    1      2007-01-01 09:00:00.000    2007-01-01 09:05:00.000
    1      2007-01-01 09:05:00.000    2007-01-01 09:10:00.000
    2      2007-01-01 09:15:00.000    2007-01-01 09:20:00.000
    2      2007-01-01 09:20:00.000    2007-01-01 09:25:00.000
    ...
    3      2007-02-02 12:25:00.000    2007-02-02 12:30:00.000
    4      2007-02-03 10:10:00.000    2007-02-03 10:15:00.000
    4      2007-02-03 10:15:00.000    2007-02-03 10:20:00.000
    */

     

    Monday, April 2, 2007 4:14 PM

All replies

  • Patrick:

     

    Here is a post in which they look at 30 minute intervals.  Note that the interval period is a variable so you should be able to readily change this to 5-minute intervals:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81913&SiteID=1

     

     Looking at it more closely you will probably need more than this.

    Monday, April 2, 2007 2:57 PM
  • Hi

     

    Here are a couple of solutions.  You can only use the common table expression solution if you have SQL Server 2005.


    Code Snippet
    DECLARE @TestTable table
    (
       RecID int,
       StartDate datetime,
       EndDate datetime
    )

    SET NOCOUNT ON
     
    INSERT INTO @TestTable Values ( 1, '2007-01-01 09:00:00', '2007-01-01 09:10:00' )
    INSERT INTO @TestTable Values ( 2, '2007-01-01 09:15:00', '2007-01-01 09:25:00' )
    INSERT INTO @TestTable Values ( 3, '2007-01-02 12:00:00', '2007-01-02 12:30:00' )
    INSERT INTO @TestTable Values ( 4, '2007-01-03 06:00:00', '2007-01-03 06:12:00' )

    -- USING A RECURSIVE COMMON TABLE EXPRESSION (CTE)
    --------------------------------------------------
    ;WITH DateList (RecID, StartDate, EndDate) AS
    (
        SELECT RecID, StartDate,
            CASE
                WHEN (DATEADD(mi, 5, startdate) <= enddate) THEN
                    DATEADD(mi, 5, startdate)
                ELSE EndDate
            END
        FROM @TestTable
    UNION ALL
        SELECT TT.RecID, DL.EndDate,
            CASE
                WHEN (DATEADD(mi, 5, DL.enddate) <= TT.enddate) THEN
                    DATEADD(mi, 5, DL.enddate)
                ELSE TT.EndDate
            END
        FROM @TestTable TT
            INNER JOIN DateList DL
                ON (TT.RecID = DL.RecID)
        WHERE (DL.enddate < TT.enddate)
    )
    SELECT *
    FROM DateList
    ORDER BY RecID, StartDate

    -- NON-CTE SOLUTION
    -------------------
    DECLARE @TimePeriods table
    (
       TimeDiff int
    )

    INSERT INTO @TimePeriods Values ( 5 )
    INSERT INTO @TimePeriods Values ( 10 )
    INSERT INTO @TimePeriods Values ( 15 )
    INSERT INTO @TimePeriods Values ( 20 )
    INSERT INTO @TimePeriods Values ( 25 )
    INSERT INTO @TimePeriods Values ( 30 )
    INSERT INTO @TimePeriods Values ( 35 )
    INSERT INTO @TimePeriods Values ( 40 )
    INSERT INTO @TimePeriods Values ( 45 )
    INSERT INTO @TimePeriods Values ( 50 )
    INSERT INTO @TimePeriods Values ( 55 )
    INSERT INTO @TimePeriods Values ( 60 )

    -- Multi join to Time Periods table
    SELECT A.RecID,
        StartDate = DATEADD(mi, B.TimeDiff - 5, A.StartDate),
        EndDate = DATEADD(mi, B.TimeDiff, A.StartDate)
    FROM (
            SELECT RecID, StartDate, PeriodLen = DATEDIFF(mi, StartDate, EndDate)
            FROM @TestTable
        ) A
        INNER JOIN @TimePeriods B
            ON (A.PeriodLen >= B.TimeDiff)
    ORDER BY A.RecID, B.TimeDiff

     

    The CTE expression will as the sample shows deal with period that are not an exact multiple of 5 minutes long.

     

    The non-cte version requires a table with a record for every increment of 5 minutes that a period can have (if they can be up to 4 hours long you need 5 to 240 in increments of 5 in it).  This is used to generate the records.  Remove the yellow marked lines (between the two comments) to get the non-cte version to work on SQL Server 2000

     

    Monday, April 2, 2007 3:46 PM
  • Ooops.

     

    Here's a modified version of the non-CTE query which will also deal with non-5 minute multiple intervals

     


    Code Snippet
    -- Multi join to Time Periods table
    SELECT A.RecID,
        StartDate = DATEADD(mi, B.TimeDiff - 5, A.StartDate),
        EndDate = CASE
            WHEN DATEADD(mi, B.TimeDiff, A.StartDate) <= A.EndDate
                THEN DATEADD(mi, B.TimeDiff, A.StartDate)
            ELSE
                A.EndDate
        END
    FROM (
            SELECT RecID, StartDate, EndDate,
                PeriodLen = DATEDIFF(mi, StartDate, EndDate)
            FROM @TestTable
        ) A
        INNER JOIN @TimePeriods B
            ON ((A.PeriodLen + 5) > B.TimeDiff)
    ORDER BY A.RecID, B.TimeDiff

     

    Didn't occur to me to update it to do that until after I had posted the other note.

     

    The queries do work with intervals which do not start on 5 minute boundaries (but the increments do not either).  It will have problems if the either there are two records with the same ID or the start time is later than the end time.

     

    If the start and end time are the same then the CTE will put one record in like that and the non-CTE version will put no record in.

    Monday, April 2, 2007 3:56 PM
  • Maybe a better approach in this case is to establish (1) a calendar table and (2) an interval table.  Information related to a calendar table can be found here:

     

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    I put together a mockup of a query using both an calendar and an interval table.  With this mockup I used a SMALL_ITERATOR table to populate these tables.  A definition of this table can be found here:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

     

    Here are the CALENDAR and INVERVAL tables that I used in the mockup:

     

    create table dbo.interval
       (    rid             int       not null
              constraint pk_interval primary key,
            fromTime        datetime,
            toTime          datetime
       )
    go

     

    insert into interval
    select iter,
           dateadd (mi, 5*(iter-1), ''),
           dateadd (mi, 5*iter,     '')
      from small_iterator (nolock)
    where iter <= 288

     

    create table dbo.calendar
       (    calDate         datetime
              constraint pk_calendar primary key
       )
    go

    insert into dbo.calendar
    select cast('12/31/2006' as datetime) + iter
      from small_iterator (nolock)
    where iter <= 365

    The query I used to test with is:

     

    declare @intervalTest table
       (    rid             int,
            loDateTime      datetime,
            hiDateTime      datetime
       )

    insert into @intervalTest
    select 1,    '01/01/2007 09:00:00',     '01/01/2007 09:10:00' union all
    select 2,    '01/01/2007 09:15:00',     '01/01/2007 09:25:00' union all
    select 3,    '02/01/2007 12:00:00',     '02/02/2007 12:30:00' union all
    select 4,    '02/03/2007 10:09:00',     '02/03/2007 10:16:00'

     

    select a.rid,
           calDate + fromTime as [From date/time],
           calDate + toTime as [To date/time]
      from @intervalTest a
     inner join calendar b
        on cast(floor(cast(a.loDateTime as float)) as datetime) <= b.calDate
       and a.hiDateTime >  b.calDate
     inner join interval c
        on b.calDate + fromTime >= loDateTime
       and b.calDate + fromTime <  hiDateTime

     

    /*
    rid    From date/time             To date/time 
    ------ -------------------------- -----------------------
    1      2007-01-01 09:00:00.000    2007-01-01 09:05:00.000
    1      2007-01-01 09:05:00.000    2007-01-01 09:10:00.000
    2      2007-01-01 09:15:00.000    2007-01-01 09:20:00.000
    2      2007-01-01 09:20:00.000    2007-01-01 09:25:00.000
    ...
    3      2007-02-02 12:25:00.000    2007-02-02 12:30:00.000
    4      2007-02-03 10:10:00.000    2007-02-03 10:15:00.000
    4      2007-02-03 10:15:00.000    2007-02-03 10:20:00.000
    */

     

    Monday, April 2, 2007 4:14 PM
  • Thanx very much for the replies. I was ill for a week so I couldn't post my thank you's any earlier, but the post helped me very well.

     

    Greetings,

     

    Patrick

    Tuesday, April 10, 2007 9:44 AM
  • Hi,

     

    The solution provided by Kent worked, except for one thing. If for example a time period is from:

     

    01/01/2007 21:56 until 01/01/2007 23:05 all intervals are created perfect except for the first one. The table then looks as follows:

     

    01/01/2007 22:00 - 01/01/2007 22:05

     

    I would like to have as the first interval the following one => 01/01/2007 21:55 - 01/01/2007 22:00

     

    Could someone help me solve this please. Again sorry for the late feedback but I was ill for a week.

     

    Greetings,

     

    Patrick

    Wednesday, April 11, 2007 9:57 AM
  • I already solved it myselve.

     

    Greetings,

     

    Patrick

    Wednesday, April 11, 2007 1:50 PM