就像我做的那样

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date]

如何指定分组时段?我使用的是MS SQL 2008。

我已经试过了,使用% 10和/ 10。

SELECT MIN([Date]) AS RecT, AVG(Value)
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY (DATEPART(MINUTE, [Date]) / 10)
  ORDER BY RecT

是否有可能使日期输出没有毫秒?


当前回答

在SQLite中,为了按小时分组,你可以这样做:

GROUP BY strftime('%H', [FRIIB].[dbo].[ArchiveAnalog].[Date]);

并按每10分钟分组:

GROUP BY strftime('%M', [FRIIB].[dbo].[ArchiveAnalog].[Date]) / 10;

其他回答

终于讲完了

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

在T-SQL中,您可以:

SELECT [Date]
  FROM [FRIIB].[dbo].[ArchiveAnalog]
  GROUP BY [Date], DATEPART(hh, [Date])

or

Date (mi, [Date])

or

使用DATEPART(mi, [Date]) / 10(像Timothy建议的那样)

select dateadd(minute, datediff(minute, 0, Date), 0),
       sum(SnapShotValue)
FROM [FRIIB].[dbo].[ArchiveAnalog]
group by dateadd(minute, datediff(minute, 0, Date), 0)
declare @interval tinyint
set @interval = 30
select dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0), sum(Value_Transaction)
from Transactions
group by dateadd(minute,(datediff(minute,0,[DateInsert])/@interval)*@interval,0)

在SQLite中,为了按小时分组,你可以这样做:

GROUP BY strftime('%H', [FRIIB].[dbo].[ArchiveAnalog].[Date]);

并按每10分钟分组:

GROUP BY strftime('%M', [FRIIB].[dbo].[ArchiveAnalog].[Date]) / 10;