时间间隔查询SQL

Question

我参考了这篇文章，但是我想按照15分钟的时间段计算所有行。

以下是我目前的内容：

SELECT      DateAdd(minute, DateDiff(minute, 0, [datetime]), 0) as Timestamp, 
            Count(*) as Tasks
FROM        [table]
GROUP BY    DateAdd(minute, DateDiff(minute, 0, [datetime]), 0)
ORDER BY    Timestamp

这对于每分钟获取行数非常有用，但是我需要15分钟...

所以我进行了更改：

DateAdd(minute, DateDiff(minute, 0, [datetime]), 15)

然而，这只是将日期向前推了15天。

任何帮助都将不胜感激！

- Hard Tacos

---

SQL Server数据类型Timestamp与时间无关，它是记录版本号。 - Suncat2000

Answer 1

要获得15分钟，先除以15（然后再乘回来）：

SELECT      DateAdd(minute, 15*(DateDiff(minute, 0, [datetime]) / 15), 0
                   ) as Timestamp, 
            Count(*) as Tasks
FROM        [table]
GROUP BY    (DateDiff(minute, 0, [datetime]) / 15)
ORDER BY    Timestamp;

SQL Server进行整数除法。如果您想明确表达您的意图，请使用FLOOR()函数。

- Gordon Linoff

---

非常好的解释。一旦允许我接受答案，我就会接受！谢谢！ - Hard Tacos

Answer 2

SELECT  ROUND(DATEDIFF(SECOND,{d '1970-01-01'},[datetime])/(15 * 60),0) as Timestamp, 
            Count(*) as Tasks
FROM   [table]
GROUP BY    ROUND(DATEDIFF(SECOND,{d '1970-01-01'},[datetime])/(15 * 60),0)
ORDER BY    Timestamp 

- Fuzzy

Answer 3

如果整数除法对您造成问题，这里有一个替代方案。它将日期时间转换为浮点数，然后使用floor()函数。

SELECT      convert(varchar,cast(round(floor(cast([datetime] as float(53))*24*4)/(24*4),5) as smalldatetime),108)  as Timestamp, 
            Count(*) as Tasks
FROM        [table]
GROUP BY    convert(varchar,cast(round(floor(cast([datetime] as float(53))*24*4)/(24*4),5) as smalldatetime),108)
ORDER BY    Timestamp

通常我会将(24*4)更改为96（一天中15分钟间隔的数量），但我想保留它，以便人们可以了解如何适应其他时间段。

- Greville Whittle