如何正确地存储UTC datetime并使用本地偏移量?

I am storing data as UTC, but I am very confused on how to properly retrieve it. I store the value as UTC in a datetime column in MSSQL. Currently the way I retrieve it is by setting the GMT offset to the start datetime range and end datetime range. I am also needing to apply aggregation to that datetime field (group by) and display it in the user's proper local timezone. The issue I am having is say if I look for a range today it will pull up columns that match correctly, but the datetime value in those columns can be different UTC days; I am grouping by day. So for example when someone selects a range for the beginning of the day and the end (for today) they are presented with UTC datetime values from yesterday and today; but if I try to apply the GMT offset to the display value it still comes out as possibly 2 different dates. How can I properly coordinate the range and the display?

我将数据存储为UTC，但是我很困惑如何正确地检索它。我将值作为UTC存储在MSSQL中的datetime列中。目前我检索它的方法是将GMT偏移量设置为开始日期时间范围和结束日期时间范围。我还需要对datetime字段(group by)应用聚合，并在用户的适当的本地时区中显示它。我的问题是，如果我今天寻找一个范围它会拉出匹配正确的列，但是这些列中的datetime值可以是不同的UTC天;我按天分组。例如，当某个人选择了一天的开始和结束(今天)的范围时，他们会从昨天和今天得到UTC datetime值;但是如果我尝试将GMT偏移量应用到显示值，它仍然可能出现两个不同的日期。如何正确地协调范围和显示?

Thanks

谢谢

1 个解决方案

#1

Converting UTC format date to Standard time of CST or EST (for example) is straight forward. We have to store the offset value in a separate column or table for each timezone which we need in our application.

将UTC格式日期转换为CST或EST(例如)的标准时间是直接的。我们必须将偏移值存储在应用程序中需要的每个时区的单独列或表中。

For example: UTC to CST is -6 hrs. Similarly UTC to EST is -5 hrs.

例如:UTC到CST是-6小时。类似地，UTC到EST是-5小时。

DECLARE @UTC_Date DATETIME
SET @UTC_Date = GETUTCDATE()

SELECT
 @UTC_Date AS [UTC],
 DATEADD(hh, -6, @UTC_Date) AS [CST - Standard Time],
 DATEADD(hh, -5, @UTC_Date) AS [EST - Standard Time]

It gets complicated once we bring in Daylight saving into calculation. But if the given date falls under daylight saving then the above calculation won't work.

一旦我们引入夏令时，它就变得复杂了。但是，如果给定的日期属于夏令时，那么上述计算将不起作用。

So how is Daylight saving calculated?

那么日光节约是如何计算的呢?

If the year <= 2006 then daylight saving is between: 2 am on First Sunday in April till 2 am on Last Sunday in October

如果年份<= 2006年，那么夏令时在4月的第一个星期天凌晨2点到10月的上个星期天凌晨2点之间
If the year >= 2007 then daylight saving is between: 2 am on Second Sunday in March till 2 am on First Sunday in November

如果>= 2007年，那么夏令时在3月的第二个周日凌晨2点到11月的第一个周日凌晨2点之间
UTC to CST (Standard Time) = -6

UTC到CST(标准时间)= -6
UTC to CDT (Daylight Time) = -5

UTC到CDT(夏令时)= -5。
UTC to EST (Standard Time) = -5

UTC到EST(标准时间)= -5
UTC to EDT (DayLight Time) = -4
UTC到EDT(夏令时)= -4

Check out the solution for handling daylight saving here - http://vadivel.blogspot.com/2011/10/timezone-conversion-utc-to-cst-with.html

查看这里处理日光节约的解决方案——http://vadivel.blogspot.com/2011/10/timezone-conversion-utc-to-cst-with.html

#1