SQL Server made modifications in 2005 onward where the internal timezone is saved in UTC. This was largely due to geo-replication and HA projectors involving log shipping, and having the log shipping times saved in different time zones made it impossible for the old method to restore them.
Thus, saving everything internally in UTC time allowed SQL Server to work well globally. This is one of the reasons why daylight savings is kind of a pain to deal with in Windows, because other MS products such as Outlook also save the date/time internally as UTC and create a offset that needs to be patched.
I work in a company where we have thousands of servers (not MS SQL Servers though, but all kinds of servers) spread out all across the world, and if we didn't specifically force everything to go by UTC, we would all go insane very quickly.
UTC is generally not very meaningful to a user. You could theoretically convert the times to the end user’s local time zone.
A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field. This allows for very simple and efficient reporting in local or UTC time without the need to process the data for display in any way.
These are the two most common requirements -
1. local time for local reports and
2. UTC time for group reports.
The local time is stored in the DATETIME portion of the DATETIMEOFFSET and the OFFSET from UTC is stored in the OFFSET portion, thus conversion is simple and, since it requires no knowledge of the timezone the data came from, can all be done at database level.
If you don't require times down to milliseconds, e.g. just to minutes or seconds, you can use DATETIMEOFFSET(0). The DATETIMEOFFSET field will then only require 8 bytes of storage - the same as a DATETIME.
Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.
Thus, saving everything internally in UTC time allowed SQL Server to work well globally. This is one of the reasons why daylight savings is kind of a pain to deal with in Windows, because other MS products such as Outlook also save the date/time internally as UTC and create a offset that needs to be patched.
I work in a company where we have thousands of servers (not MS SQL Servers though, but all kinds of servers) spread out all across the world, and if we didn't specifically force everything to go by UTC, we would all go insane very quickly.
UTC is generally not very meaningful to a user. You could theoretically convert the times to the end user’s local time zone.
A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field. This allows for very simple and efficient reporting in local or UTC time without the need to process the data for display in any way.
These are the two most common requirements -
1. local time for local reports and
2. UTC time for group reports.
The local time is stored in the DATETIME portion of the DATETIMEOFFSET and the OFFSET from UTC is stored in the OFFSET portion, thus conversion is simple and, since it requires no knowledge of the timezone the data came from, can all be done at database level.
If you don't require times down to milliseconds, e.g. just to minutes or seconds, you can use DATETIMEOFFSET(0). The DATETIMEOFFSET field will then only require 8 bytes of storage - the same as a DATETIME.
Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.
Example:
SELECT t_odat As
'Order Date stored in SQL Server', CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
t_odat),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS 'Order Date In LN'
from ttdsls401101
No comments:
Post a Comment