How Can I Group Time Intervals (Hourly and 10-Minute) in SQL?

SQL time interval grouping (by hours and 10 minutes)

SQL's GROUP BY clause allows you to specify a grouping period to organize data into specific intervals. This article explains how to group data by hourly or 10-minute intervals.

Group by hour

Group by hour using the following syntax:

<code class="language-sql">GROUP BY DATEPART(HOUR, [Date])</code>

Group by 10-minute interval

Group by 10-minute intervals using the following syntax:

<code class="language-sql">GROUP BY (DATEPART(MINUTE, [Date]) / 10)</code>

Remove milliseconds from date output

Remove milliseconds from date output using the following syntax:

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

This query will group data by year, month, day, hour, and 10-minute intervals. The output will show the minimum date and mean for each interval, no milliseconds are shown in the date output.

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