SQL Server 2014 – Daylight Saving Time Handling

datedatetimesql servert-sqltimezone

My computer/SQL Server 2014 uses Eastern Time Zone which is 5 hours behind UTC time. But when I run the following code, I get a 4-hour difference.

CREATE TABLE datetimeoffsetfunction(    
                                    servertime DATETIME2,
                                    utc_servertime DATETIME2,
                                    offset_datetime DATETIMEOFFSET,
                                    switch_offset DATETIMEOFFSET,
                                    to_datetime DATETIMEOFFSET
                                      );

INSERT INTO datetimeoffsetfunction
VALUES (  SYSDATETIME(), 
          SYSUTCDATETIME(), 
          SYSDATETIMEOFFSET(),
          SWITCHOFFSET(  SYSDATETIMEOFFSET(),  '+05:00'),
          TODATETIMEOFFSET( SYSDATETIME(), '+05:00')
          );

The result I got is 

servertime:         2016-07-09 23:59:16.4770556
utc_servertime:     2016-07-10 03:59:16.4770556
offset_datetime:    2016-07-09 23:59:16.4770556 -04:00
switch_offset:      2016-07-10 08:59:16.4770556 +05:00
to_datetime:        2016-07-09 23:59:16.4770556 +05:00

The servertime is my system time, and considering the daylight saving time, I can explain why there were only four hour difference between servertime and utc_servertime. But I did expect offset_datetime to be 2016-07-09 23:59:16.4770556 -05:00 unless SQL Serve now accounts for Daylight saving time.

So my question is Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

Best Answer

Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

No, SQL Server 2014 is not "DST-aware", nor is your test showing it to be.

First, your 4th and 5th tests -- SWITCHOFFSET( SYSDATETIMEOFFSET(), '+05:00') and TODATETIMEOFFSET( SYSDATETIME(), '+05:00') respectively -- are showing +05:00 because that is exactly what you told them to do.

Next, SYSDATETIMEOFFSET() is showing -04:00 because that offset is currently the true difference between your server's TimeZone (which does change, else it wouldn't show the correct time for approximately half of the year) and UTC, which doesn't change during the year. This, however, does indicate DST-awareness: it is the OS that is DST-aware, which is how the server's current time changes based on DST.

Being DST-aware means that the DST date boundaries for prior periods and for other TimeZones can be taken into account when switching TimeZones. And this requires knowledge of TimeZones. However, SQL Server (at least prior to SQL Server 2016) has no concept of TimeZones. Offsets are just that: a specific number of hours and minutes away from UTC. In contrast, a TimeZone is a rule that states a particular offset and if Daylight Saving is ever applied (some TimeZones do not participate in DST) and if DST is applied, what those date boundaries are (which are a matter of law and change over time, per each region).

Hence, being DST-aware means you have the ability to convert a date in the past to, or from, your current TimeZone, or any date between any two TimeZones, neither of which are the server's current TimeZone. Meaning, SQL Server being DST-aware would indicate that you could convert from:

UTC to Eastern Time (your server's current TimeZone), for any date in the year, for 20 years ago, and get the correct time, even though the DST date boundaries changed in the US during that time. But you can't since you can only calculate the current offset, which is -04:00 and have no way to determine when that offset would be -05:00.
Any non-Eastern TimeZone to any other non-Eastern TimeZone, for any date, and get the correct time. This is not possible because there is no chart of TimeZones that details which TimeZone is what offset for which dates, including historical shifts in those date boundaries, so that any particular date, of any particular year, will be adjusted correctly.

More info and some examples

As mentioned above, time zone offsets are not time zones: they are merely a property of a time zone. Meaning, the "US/Eastern" (a.k.a. "America/New_York") time zone (i.e. EST / EDT) is not identified as -04:00 any more than as -05:00; it is an area that has boundaries to the East and to the West, has a base offset of -05:00, and participates in Daylight Saving and so has an offset -- +01:00 -- and a specific start date and end date for when to apply that DST offset. You can check the info on TimeZoneDB.com.

The main database of this Time Zone info is maintained by the Internet Assigned Names Authority (IANA), and can be found at:

http://www.iana.org/time-zones

Time zone information is determined by individual governments. Some countries participate in DST, other do not. Some cities within various countries / states opt in or out of the parent regions rule. See the page for Daylight Saving Time Around the World 2016 on TimeAndDate.com for a chart the various start and end dates around the world (at least for 2016). And check out their page for Eastern Time (ET) for details including exceptions.

Also, whether or not a particular area observes DST, and if so, what the data boundaries are, changes over time. For example, in the United States, the DST start and end dates changed in late 2005, effective starting in 2007: US Daylight Saving Time Changed in 2005. Meaning, even if you know that the current rule is to apply the +01:00 offset to EST between March 13th and November 6th, then if you convert a DATETIME value of 2006-11-02 18:00:00 from UTC to ET, you would get 2006-11-02 14:00:00 which is wrong, because in 2006 DST ended on October 28th, making the correct Eastern Time be 2006-11-02 13:00:00.

Additional explanation in my DBA.StackExchange answer here: How to check the timezone for a given datetime?

How to handle DST (and converting between time zones) in SQL Server 2005 - 2014

The two main ways I know of to properly handle DST-awareness as well as convert between different time zones are:

Matt Johnson's SQL Server Time Zone Support project:

This is a pure T-SQL approach. Please see the ReadMe at the bottom of the main page linked above (i.e. the project name). Pay close attention to the ConvertZone and SwitchZone functions, as they convert between actual time zones, not mere offsets!

Please also note that it states:

Microsoft SQL Server 2008 R2 and higher are supported, including Azure SQL Database
SQLCLR:

Converting back and forth, between the server's local time zone and UTC is fairly simple using DateTime.ToLocalTime and DateTime.ToUniversalTime. This would work starting with SQL Server 2005.

Stay away from the TimeZoneclass that has methods of the same names, due to the following warning shown on the TimeZone.ToLocalTime page:

The ToLocalTime method recognizes only the current daylight saving time adjustment rule for the local time zone. As a result, it is guaranteed to accurately return the local time corresponding to a particular UTC time only during the period in which the latest adjustment rule is in effect. It may return inaccurate results if time is a historic date and time value that was subject to a previous adjustment rule.

Converting between time zones is a bit trickier. There is a built-in .NET class that handles this, BUT it requires the calling Assembly to be marked as UNSAFE do to the TimeZoneInfo class having a Host Protection Attribute of MayLeakOnAbort (i.e. memory leak). People have used this class, and it might be fine when not calling its methods terribly frequently. But I did see at least one case of someone reporting what appeared to be a memory-leak when running this across many (hundreds of thousands or millions) of rows.

Regarding the safe ToLocalTime and ToUniversalTime methods in the DateTime class: precompiled versions of these are available in the SQL# SQLCLR library that I created, but only in the Full version (not in the Free version). To show that these 2 methods do work, consider the situation mentioned above regarding the date boundaries change that occurred in the United States, starting in 2007:
```
SELECT SQL#.Date_ToLocalTime('2007-10-31 20:23:45') AS [CurrentRule],
       SQL#.Date_ToLocalTime('2006-10-31 20:23:45') AS [PriorRule];
```
Returns:
```
CurrentRule                PriorRule
2007-10-31 16:23:45.000    2006-10-31 15:23:45.000
```

Firstly...

Separating Datime/Time into a Date dimension and a Time dimension is definitely the way to go.

To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following:

LocalDateKey
LocalTimeKey
UtcDateKey
UtcTimeKey

You say...

The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.

By having the 4 columns I've listed above you, will be able to join the fact table to the Date and/or Time dimension Using Table Aliases (in Kimball terminology these aliased dimension tables are known as "Role Playing Dimensions"), so you would have something like the following:

/*
    Assumes the following:
        - [DateLongName] has the format of this example "Tuesday, December 31, 2013"
        - [TimeShortName] has the format of this example "11:00 PM"
        - Both [DateLongName] & [TimeShortName] are strings
*/
select
    -- Returns a string matching this example  "11:00 PM Tuesday, December 31, 2013"
    localTime.TimeShortName + ' ' + localDate.DateLongName
    ,utcTime.TimeShortName + ' ' + utcDate.DateLongName
    ,f.*
from
    FactTableName  AS f

    -- Local Date and Local Time joins          
    inner join dbo.Date  AS localDate
        on localDate.DateKey = f.LocalDateKey

    inner join dbo.Time  AS localTime
        on localTime.TimeKey = f.LocalTimeKey 

    -- Utc Date and Utc Time joins    
    inner join dbo.Date  AS utcDate
        on utcDate.DateKey = f.UtcDateKey

    inner join dbo.Time  AS utcTime
        on utcTime.TimeKey = f.UtcTimeKey

In closing...

As you're building a data mart, and not an OLTP database, the generation of the Local and Utc times should be performed in your ETL, NOT in any client side applications for the following reasons (apart from localisation of the UTC time to the report reader's perspective):

Having the calculation reside in any queries places an extra performance burden on them, multiplied by the number of times you have to run said query for any reports you have (this matters when reading millions of rows)
Extra burden of ensuring the calculation is maintained correctly in each query (especially when you take daylight savings time into account)
Prevent range scanning of any indexes the column is part of, as you'll be performing a calculation on the column which forces queries to perform index scans instead of seeks (which are typically more expensive as each data page is needed to be read); this is known as being non-sargable.
- Edit due to comments: This applies if you push the conversion down into the actual query.
Using the concept of having the additional UTC dates and times available, there is nothing stopping you from taking this concept and extending it by calling this StandardisedDateKey, or CorporateHQDateKey, where instead of a UTC date table you standardise based on some other business agreed standard
Having the two separate column types (Local and UTC), allows for side-by-side comparison across geographic distance. Think -> someone in Australia enters a record that is timestamped with both Local and UTC, someone in New York reads the report with the Local (Australia) date and time and the New York representation of the UTC date and time, thereby seeing that something their Australian counterpart did during the middle of the day (Australia time) happened in the middle of the night their time (New York time). This comparison of time is indispensable in multi-national businesses.

Sql-server – SQL Server and SharePoint time problem

SQL Server gets its time Zone information from the operating system on which it is installed. So if SELECT GETDATE() reveals the right information, then SQL Server is most likely not the problem.

You can query the registry on your SQL Server, if you have the appropriate permissions, and see what the server's time zone is set to:

    DECLARE @TimeZone NVARCHAR(255)

EXEC master.dbo.xp_instance_regread 

N'HKEY_LOCAL_MACHINE', 

N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

N'StandardName',

@TimeZone OUTPUT


SELECT @TimeZone

That should tell you what time zone your SQL Server thinks it is in.

I would suggest that the issue is either the client on which you are running the SharePoint application session - or more likely a setting in SharePoint itself or the server time zone on the SharePoint server itself.

You can check the regional settings for a site and see what it reports. This link should help get you started there.

Best Answer

Related Solutions

Sql-server – Handling time zones in data mart/warehouse

Firstly...

You say...

In closing...

Sql-server – SQL Server and SharePoint time problem

Related Question