CODEWITHSUNDEEP
sqlsql-server-2012
Scart1000
Scart1000

Reputation: 55

Grouping a time field into 5 minute periods in SQL server

I have a column of times and want to create a second column to show which 5 minute interval on a 24 hr clock they fall into. For example

15:19:52 becomes 15:15:00
15:20:11 becomes 15:20:00

Upvotes: 4

Views: 193

Answers (3)

Royi Namir
Royi Namir

Reputation: 148644

Another simplified solution is to create time line and find your exact location : 

DECLARE @t TABLE (b  TIME)


INSERT INTO @t
VALUES   (  '14:16') ,( '15:19:52') ,('15:20:11')


;
WITH cte AS (
         SELECT CAST ('00:00'AS TIME) AS a
         UNION ALL
         SELECT DATEADD(MINUTE, 5, a) from cte
         WHERE  cte.a   <  CAST ('23:54:00' AS TIME)
     )
SELECT * FROM @t CROSS APPLY(SELECT TOP 1 a FROM cte WHERE  a<=b ORDER BY
       a         DESC) k  option (maxrecursion 0)




b   a
14:16:00.0000000    14:15:00.0000000
15:19:52.0000000    15:15:00.0000000
15:20:11.0000000    15:20:00.0000000

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

You can do the following. It builds the time

SELECT TIMEFROMPARTS(
         DATEPART(HOUR, yourTimeField),
         DATEPART(MINUTE, yourTimeField) / 5 * 5, 0,
         0,
         0)
FROM yourTable

Link to SQL Fiddle Example

Upvotes: 5

PWilliams0530
PWilliams0530

Reputation: 170

There is a thread I found that may help you begin getting started with this.

T-SQL: Round to nearest 15 minute interval

However, all the examples here only seem to round up, so you'd need to subtract 5 from the result to get what appears to be what you are seeking.

Upvotes: 1

Related Questions