UTC to local time conversion for previously saved datetimes if rules for timezone change

Question

I'm storing a product in db. All dates (sql server datetime) are UTC and along with the dates I store the time zone id for that product. User enters the dates when product is available "from" and "until" in the listing. So I do something like:

// Convert user's datetime to UTC
var userEnteredDateTime = DateTime.Parse("11/11/2014 9:00:00");
// TimeZoneInfo id will be stored along with the UTC datetime
var tz = TimeZoneInfo.FindSystemTimeZoneById("FLE Standard Time");
// following produces: 9/11/2014 7:00:00 AM (winter time - 1h back)
var utcDateTime = TimeZoneInfo.ConvertTimeToUtc(userEnteredDateTime, tz);

and save the record. Let's assume user did this on the 1st of August, while his time zone offset to UTC is still +03:00, nevertheless the saved date for the future listing has the correct +02:00 value because conversion took into consideration the "winter" time for that period.

Question is what datetime value will I get if I will attempt to convert that product's "from" and "until" date to product's local time zone on 11/11/2014 if, for example, due to some new rules the transition to winter time was abandoned, thus the time zone is still +03:00 instead of +02:00?

// Convert back
var userLocalTime = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, tz);

will I get 10AM or correct 9AM because OS/.NET patch will handle this?

Thank you!

P.S.: TimeZoneInfo has ToSerializedString() method, if I rather store this value instead of timezone id, will this guarantee that via UTC datetime + serialized timezoneinfo I will always be able to convert to the user's original datetime input?

Answer 1

In the scenario you describe, you would get 10:00 AM. The time zone conversion function would not have any idea that the value was originally entered as 9:00 AM, because you only saved the UTC time of 7:00 AM.

This illustrates one of the cases where the advice "always store UTC" is flawed. When you're working with future events, it doesn't always work. The problem is that governments change their mind about time zones often. Sometimes they give reasonable notice (ex. United States, 2007) but sometimes they don't (ex. Egypt, 2014).

When you made the original conversion from local time to UTC, you intentionally decided to trust that the time zone rules would not change. In other words, you decided that you would assign the event to the universal timeline based solely on the time zone rules as you knew them at that time.

The way to avoid this is simple: Future events should be scheduled in local time. Now, I don't mean "local to your computer", but rather "local to the user", so you will need to know the user's time zone, and you should also store the ID of the time zone somewhere.

You'll also need to decide what you want to do if the event falls into the spring-forward or fall-back transition for daylight saving time. This is especially important for recurrence patterns.

Ultimately though, you'll need to figure out when to run the event. Or in your case, you'll need to decide if the event has passed or not. There are a few different ways you can accomplish this:

Option 1

• You can calculate the corresponding UTC value for each local time and keep it in a separate field.

• On some cycle (daily, weekly, etc) you can recalculate upcoming UTC values from their local values and your current understanding of the time zone rules. Or, if you apply time zone updates manually, you can choose to recalculate everything at that time.

Option 2

• You can store the values as a DateTimeOffset type instead of a DateTime. It will contain the original local time, and the offset that you calculated based on the time zone rules as you knew them at time of entry.

• DateTimeOffset values can easily be coerced back to UTC, so they tend to work very well for this. You can read more in DateTime vs DateTimeOffset.

• Just like in option 1, you would revisit the values periodically or after time zone data updates, and adjust the offsets to align with the new time zone data.

• This is what I usually recommend, especially if you're using a database that has support for DateTimeOffset types, such as SQL Server or RavenDB.

Option 3

• You can store the values as a local DateTime.

• When querying, you would calculate the current time in the target time zone and compare against that value.

  DateTime now = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, targetTZ);
  bool passed = now >= eventTime;
  

• The down side of this option is that you may have to make lots queries if you have events in lots of different time zones.

• You may also have issues with values close to the fall-back DST transition, so be careful if you use this approach.

I recommend against the idea of serializing the time zone itself. If the time zone has changed, then it has changed. Pretending that it hasn't isn't a good workaround.