使用聚合功能将UTC时间转换为SQL Server 2014中的本地时间

Question

问题：需要查询从世界不同地区收集的大量时间序列数据以生成不同的报告。

时间序列表看起来像这样：LoggerId（INT），日期时间（日期时间）值（十进制）。

日期时间以UTC存储，但它们需要转换为当地时间。在某些情况下，数据需要使用AVG，SUM把事情像每天/每周/每月平均总等

用户可以更改在任何时候在他们的报告中使用的数据的时区进行汇总。例如，用户A可以决定在时区A中看到记录器＃1的数据，并且用户B可以选择在时区B中看到相同的数据。

到目前为止，由于夏令时，我无法在SQL Server 2014中找到一个好的解决方案这使事情变得复杂。我知道，在SQL Server二零一六年，是一种新的“在时区”功能，这可能是我需要什么，但它不提供2014年

任何帮助将不胜感激。

下面是一些示例数据：

LoggerId，DatetimeUTC，价值

1，2017年4月10日10:00:00，10

1，2017年4月10日11:00:00，20

1，2017年4月10日12:00:00，30

1，2017年4月10日13:00:00，40


从查询的预期结果，显示每日总记录器1的NZ时间（UTC + 12）：

LoggerId，DatetimeLocal，价值

1，2017年4月10日，30

1，2017年4月11日，70

Answer 1

对低于回DST功能的外观，你可以修改按您的要求。你只需传递utc日期时间和偏移量。 ：

Create FUNCTION [dbo].[UDTToLocalTime_Test](@UDT AS DATETIME,@offs as SMALLINT) 
RETURNS DATETIME 
AS 
BEGIN 
--==================================================== 
--Set the Timezone Offset (NOT During DST [Daylight Saving Time]) 
--==================================================== 
DECLARE @Offset AS SMALLINT 
SET @Offset = @offs 
--==================================================== 
--Figure out the Offset Datetime 
--==================================================== 
DECLARE @LocalDate AS DATETIME 
SET @LocalDate = DATEADD(hh, @Offset, @UDT) 
--==================================================== 
--Figure out the DST Offset for the UDT Datetime 
--==================================================== 
DECLARE @DaylightSavingOffset AS SMALLINT 
DECLARE @Year as SMALLINT 
DECLARE @DSTStartDate AS DATETIME 
DECLARE @DSTEndDate AS DATETIME 
--Get Year 
SET @Year = YEAR(@LocalDate) 
--Get First Possible DST StartDay 
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00' 
ELSE SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00' 
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate) 
--Get First Possible DST EndDate 
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00' 
ELSE SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00' 
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate) 
--Get DaylightSavingOffset 
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END 
--==================================================== 
--Finally add the DST Offset 
--==================================================== 
RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate) 
END 

只需要调用，如：

select [dbo].[UDTToLocalTime_Test](getutcdate(),+4) as DateSTim 

很久以前我就发现这个解决方案，并为我的情景做工精细希望它也将帮助你。

Answer 2

如果你不介意使用不安全的CLR组件在数据库中，那么你可以简单地写自己的.NET CLR标量函数来执行转换为您服务。以下是Dicko2的GitHub项目的链接。0演示了如何实现自己的CLR：

https://github.com/HostedSolutions/SQLDates

以下OneDrive链接包含完整的上述CLR项目安装脚本：

SQLDatesFunc

下面是对一个例子使用该组件：

DECLARE @utcDateTime DATETIME = GETUTCDATE(); 

DECLARE @cstDateTime DATETIME; 
DECLARE @estDateTime DATETIME; 

SET @cstDateTime = [dbo].[ConvertToLocalTimeZone]('Central Standard Time', @utcDateTime); 
SET @estDateTime = [dbo].[ConvertToLocalTimeZone]('Eastern Standard Time', @utcDateTime); 

SELECT @cstDateTime AS CST, @estDateTime AS EST;