我有一个插入存储过程,其中传递了 GETDATE()
作为其中一个值,因为每个插入操作也会保存其插入时间。该存储过程托管在 SQL Azure 上,使用的是 GMT 时区。
现在,当我接收消息时,它们的时间戳列中都存储了 GMT 时间,我该如何将其转换为访问页面时所在地的本地 datetime
?
谢谢。
我有一个插入存储过程,其中传递了 GETDATE()
作为其中一个值,因为每个插入操作也会保存其插入时间。该存储过程托管在 SQL Azure 上,使用的是 GMT 时区。
现在,当我接收消息时,它们的时间戳列中都存储了 GMT 时间,我该如何将其转换为访问页面时所在地的本地 datetime
?
谢谢。
declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'
declare @LocalDateTime datetime2 = dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), @InputUtcDateTime)
print @LocalDateTime
或者declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'
declare @LocalDateTime datetime2 = dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), @InputUtcDateTime)
print @LocalDateTime
除了夏令时问题外,为什么不采用以下简化方式:
```yourDateTime - getutcdate() + getdate()
CREATE FUNCTION [dbo].[fn_DateTime_GMTFromLocal](@LocalTime DATETIME)
RETURNS DATETIME2(3)
AS
BEGIN
/*
=============================================
Author: Mark Griffiths
Create date: 29/05/2018
Description: BST runs from 02:00AM on the last Sunday of March to the same time on the last Sunday of October.
The Series of DATEDIFFs and DATEADDS below function as follows
1 ● Count the number of months there have been between the given date and start of computer time
2 ● Add that number of months to the end of the first month to get the end of the given month
3 ● Count the number of days there have been between the end of the given month and the first Saturday
4 ● Add that number of days to the calculated end of the given month
5 ● Add Two hours to that time as the clocks go back at 02:00 in the morning
I know that the tabbing below makes it all look odd, but the description above is the best way I could find to comment things, given the nesting...
The comments in the code below should help find the nesting levels and the numbers refer to the bullet points above.
=============================================
-- Test Variables --
DECLARE @GMTime DATETIME2(3) = '2018-05-01 12:00:00.000'
*/
DECLARE @RealTime As DATETIME2(3)
DECLARE @Year VARCHAR(4)
SET @Year = CONVERT(VARCHAR,DATEPART(YEAR,@GMTime))
DECLARE @StartOfBST AS DATETIME
DECLARE @EndOfBST AS DATETIME
SELECT
@StartOfBST =
DATEADD -----------------------------------------------------------------------------------------
( -- |
HOUR -- |
,2 -- |
,DATEADD ----------------------------------------------------------------------------- |
( -- | |
DAY -- | |
,DATEDIFF ------------------------------------------------------------- | |
( -- | | |
DAY -- | | |
,'19000107' -- | | 5
,DATEADD --------------------------------------------- | | |
( -- | 3 4 |
MONTH -- | | | |
,DATEDIFF(MONTH,0,CONVERT(DATE,'03/01/' + @Year)) -- 1 2 | | |
,CONVERT(DATE,'01/31/1900') -- | | | |
) --------------------------------------------- | | |
)/7*7 ------------------------------------------------------------- | |
,'19000107' -- | |
) --------------------------------------------------------------------- |
), -----------------------------------------------------------------------------------------
@EndOfBST =
DATEADD(HOUR,2,DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,CONVERT(DATE,'10/01/' + @Year)),30))/7*7,'19000107'))
SET @RealTime = CASE
WHEN @GMTime BETWEEN @StartOfBST AND @EndOfBST THEN DATEADD(HOUR,-1,@GMTime)
ELSE @GMTime
END
RETURN @RealTime;
--SELECT @RealTime
END
*编辑:将CONVERT(DATE,'01/30/1900')
更改为CONVERT(DATE,'01/31/1900')
,因为自公元前45年以来,1月份有31天。这会导致2019年和其他一些3月最后一个星期日是31号的日期计算出现错误。
+-1
不同。顺便说一下,让我惊奇的是被接受的答案(并且还被点赞)实际上是不起作用的! - High Plains GrifterSWITCHOFFSET(CONVERT(DATETIMEOFFSET, [ColumnName]), '-07:00')
现在,如果您的本地日期/时间与GMT/UTC不同,您可能想使用以下内容...
SWITCHOFFSET(TODATETIMEOFFSET([ColumnName], datepart(tz,sysdatetimeoffset())),'+00:00')
以下是细节说明。
SWITCHOFFSET
- 将DateTimeOffset值转换为不同的时区,同时保留偏移量。TODATETIMEOFFSET
- 将DateTime值转换为指定时区的DateTimeOffset值。DATEPART
- 在这种情况下,获取本地日期时间的时区部分。'+00:00'
- 目标偏移量,在第二个示例中是UTC/GMT目标,从本地...前一个示例是到MST。注意/警告:我认为这并没有考虑夏令时,这可能会对您造成影响。如果绝对保留不是必要的,您可以添加一个辅助列,进行粗略转换并安全地继续。
您可能希望将逻辑抽象成函数调用,以便考虑DST的保留...尽管这不应该过于困难。
这是一个处理历史数据的函数。我为英国夏令时编写了它,不幸的是英国夏令时发生在三月和十月的最后一个星期日,使得逻辑有点复杂。
基本上硬编码的日期部分01/03是在寻找三月的最后一个星期日,01/10是在寻找十月的最后一个星期日(这是当地时钟向前或向后调整的时间)。注意:如果您的服务器使用本地美国日期,请将这两个日期部分反转为03/01和10/01!!!
因此,您可以输入一个UTC日期,它会自动计算出历史日期是BST还是GMT。虽然不是在大数据集上使用的最佳方案,但它是一种解决方案。
运行此脚本以创建函数并在您的选择中内联调用它。SQL 2008存在用户定义函数的问题,似乎会在代码下面放置一个红线,但只要您使用dbo前缀(SELECT dbo.UTCConvert(yourdate)来运行它),它仍然会运行。
CREATE FUNCTION [dbo].[UTCConvert]
(
@p1 datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @Result datetime
RETURN CASE
WHEN
@p1 >
(DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,'01/03/' + CAST(DATEPART(year,@p1) as CHAR)),30))/7*7,'19000107'))
AND
@p1<
(DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,'01/10/' + CAST(DATEPART(year,@p1) as CHAR)),30))/7*7,'19000107'))
THEN (DATEADD(HH, 1, @p1))
ELSE @p1
END
END