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.
Hi Roelant, it’s been so long since we have talked. This is an interesting post, we treat time zones as business rules in our dv. We store business dates in UTC, either in readable date if we need sub second grain or epoch time in seconds (# seconds since 1/1/1970). Then we have a SQL function to get dates out of the dv in whatever time zone we need. This way if time zone rules change (they did in the US in 2006) we just update our function. For example in the US we know that daylight savings time begins at 2:00 AM on the second Sunday in March and ends the first Sunday in November. So in our function we pass in the epoch time and a 3 digit time zone (ex. EST) and the function sets the offset (+4 or +5 for EST) based on the time zone rules. It works well for us but can cause overhead on very large datasets, if you work for a global company where you need data in many different time zones depending on report requester locations this works well.
Hi Doug – good to hear from you! Thanks for this solution, it’s a good way to handle shifts etc. because as you point out this information needs to come from somewhere (similar to what ‘at time zone’ would do).. Do you also store the offset when you store the business date, or the timezone in a different field? I was thinking the original info otherwise gets lost, which means you have to ‘know’ which values you need to add to the function to interpret the correct outcome.