This post will help you as database developer, architect, or tech lead avoid pitfalls of storing and managing datetime records in databases. It will also help you to fix issues that arise during on-premise OLTP SQL Server migration and integration with cloud database solutions from the likes of Microsoft SQL Azure and Salesforce.
A developer friend of mine recently came to me for help on a problem his team at work is trying to solve. The company is currently migrating its legacy system to Microsoft Azure. They hit a brick wall when they discovered that their current database that is storing date and time records in the local central time zone, which does not account for daylight saving time (DST), cannot be migrated to Azure SQL database, because Azure like most cloud databases store their date and time records in UTC/GMT.
The company is located in Tennessee. So one of the developers suggested adding 6 hours - which is the current time offset for CST zone - to all date and time records in the database. I told my friend that that will be disastrous because it does not account for DST shifts in time that occur twice each year - March and November.
I remembered that I already solved this problem during on-premise SQL Server database migration and integration with Salesforce project in San Francisco six years ago. So I searched my SQL code archives and luckily found the codes I developed for this purpose.
First of all you will have to create a table that holds the DST start date, end date, and offset value for each year starting from the minimum year and ending on the maximum year in the table that has the historical datetime records you want to convert. Here is the table I created with the DST start date, end date, and offset values for California PST/PDT timezone starting from year 2007 and ending this year 2019:
CREATE TABLE [SFDC].[GMT_DST](OFFSET INT,DST_START DATETIME, DST_END DATETIME) GO INSERT [SFDC].[GMT_DST](DST_START,DST_END,OFFSET) VALUES ('2007-03-11 02:00','2007-11-04 02:00',7), ('2007-11-04 02:00','2008-03-09 02:00',8), ('2008-03-09 02:00','2008-11-02 02:00',7), ('2008-11-02 02:00','2009-03-08 02:00',8), ('2009-03-08 02:00','2009-11-01 02:00',7), ('2009-11-01 02:00','2010-03-14 02:00',8), ('2010-03-14 02:00','2010-11-07 02:00',7), ('2010-11-07 02:00','2011-03-13 02:00',8), ('2011-03-13 02:00','2011-11-06 02:00',7), ('2011-11-06 02:00','2012-03-11 02:00',8), ('2012-03-11 02:00','2012-11-04 02:00',7), ('2012-11-04 02:00','2013-03-10 02:00',8), ('2013-03-10 02:00','2013-11-03 02:00',7), ('2013-11-03 02:00','2014-03-09 02:00',8), ('2014-03-09 02:00','2014-11-02 02:00',7), ('2014-11-02 02:00','2015-03-08 02:00',8), ('2015-03-08 02:00','2015-11-01 02:00',7), ('2015-11-01 02:00','2016-03-13 02:00',8), ('2016-03-13 02:00','2016-11-06 02:00',7), ('2016-11-06 02:00','2017-03-12 02:00',8), ('2017-03-12 02:00','2017-11-05 02:00',7), ('2017-11-05 02:00','2018-03-11 02:00',8), ('2018-03-11 02:00','2018-11-04 02:00',7), ('2018-11-04 02:00','2019-03-10 02:00',8), ('2019-03-10 02:00','2019-11-03 02:00',7)
You may need to add more dates by extending the minimum and/or the maximum dates. To get the DST start date, end date, and offset value, you can search for daylight saving time start and end 2020 on Google. Here is a sample code on how to use this table:
SELECT DATEADD(HOUR, (SELECT OFFSET FROM [SFDC].[GMT_DST] K WHERE B.ServerModifiedDate >= K.DST_START AND B.ServerModifiedDate < K.DST_END), B.ServerModifiedDate) AS ConvertedDate FROM TBL B
Here ServerModifiedDate is the datetime record you want to convert and TBL is the table that holds those records. So you should replace those values appropriately.
What this function does is to add the appropriate DST time offset value to the datetime records depending on which of the two months of March and November that DST shifted by an hour. DST adds an hour to the current timezone on November and then on March it is subtracted by an hour. So for the year 2019, it started at 02:00AM on March 10th and ended at 02:00AM on November 3rd.
Yea it isn't my fault, you can blame it on Benjamin Franklin. I heard somewhere that the whole idea of DST was his.
To properly store datetime records for applications that do need more accuracy in their time records, it is best to use the TSQL datatype
[datetimeoffset](7) for datetime storage. This TSQL datatype will store datetime record in UTC/GMT including the time offset value like for example
2018-01-26 14:30:00.0000000 +01:00. As you can see this also includes the offset value of
+01:00 which tells you the original timezone from which this UTC/GMT record was converted.
The 7 in the datatype indicates the milliseconds cut off for the datetime records. For example in the date sample given here, there are 7 zeros -
0000000. This gives you more control over the datetime's precision.
More from @sqldibia