0

I have a c# solution which uses mssql database to store data.

In my database there is a big table of entities (e.g. accounts) for each of which the TimezoneId field is defined. This field is filled during the registration of a new account using the MaxMind GeoIP2 library.

+----+-------------+------------+
| Id | AccountName | TimeZoneId |
+----+-------------+------------+
|  1 | account1    |          1 |
|  2 | account2    |          1 |
|  3 | account3    |          2 |
|  4 | account4    |          3 |
|  5 | account5    |          1 |
+----+-------------+------------+

TimeZoneId as a foreign key for table TimeZones, which stores the names of time zones in the IANA time zone format (e.g., America/New_York).

+----+----------------------+
| Id |         Name         |
+----+----------------------+
|  1 | Europe/Paris         |
|  2 | Africa/Kampala       |
|  3 | Africa/Dar_es_Salaam |
|  4 | Asia/Karachi         |
+----+----------------------+

Now I need to be able to select all the accounts from the database, the time zone for which match with utc offset specified by the user. For example, if the user specifies the offset "UTC+2" i want to select accounts with timezones "Europe/Oslo", "Europe/Paris", "Europe/Rome" and other that have this offset. Which solution in terms of performance is best suited for this task?

Expand the TimeZones table with utc-offset field, create a memory cache with data about time zones and offsets or something else? I would appreciate advice.

4
  • 1
    How do you plan to handle Daylight Savings Time, which have different start and end days for different Time Zones, even if they have the same (current) offset. Commented Jun 4, 2019 at 14:14
  • 2
    Perhaps timely (pun intended): codeblog.jonskeet.uk/2019/03/27/… Commented Jun 4, 2019 at 15:54
  • 1
    A big part of the answer to a solution would also be what the use of the saved time field is. If it is a history for when something happened in the past, then saving as UTC makes sense because you can convert that time to a local time easily. If saving future dates then the linked blog post makes a good argument for saving in local time an event should occur and then converting. Commented Jun 4, 2019 at 20:44
  • As a separate point, it could be a good practice to use the timezone name (or some abbreviation of it) as the primary key here rather than a random Id number. Worries about performance implications of using (a limited number of) characters rather than ints for PKs are often unjustified, as the space difference is often negligible, joins can often be avoided altogether (because the timezone name is already stored with the account), and the raw data is easier to interpret in place.
    – Steve
    Commented Jun 5, 2019 at 13:23

2 Answers 2

3

As @1201ProgramAlarm commented, you have to consider DST for precision since the offset changes along the year. And from year to year since the saving time change doesn't occur every year on the same days.

Take Madrid's time zone and its DST for 2019. If the user specifies the offset UTC+2, we have to consider the possibility to allow the user to specify the reference date (or choose the current one by default) because accounts from Madrid will match the offset if the reference date is between April and November. In other words, Madrid is UTC+2 only a few months a year. If the date and time precision is critical for the business, this reference date is required. If you are to schedule events or tasks and date and time precision is important, the reference date is important.

A way to expand TimeZones table could be adding the information related with the ST so you can choose the right offset any time along the year. A simple solution could be expanding the TimeZone table setting a default_offset. Choose which offset you think should be the default one then set the ST date range and its offset somewhere else. E.g

 [Account] n--->1 [TimeZone] 1--->n [SavingTime]

or

 [Account] n--->1 [TimeZone] 1--->1 [SavingTime]

Bear in mind that the ST date range should be updated yearly since it varies from year to year. Check out the previous example and note that values in the Date & Time column change from year to year. The ST could also change due to political decisions. This's something fairly possible within the EU in the near future since some countries are trying to conceal their calendars with the rest of the countries.

As for performance, don't rush. As said often solve the problem first, optimize later. The opposite is often premature optimization and could distract you from the real goal of the application. Once the solution is implemented, get some metrics and compare with those expected or desired.

0

Expanding table with offset column seems to be a good idea. There is no point in searching for optimizations as far as it works good enough.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.