Grouping Time Stamp using T-SQL

• rka

  SSCommitted

  Points: 1599

  More actions

  November 30, 2012 at 6:06 am

  #265592

  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?

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  November 30, 2012 at 6:28 am

  #1564333

  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