How Can I Group Time Intervals in MS SQL 2008?

MS SQL 2008 Time Interval Grouping

When grouping time data in a SQL query, it is often useful to specify the desired grouping period. In MS SQL 2008 you can use DATEPART function for this purpose.

Group by hour, use the following code:

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

For 10 minute intervals, apply the following code:

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

To remove milliseconds from Date output, use the CAST function:

<code class="language-sql">GROUP BY CAST([Date] AS DATE)</code>

Here is a complete example, grouping data by 10 minute intervals and removing milliseconds:

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

The above is the detailed content of How Can I Group Time Intervals in MS SQL 2008?. For more information, please follow other related articles on the PHP Chinese website!