Grouping Time Stamp using T-SQL

  • With cte_time

    As

    (Select 100 As event_id, '2012-11-29 06:00:00' As event_ts

    UNION ALL

    Select 200 As event_id, '2012-11-29 06:01:22' As event_ts

    UNION ALL

    Select 300 As event_id, '2012-11-29 06:01:49' As event_ts

    UNION ALL

    Select 400 As event_id, '2012-11-29 06:02:07' As event_ts

    UNION ALL

    Select 500 As event_id, '2012-11-29 06:02:33' As event_ts

    UNION ALL

    Select 600 As event_id, '2012-11-29 06:02:34' As event_ts

    UNION ALL

    Select 700 As event_id, '2012-11-29 06:04:11' As event_ts

    UNION ALL

    Select 800 As event_id, '2012-11-29 06:05:19' As event_ts

    UNION ALL

    Select 900 As event_id, '2012-11-29 06:06:30' As event_ts

    UNION ALL

    Select 1000 As event_id, '2012-11-29 06:07:47' As event_ts

    UNION ALL

    Select 1100 As event_id, '2012-11-29 06:08:59' As event_ts

    )

    Select event_id, event_ts, 'x' As time_stamp_group

    From cte_time

    I want the 'x' to be replaced by a 5 minute grouping so that my final result looks like:

    event_idevent_tstime_stamp_group

    ----------------------------------------------

    1002012-11-29 06:00:002012-11-29 06:05:00

    2002012-11-29 06:01:222012-11-29 06:05:00

    3002012-11-29 06:01:492012-11-29 06:05:00

    4002012-11-29 06:02:072012-11-29 06:05:00

    5002012-11-29 06:02:332012-11-29 06:05:00

    6002012-11-29 06:02:342012-11-29 06:05:00

    7002012-11-29 06:04:112012-11-29 06:05:00

    8002012-11-29 06:05:192012-11-29 06:10:00

    9002012-11-29 06:06:302012-11-29 06:10:00

    10002012-11-29 06:07:472012-11-29 06:10:00

    11002012-11-29 06:08:592012-11-29 06:10:00

    How can this be done using t-sql?

  • Try this

    Select event_id, event_ts, DATEADD(minute,(1+DATEDIFF(minute, 0, event_ts)/5)*5, 0) AS time_stamp_group

    From cte_time

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply