Designing for both UTC & Local time at the same time

Other SQL Server Topics
1

I have a table that stores events from all around the world. The table has around a million rows and is queried several times a second 24 hours a day. In most cases the event is recorded in UTC but there are about 10% of the records that are in their local time zone. A column in the table identifies when the datetime is UTC and when it is local to the event's location.

I think the best way to handle the 2 types of datetimes might be to unify everything to use UTC and then base my queries on that. That would allow me to take advantage of a meaningful index on the unified column. But the downside is how do I then handle DST? Should I set a job up to run every so often to update the unified column's value and account for DST or is there a better way to handle this? I'm trying to avoid calculating a UTC value every time the query is called since it is called so frequently and the local time would only change twice a year (hopefully).

Any suggestions? Does anybody else have a similar situation that they have a solution for?

Thanks,
Jeff

2

I definitely agree on unifying on UTC and basing all your queries on that.

I'd say definitely do not modify the UTC time later, since it does not change.

Instead, keep an offset that has the different between UTC and local time. Use that offset to convert local time to UTC. Adjust the offset as needed (to reflect DST, if a place decides to quit using DTS, etc.). A single calc to add an offset won't hurt performance noticeably.

3

I think this would be a good place to use DATETIMEOFFSET and AT TIME ZONE.

Once you have the date/time stored in the unified column as a datetimeoffset, you should then have all the information you need to convert to/from local time as needed. AT TIME ZONE is DST aware so you don't have to worry about adjusting the offset.

Here is an example:

Declare @timeZoneName varchar(100) = ''; 

Execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
                            , 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
                            , 'TimeZoneKeyName'
                            , @timeZoneName Output;

 Select *
   From sys.time_zone_info              tzi
  Where tzi.name = @timeZoneName;

Declare @current_date datetimeoffset(7) = cast('2020-10-31 12:59:59.5555555' As datetimeoffset) At Time Zone @timeZoneName;

 Select @current_date
      , following_date = dateadd(day, 1, @current_date) At Time Zone @timeZoneName;

Here - we set our date/time to before DST - add 1 day and apply the time zone to get to the date/time after DST.

4

Have you tought of a computed column with PERSISTED? Then it will be calculated when the data is inserted or updated but not when it's selected.

5

The potential problem I see with that is that from time to time the DST status / setting changes. Then you'd have to go in and update actual values rather than just an offset amount. I think I'd still prefer to have the offset rather than a hard-computed value.

6

Why would you have to update anything? DATETIMEOFFSET is stored as UTC already - so no changes needed for any DST status/setting changes.

7

My initial design was using a calculated column but as Scott says below, the record would then need to be updated at least twice a year to force the calculation to recalculate for DST time changes. Along with that comes the issue of when to do the update since different regions start and end DST at different dates/times. A daily update job wouldn't be sufficient because then there would be a time period when the calculated time would be incorrect. There are some time zones that have weird offsets too like :30 or :15 so a job that touched all million rows to force a recalculation every 15 minutes seems a bit inefficient.

8

Let me add some context to the problem I'm trying to solve. This data involves airports all around the world. As an example: When a runway is closed (maintenance?) the airport will issue a notice with the UTC datetimes of when it is closed and when it gets reopened. However sometimes there are events that are recurring such as noise abatement laws where every evening aircraft takeoffs are affected between the hours of 10pm-6am. Recurring events like these are always in the airport's local time.

One part of flight planning involves knowing about these events so that the pilot doesn't mistakenly think he/she can land somewhere that is closed or maybe fly through an area during a time when there are additional restrictions in place.

The query I'm trying to support has to search on both types of datetimes (UTC & Local), sometimes at the same time. It also isn't just a single datetime calculation as there are actually 16 datetimes that I need to work with for a given query. My understanding is that if I'm relying on calculating a value each time then I incur a table scan each time the query runs as well because the query engine can't just seek to the correct records in an index. Remember that this table has about a million rows and this query is executed about 6-7 times a second. Those table scans get expensive really quick.

The airport example was just a single instance. I have 16 different types of situations that all have this same UTC/Local datetime pattern and the query results are all interconnected. Meaning that I have to evaluate each of the possible 16 situations at the same time because sometimes there are dependencies created between them and that would affect the query results.

I feel like I'm leaning towards the simple brute force method and that perhaps the best approach is just storing a unified UTC value in another indexed column. Then periodically updating it to ensure that DST doesn't mess things up.

9

I do like this approach and I will try it for a slightly different query I need to write that doesn't involve so many potential records needing to be calculated.