按日期时间分组忽略时间部分 [英] Group by datetime ignoring time portion

问题描述

是否可以编写一个Microsoft SQL查询，该查询将由 datetime 数据类型分组，但忽略时间部分，如小时和分钟？

Is it possible to write a Microsoft SQL query that will group by a datetime data-type but ignoring the time part such as the hour and minute?

推荐答案

如果您在SQL Server 2008上，这很简单。

If you are on SQL Server 2008 this is simple.

GROUP BY CAST(YourCol AS Date)

对于以前的版本，您可以使用

For previous versions you can use

GROUP BY DATEDIFF(DAY, 0, YourCol)

但是，这两个都不能够利用 YourCol 的索引，由 datetime 也将由 date 订购，因此使用流汇总而不进行排序操作。

However neither of these will be able to leverage the fact that an index on YourCol that is ordered by datetime will also be ordered by date and thus use a stream aggregate without a sort operation.

在SQL Server 2008+上，您可能会考虑将（日期，时间）而不是 datetime 查询的类型。

On SQL Server 2008+ you might consider indexing (date,time) rather than datetime to facilitate this type of query.

通过简单地将其存储为两个单独的组件，并可能提供计算列mbines部分（一个 datetime2 与日期和时间，所以这不会消耗任何更多的空间，除了如果附加列将 NULL_BITMAP 推送到一个新的字节。）。

Either by simply storing it as two separate components and possibly providing a calculated column that recombines the parts (a datetime2 is stored the same as a date and a time so this won't consume any more space except for if the additional column pushes the NULL_BITMAP onto a new byte.).

CREATE TABLE T
(
YourDateCol date,
YourTimeCol time,
YourDateTimeCol AS  DATEADD(day, 
                            DATEDIFF(DAY,0,YourDateCol), 
                            CAST(YourTimeCol AS DATETIME2(7)))
/*Other Columns*/
)

或者，您可以将其存储在基表中，并将索引使用计算的列拆分出来。

Or alternatively you could store it combined in the base table and have the indexes use calculated columns that split it out.

这种方法的一个例子

CREATE TABLE T
(
DT DATETIME2,
D AS CAST(DT AS DATE),
T AS CAST(DT AS TIME)
)

CREATE INDEX IX1 ON T(DT);

SELECT COUNT(*), 
       CAST(DT AS DATE)
FROM T
GROUP BY CAST(DT AS DATE)

CREATE INDEX IX2 ON T(D,T);

SELECT COUNT(*), 
       CAST(DT AS DATE)
FROM T
GROUP BY CAST(DT AS DATE)

DROP TABLE T

这篇关于按日期时间分组忽略时间部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！