How Can I Group Time Intervals in MS SQL Server 2008?

MS SQL Server 2008 Time Interval Grouping

When working with time-based data, grouping records by time intervals such as hours or minutes is useful. In Microsoft SQL Server 2008, the GROUP BY clause provides a convenient way to do this.

The following code demonstrates how to group records by hour:

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

To group records by 10-minute intervals, use the following syntax:

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

However, this query returns datetime values ​​containing milliseconds. To exclude milliseconds, add the following code to your query:

<code class="language-sql">GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)</code>

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