SQL Server 按时间间隔提取数据进行数据采样

其中30为取样时间间隔，按datediff使用的定义时间计算。

 

Select CollectNum,CollectDate,CollectTime,t.interval FROM 
(SELECT CollectNum,CollectDate,CollectTime,
datediff (mi,cast(Cast(A.CollectDate AS VARCHAR)+' ' + Cast(A.CollectTime AS VARCHAR) AS DATETIME), GETDATE()) AS interval 
FROM Analysis.dbo.OnlineRoom A Where YEAR(CollectDate)=2010
)
AS t WHERE t.interval%30 = 0 
ORDER BY t.interval Desc;