Receiving and integrating data from multiple time zones
How to capture the date / time information that is received from different international locations, across different time zones, is a challenge that comes up from time to time. Recently I was involved in some conversations about this again, which prompted me to capture this once and for all and share this here.
As outlined in the pattern for Data Mart delivery you should be able to deliver information according to the timeline that the business user is familiar with. If transactions span multiple time zones this means that you need to capture all date / time information that is associated with the event separate from the time the information arrives in the Data Warehouse.
To accurately represent this information across time zones you will have to capture the local time including the UTC offset. But this is not yet the full solution. The time zones themselves change throughout the year and different rules can apply for specific time zones. For example daylight savings. The UTC offset is not fixed.
Due to these time zone shifts, this may mean that some transactions may appear ‘twice’ in the history, or even with one transaction overtaking the other ‘in time’ whereas in reality they happened subsequently. This is expected (correct) in the case where daylight savings adjustments are applied.
The only way to interpret this information is to include the time zone shift in your query, i.e. specifying if the reported time was before or after the time zone adjustment.
Depending on the platform this can be remediated in various ways. SQL Server 2016 for example now has a new way of addressing some of these challenges with the ‘AT TIME ZONE’, which also takes into account shifts like daylight savings (by and large – there are some exceptions).
I have updated the ‘All about Dates and Times‘ paper to incorporate a new section about handling time zones and shifts therein.