0

Converting Historical Database DateTime Records to UTC or GMT TimeZone While Accounting for DST

Emeka OkekeDec 13, 2019

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.

The Problem

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.

The Solution

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.

The Prevention

In order to avoid problems like this, database developers and architects should store date and time records in UTC/GMT and then convert it to the client?s timezone perhaps using JavaScript during UI rendering especially for applications that are sensitive to time and date records. For example for applications like HelpLeap storing dates and times in a specific timezone does not matter because it is not sensitive to time and date records or to DST shifts in time. It only needs to display post and comments' published dates.

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.

Emeka Okeke@sqldibia+ Follow
Kofi Author Dec 16, 2019
0

In our case, we're creating this datetime record afresh on our database. We don't already have it. Do we store this record on all client database?

Emeka Okeke Author Dec 16, 2019
0

I am not sure what you mean by "all client database". If you don't have existing database datetime records, then you should store your records in UTC/GMT timezone. Also if you are creating new datetime records, then you do not have this problem. See the "The Prevention" section of this post, so you don't get this problem in the future.

0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia