Geek Answers Handbook

Grouping a temporary field in a 5-minute period on an SQL server

I have a column once and want to create a second column to show what the 5-minute interval is at the 24-hour time they fall into. for instance

15:19:52 becomes 15:15:00
15:20:11 becomes 15:20:00
+4
source share
3 answers

You can do the following. He creates time

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

Link to SQL Fiddle Example

+5
source

There is a thread that I found that can help you get started with this.

T-SQL: round to the nearest 15 minute interval

However, all the examples here are only rounded, so you need to subtract 5 from the result to get what seems to be what you are looking for.

+1
source

:

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
0
source

More articles:


All Articles