如何使用指定的时区按日期进行有效分组？

Question

我目前正在运行一个聚合查询，将给定日期的销售额(例如)进行汇总。

select convert(date, datetimesold), sum(amountsold) from tblSold 
group by convert(date, datetimesold)

其中datetimesold是一个datetime值。

convert(date,...)可以去掉时间值，这样group by就可以一整天进行分组。

这已经没有效率了，因为它需要对每一行的转换进行表格扫描--更好的方法是添加一个“datesold”列，该列只包含日期值，索引，每次插入时都包含此值。但这会失去列的准确性，这很重要，因为.

datetimesold是UTC的一个日期时间。所以我的问题是:假设我想在一天内分组，但在美国东部时间。在通过- group by convert(date, dateadd(hours, -5, datetimesold))在组中进行转换之前，我需要添加一个以小时为单位的日期偏移--但即使这样，由于夏时制--EDT-4小时，EST-5小时--这并不总是准确的。

我是否有任何有效的选项可以在SQL中这样做？有什么时区感知功能可以在这里使用吗？

编辑:为了进一步澄清，我在Azure SQL数据库上操作。

Answer 1

您已经注意到很难正确地将UTC转换为本地时区。事实上，这是非常困难的，因为日光节约的规则发生了变化。您需要维护一个时区的历史数据库才能正确地执行此操作。

我存储两个时间戳-在UTC和本地时区。在一些报告中，我们需要世界协调时，在一些地方。

当插入行时，通常很容易在UTC和本地时区之间进行转换，而生成数据的客户端计算机的操作系统处于正确的本地时区。那时OS知道本地和UTC的时间。但是，如果您有前几年的历史数据，那么执行这种转换就变得更加困难了。

Server 2016承诺添加对时区的更好支持，请参见：时区。

至于您对表扫描的关注--您必须始终扫描整个表来计算SUM，所以额外的CONVERT到date并不重要。

另一方面，

如果您有一个单独的列，它只存储date，而不是datetime，那么查询的效率会更高一些，因为date占用的字节比datetime少，所以从磁盘读取的字节更少。

如果在(datesold, amountsold)上添加索引，那么GROUP BY就不必执行额外的排序，这也会提高查询的效率。

因此，在当前版本的Server中，我将添加一个索引的date列，该列将包含报表所需的时区中的日期。如果在UTC和美国东部时区需要报告，我会添加两个单独的date列。

Answer 2

如果您可以以某种方式访问时区名称，则可以使用接受标准时区字符串的SQL函数的变体。

select
    date(date, @timezone_name) as dte, sum(amountsold) from tblSold 
    group by dte

其中@timezone_name是来自以下任何值：

select * from sys.time_zone_info 

(SQL 2016+)

Answer 3

尝试将值强制转换为组的日期类型。

组按铸(myDateTime为日期)

要管理时区，您可以使用.net api nodatime并使其具有CLR函数，这将有助于解决问题。

http://nodatime.org/