How to Group Time Intervals (Hourly or 10-Minute) in SQL Server?

Time interval grouping in SQL Server

In data analysis, grouping data by time intervals is critical to identifying patterns and trends. This article explores how to implement time grouping in Microsoft SQL Server 2008, explaining how to group by hour or 10 minutes.

Group by hour or 10 minutes

To group times by hours, use the DATEPART(HOUR, [Date]) function. For a 10-minute interval, use (DATEPART(MINUTE, [Date]) / 10). The following modified query groups data by hour:

<code class="language-sql">SELECT MIN([Date]) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY DATEPART(HOUR, [Date])
ORDER BY RecT</code>

For 10 minute intervals, use:

<code class="language-sql">SELECT MIN([Date]) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY (DATEPART(MINUTE, [Date]) / 10)
ORDER BY RecT</code>

Remove milliseconds from date output

To exclude milliseconds from the output, use the DATEPART function again:

<code class="language-sql">SELECT MIN(DATEPART(HOUR, [Date])) AS RecT, AVG(Value)
FROM [FRIIB].[dbo].[ArchiveAnalog]
GROUP BY YEAR([Date]), MONTH([Date]), DAY([Date]), HOUR([Date])
ORDER BY RecT</code>

In this query, the DATEPART function is used on each date component to remove milliseconds.

The above is the detailed content of How to Group Time Intervals (Hourly or 10-Minute) in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!