Storing UTC is not a silver bullet

Note: this is a pretty long post. If you’re not interested in the details, the conclusion at the bottom is intended to be read in a standalone fashion. There’s also a related blog post by Lau Taarnskov – if you find this one difficult to read for whatever reason, maybe give that a try.

When I read Stack Overflow questions involving time zones, there’s almost always someone giving the advice to only ever store UTC. Convert to UTC as soon as you can, and convert back to a target time zone as late as you can, for display purposes, and you’ll never have a time zone issue again, they say.

This blog post is intended to provide a counterpoint to that advice. I’m certainly not saying storing UTC is always the wrong thing to do, but it’s not always the right thing to do either.

Note on simplifications: this blog post does not go into supporting non-Gregorian calendar systems, or leap seconds. Hopefully developers writing applications which need to support either of those are already aware of their requirements.

Background: EU time zone rule changes

The timing of this blog post is due to recent European Parliament proceedings that look like they will probably end the clocks changing twice a year into “summer time” or “winter time” within EU member states. The precise details are yet to be finalized and are unimportant to the bigger point, but for the purpose of this blog post I’ll assume that each member state has to decide whether they will “spring forward” one last time on March 28th 2021, then staying in permanent “summer time”, or “fall back” one last time on October 31st 2021, then staying in permanent “winter time”. So from November 1st 2021 onwards, the UTC offset of each country will be fixed – but there may be countries which currently always have the same offset as each other, and will have different offsets from some point in 2021. (For example, France could use winter time and Germany could use summer time.)

The larger point is that time zone rules change, and that applications should expect that they will change. This isn’t a corner case, it’s the normal way things work. There are usually multiple sets of rule changes (as released by IANA) each year. At least in the European changes, we’re likely to have a long notice period. That often isn’t the case – sometimes we don’t find out about rule changes until a few days before they happen.

Application example

For the sake of making everything concrete, I’m going to imagine that we’re writing an application to help conference organizers. A conference organizer can create a conference within the application, specifying when and where it’s happening, and (amongst other things) the application will display a countdown timer of “the number of hours left before the start of the conference”. Obviously a real application would have a lot more going on than this, but that’s enough to examine the implementation options available.

To get even more concrete, we’ll assume that a conference organizer has registered a conference called “KindConf” and has said that it will start at 9am in Amsterdam, on July 10th 2022. They perform this registration on March 27th 2019, when the most recently published IANA time zone database is 2019a, which predicts that the offset observed in Amsterdam on July 10th 2022 will be UTC+2.

For the sake of this example, we’ll assume that the Netherlands decides to fall back on October 31st 2021 for one final time, leaving them on a permanent offset of UTC+1. Just to complete the picture, we’ll assume that this decision is taken on February 1st 2020, and that IANA publishes the changes on March 14th 2020, as part of release 2020c.

So, what can the application developer do? In all the options below, I have not gone into details of the database support for different date/time types. This is important, of course, but probably deserves a separate blog post in its own right, on a per-database basis. I’ll just assume we can represent the information we want to represent, somehow.

Interlude: requirements

Before we get to the implementations, I’ll just mention a topic that’s been brought up a few times in the comments and on Twitter. I’ve been assuming that the conference does still occur at 9am on July 10th 2022… in other words, that the “instant in time at which the conference starts” changes when the rules change.

It’s unlikely that this would ever show up in a requirements document. I don’t remember ever being in a meeting with a product manager where they’d done this type of contingency planning. If you’re lucky, someone would work out that there’s going to be a problem long before the rules actually change. At that point, you’d need to go through the requirements and do the implementation work. I’d argue that this isn’t a new requirement – it’s a sort of latent, undiscovered requirement you’ve always had, but you hadn’t known about before.

Now, back to the options…

Option 1: convert to UTC and just use that forever

The schema for the Conferences table in the database might look like this:

  • ID: auto-incremented integer
  • Name: string
  • Start: date/time in UTC
  • Address: string

The entry for KindConf would look like this:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T07:00:00Z
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands

That entry is then preserved forever, without change. So what happens to our countdown timer?

Result

The good news is that anyone observing the timer will see it smoothly count down towards 0, with no jumps. The bad news is that when it reaches 0, the conference won’t actually start – there’ll be another hour left. This is not good.

Option 2: convert to UTC immediately, but reconvert after rule changes

The schema for the Conferences table would preserve the time zone ID. (I’m using the IANA ID for simplicity, but it could be the Windows system time zone ID, if absolutely necessary.) Alternatively, the time zone ID could be derived each time it’s required – more on that later.

  • ID: auto-incremented integer
  • Name: string
  • Start: date/time in UTC
  • Address: string
  • Time zone ID: string

The initial entry for KindConf would look like this:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T07:00:00Z
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam

On March 14th 2020, when the new time zone database is released, that entry could be changed to make the start time accurate again:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T08:00:00Z
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam

But what does that “change” procedure look like? We need to convert the UTC value back to the local time, and then convert back to UTC using different rules. So which rules were in force when that entry was created? It looks like we actually need an extra field in the schema somewhere: TimeZoneRulesVersion. This could potentially be a database-wide value, although that’s only going to be reasonable if you can update all entries and that value atomically. Allowing a value per entry (even if you usually expect all entries to be updated at roughly the same time) is likely to make things simpler.

So our original entry was actually:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T07:00:00Z
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam
  • TimeZoneRules: 2019a

And the modified entry is:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T08:00:00Z
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam
  • TimeZoneRules: 2020c

Of course, the entry could have been updated many times over the course of time, for 2019b, 2019c, …, 2020a, 2020b. Or maybe we only actually update the entry if the start time changes. Either way works.

Result

Now, anyone refreshing the countdown timer for the event will see the counter increase by an hour when the entry is updated. That may look a little odd – but it means that when the countdown timer reaches 0, the conference is ready to start. I’m assuming this is the desired behaviour.

Implementation

Let’s look at roughly what would be needed to perform this update in C# code. I’ll assume the use of Noda Time to start with, but then we’ll consider what happens if you’re not using Noda Time.

public class Conference
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public Instant Start { get; set; }
    public string TimeZoneId { get; set; }
    public string TimeZoneRules { get; set; }
}

// In other code... some parameters might be fields in the class.
public void UpdateStartTime(
    Conference conference,
    Dictionary<string, IDateTimeZoneProvider> timeZoneProvidersByVersion,
    string latestRules)
{
    // Map the start instant into the time zone using the old rules
    IDateTimeZoneProvider oldProvider = timeZoneProvidersByVersion[conference.TimeZoneRules];
    DateTimeZone oldZone = oldProvider[conference.TimeZoneId];
    ZonedDateTime oldZonedStart = conference.Start.InZone(oldZone);   

    IDateTimeZoneProvider newProvider = timeZoneProvidersByVersion[latestRules];
    DateTimeZone newZone = newProvider[conference.TimeZoneId];
    // Preserve the local time, but with the new time zone rules
    ZonedDateTime newZonedStart = oldZonedStart.LocalDateTime.InZoneLeniently(newZone);

    // Update the conference entry with the new information
    conference.Start = newZonedStart.ToInstant();
    conference.TimeZoneRules = latestRules;
}

The InZoneLeniently call is going to be a common issue – we’ll look at that later (“Ambiguous and skipped times”).

This code would work, and Noda Time would make it reasonably straightforward to build that dictionary of time zone providers, as we publish all the “NZD files” we’ve ever created from 2013 onwards on the project web site. If the code is being updated with the latest stable version of the NodaTime NuGet package, the latestRules parameter wouldn’t be required – DateTimeZoneProviders.Tzdb could be used instead. (And IDateTimeZoneProvider.VersionId could obtain the current version.)

However, this approach has three important requirements:

  • The concept of “version of time zone rules” has to be available to you
  • You have to be able to load a specific version of the time zone rules
  • You have to be able to use multiple versions of the time zone rules in the same application

If you’re using C# but relying on TimeZoneInfo then… good luck with any of those three. (It’s no doubt feasible, but far from simple out of the box, and it may require an external service providing historical data.)

I can’t easily comment on other platforms in any useful way, but I suspect that dealing with multiple versions of time zone data is not something that most developers come across.

Option 3: preserve local time, using UTC as derived data to be recomputed

Spoiler alert: this is my preferred option.

In this approach, the information that the conference organizer supplied (“9am on July 10th 2022”) is preserved and never changed. There is additional information in the entry that is changed when the time zone database is updated: the converted UTC instant. We can also preserve the version of the time zone rules used for that computation, as a way of allowing the process of updating entries to be restarted after a failure without starting from scratch, but it’s not strictly required. (It’s also probably useful as diagnostic information, too.)

The UTC instant is only stored at all for convenience. Having a UTC representation makes it easier to provide total orderings of when things happen, and also to compute the time between “right now” and the given instant, for the countdown timer. Unless it’s actually useful to you, you could easily omit it entirely. (My Noda Time benchmarks suggest it’s unlikely that doing the conversion on every request wouldn’t cause a bottleneck. A single local-to-UTC conversion on my not-terribly-fast benchmark machine only takes ~150ns. In most environments that’s close to noise. But for cases where it’s relevant, it’s fine to store the UTC as described below.)

So the schema would have:

  • ID: auto-incremented integer
  • Name: string
  • Local start: date/time in the specified time zone
  • Address: string
  • Time zone ID: string
  • UTC start: derived field for convenience
  • Time zone rules version: for optimization purposes

So our original entry is:

  • ID: 1
  • Name: KindConf
  • LocalStart: 2022-07-10T09:00:00
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam
  • UtcStart: 2022-07-10T07:00:00Z
  • TimeZoneRules: 2019a

On March 14th 2020, when the time zone database 2020c is released, this is modified to:

  • ID: 1
  • Name: KindConf
  • LocalStart: 2022-07-10T09:00:00
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam
  • UtcStart: 2022-07-10T08:00:00Z
  • TimeZoneRules: 2020c

Result

This is the same as option 2: after the update, there’s a jump of an hour, but when it reaches 0, the conference starts.

Implementation

This time, we don’t need to convert our old UTC value back to a local value: the “old” time zone rules version and “old” UTC start time are irrelevant. That simplifies matter significantly:

public class Conference
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public LocalDateTime LocalStart { get; set; }
    public string TimeZoneId { get; set; }
    public Instant UtcStart { get; set; }
    public string TimeZoneRules { get; set; }
}

// In other code... some parameters might be fields in the class.
public void UpdateUtcStart(
    Conference conference,
    IDateTimeZoneProvider latestZoneProvider)
{
    DateTimeZone newZone = latestZoneProvider[conference.TimeZoneId];
    // Preserve the local time, but with the new time zone rules
    ZonedDateTime newZonedStart = conference.LocalStart.InZoneLeniently(newZone);

    // Update the conference entry with the new information
    conference.UtcStart = newZonedStart.ToInstant();
    conference.TimeZoneRules = latestZoneProvider.VersionId;
}

As the time zone rules version is now optional, this code could be ported to use TimeZoneInfo instead. Obviously from my biased perspective the code wouldn’t be as pleasant, but it would be at least reasonable. The same is probably true on other platforms.

So I prefer option 3, but is it really so different from option 2? We’re still storing the UTC value, right? That’s true, but I believe the difference is important because the UTC value is an optimization, effectively.

Principle of preserving supplied data

For me, the key difference between the options is that in option 3, we store and never change what the conference organizer entered. The organizer told us that the event would start at the given address in Amsterdam, at 9am on July 10th 2022. That’s what we stored, and that information never needs to change (unless the organizer wants to change it, of course). The UTC value is derived from that “golden” information, but can be re-derived if the context changes – such as when time zone rules change.

In option 2, we don’t store the original information – we only store derived information (the UTC instant). We need to store information to tell us all the context about how we derived it (the old time zone rules version) and when updating the entry, we need to get back to the original information before we can re-derive the UTC instant using the new rules.

If you’re going to need the original information anyway, why not just store that? The implementation ends up being simpler, and it means it doesn’t matter whether or not we even have the old time zone rules.

Representation vs information

It’s important to note that I’m only talking about preserving the core information that the organizer entered. For the purposes of this example at least, we don’t need to care about the representation they happened to use. Did they enter it as “July 10 2022 09:00” and we then parsed that? Did they use a calendar control that provided us with “2022-07-10T09:00”? I don’t think that’s important, as it’s not part of the core information.

It’s often a useful exercise to consider what aspects of the data you’re using are “core” and which are incidental. If you’re receiving data from another system as text for example, you probably don’t want to store the complete XML or JSON, as that choice between XML and JSON isn’t relevant – the same data could be represented by an XML file and a JSON file, and it’s unlikely that anything later will need to know or care.

A possible option 4?

I’ve omitted a fourth option which could be useful here, which is a mixture of 2 and 3. If you store a “date/time with UTC offset” then you’ve effectively got both the local start time and the UTC instant in a single field. To show the values again, you’d start off with:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T09:00:00+02:00
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam
  • TimeZoneRules: 2019a

On March 14th 2020, when the time zone database 2020c is released, this is modified to:

  • ID: 1
  • Name: KindConf
  • Start: 2022-07-10T09:00:00+01:00
  • Address: Europaplein 24, 1078 GZ Amsterdam, Netherlands
  • TimeZoneId: Europe/Amsterdam
  • TimeZoneRules: 2020c

In systems that support “date/time with UTC offset” well in both the database and the languages using it, this might be an attractive solution. It’s important to note that the time zone ID is still required (unless you derive it from the address whenever you need it) – there’s a huge difference between knowing the time zone that’s applied, and knowing the UTC offset in one specific situation.

Personally I’m not sure I’m a big fan of this option, as it combines original and derived data in a single field – the local part is the original data, and the offset is derived. I like the separation between original and derived data in option 3.

With all those options presented, let’s look at a few of the corner cases I’ve mentioned in the course of the post.

Ambiguous and skipped times

In both of the implementations I’ve shown, I’ve used the InZoneLeniently method from Noda Time. While the mapping from UTC instant to local time is always completely unambiguous for a single time zone, the reverse mapping (from local time to UTC instant) is not always unambiguous.

As an example, let’s take the Europe/London time zone. On March 31st 2019, at 1am local time, we will “spring forward” to 2am, changing offset from UTC+0 to UTC+1. On October 27th 2019, at 2am local time, we will “fall back” to 1am, changing offset from UTC+1 to UTC+0. That means that 2019-03-31T01:30 does not happen at all in the Europe/London time zone, and 2019-10-27T01:30 occurs twice.

Now it’s reasonable to validate this when a conference organizer specifies the starting time of a conference, either prohibiting it if the given time is skipped, or asking for more information if the given time is ambiguous. I should point out that this is highly unlikely for a conference, as transitions are generally done in the middle of the night – but other scenarios (e.g. when to schedule an automated backup) may well fall into this.

That’s fine at the point of the first registration, but it’s also possible that a previously-unambiguous local time could become ambiguous under new time zone rules. InZoneLeniently handles that in a way documented in the Resolvers.LenientResolver. That may well not be the appropriate choice for any given application, and developers should consider it carefully, and write tests.

Recurrent events

The example I’ve given so far is for a single event. Recurrent events – such as weekly meetings – end up being trickier still, as a change to time zone rules can change the offsets for some instances but not others. Likewise meetings may well be attended by people from more than a single time zone – so it’s vital that the recurrence would have a single coordinating time zone, but offsets may need to be recomputed for every time zone involved, and for every occurrence. Application developers have to think about how this can be achieved within performance requirements.

Time zone boundary changes and splits

So far we’ve only considered time zone rules changing. In options 2-4, we stored a time zone ID within the entry. That assumes that the time zone associated with the event will not change over time. That assumption may not be valid.

As far as I’m aware, time zone rules change more often than changes to which time zone any given location is in – but it’s entirely possible for things to change over time. Suppose the conference wasn’t in Amsterdam itself, but Rotterdam. Currently Rotterdam uses the Europe/Amsterdam time zone, but what if the Netherlands splits into two countries between 2019 and 2022? It’s feasible that by the time the conference occurs, there could be a Europe/Rotterdam time zone, or something equivalent.

To that end, a truly diligent application developer might treat the time zone ID as derived data based on the address of the conference. As part of checking each entry when the time zone database is updated, they might want to find the time zone ID of the address of the conference, in case that’s changed. There are multiple services that provide this information, although it may need to be a multi-step process, first converting the address into a latitude/longitude position, and then finding the time zone for that latitude/longitude.

Past vs recent past

This post has all been about future date/time values. In Twitter threads discussing time zone rule changes, there’s been a general assertion that it’s safe to only store the UTC instant related to an event in the past. I would broadly agree with that, but with one big caveat: as I mentioned earlier, sometimes governments adopt time zone rule changes with almost no notice at all. Additionally, there can be a significant delay between the changes being published and them being available within applications. (That delay can vary massively based on your platform.)

This means that while a conversion to UTC for a value more than (say) a year ago will probably stay valid, if you’re recording a date and time of “yesterday”, it’s quite possible that you’re using incorrect rules without knowing it. (Even very old rules can change, but that’s rarer in my experience.)

Do you need to account for this? That depends on your application, like so many other things. I’d at least consider the principle described above – and unless it’s much harder for you to maintain the real source information for some reason, I’d default to doing that.

Conclusion

The general advice of “just convert all local date/time data to UTC and store that” is overly broad in my view. For future and near-past events, it doesn’t take into account that time zone rules change, making the initial conversion potentially inaccurate. Part of the point of writing this blog post is to raise awareness, so that even if people do still recommend storing UTC, they can add appropriate caveats rather than treating it as a universal silver bullet.

I should explicitly bring up timestamps at this point. Machine-generated timestamps are naturally instants in time, recording “the instant at which something occurred” in an unambiguous way. Storing those in UTC is entirely reasonable – potentially with an offset or time zone if the location at which the timestamp was generated is relevant. Note that in this case the source of the data isn’t “a local time to be converted”.

That’s the bigger point, that goes beyond dates and times and time zones: choosing what information to store, and how. Any time you discard information, that should be a conscious choice. Are you happy discarding the input format that was used to enter a date? Probably – but it’s still a decision to make. Defaulting to “convert to UTC” is a default to discarding information which in some cases is valid, but not all. Make it a conscious choice, and ensure you store all the information you think may be needed later. You might also want to consider whether and how you separate “source” information from “derived” information – this is particularly relevant when it comes to archiving, when you may want to discard all the derived data to save space. That’s much easier to do if you’re already very aware of which data is derived.

My experience is that developers either don’t think about date/time details nearly enough when coding, or are aware of some of the pitfalls but decide that means it’s just too hard to contemplate. Hopefully this worked example of real life complexity shows that it can be done: it takes a certain amount of conscious thought, but it’s not rocket science.

75 thoughts on “Storing UTC is not a silver bullet”

  1. A final option would be to leave things as they were, and instead ask conference organizers to confirm whether they would like their start time to stay as entered or moved by an hour. This delegates all the problematic decision making, and lets the developer get on with solving more pressing matters ;)

    Liked by 2 people

    1. “A final option would be to leave things as they were”

      That’s meaningless.

      “and instead ask conference organizers to confirm whether they would like their start time to stay as entered or moved by an hour”

      Did you read or understand a single word of this article? The string describing the start time remains unchanged, but when that is relative to the time that someone queries how long it will be before the conference starts changes depending on timezone rules and even what timezone the address of the conference is in.

      Like

      1. Ignoring the problem is not meaningless at all, if it is not a vital business objective to handle rare occurrences, such as timezone changes, correctly. For instance, in my home country Denmark, summer-time has been in effect sporadically: “Daylight saving time was used in the years 1916, 1940, 1945-1948 and is used from 1980.” (source: wikipedia). I have never heard of anyone adding logic to their application to handle this, let alone one doing this for multiple countries.

        Sometimes the best solution to a problem is not to add more code/state, but to take a step back and solve it differently.

        Like

  2. So much thinking about time zones and UTC and all and then there is this big company which releases its next visual studio version globally at the same time but fails to fix the remaining time widget on their landing page: https://visualstudio.microsoft.com/de/vs2019-launch/

    At least for me in Germany, the remaining hours are just wrong.

    it currently says 5 days, 15 hours, 38 min but it should be 5 days, 1 hour, 38 min I think.

    Also say fail to listen because I reported it already :-(

    sad

    Like

  3. I think you approached problem with a blurred view of what it means if a timezone changes its settings? How to convert back too those UTC’s stored back to local timezone? to respect the day they recorded? or to respect todays settings?
    The solution is to treat timezone as different timezone. Once a timezone changes its settings it is no longer same old timezone. What EU doing will introduce new time-zones but alas, with same names! To make programmers life easy now all those old timezones must be renamed to something like CentralEuropeSummerTimeZonePre2020 ;; If there is a business reason to display recorded time in old timezone we could support it easily. Store always in UTC is still silver bullet. :-) I learned this lesson in hardway. Around ten years back my hosting company shifted servers from west to east and all my server NOW()/CURRENT_TIME_STAMP stored times are invalid!

    Like

    1. I note on Twitter that you’ve only read the start and the end so far. Hopefully when you’ve read the rest of the post you’ll see how storing just UTC is really, really not the solution for this. (For timestamps, sure – but that’s not the scenario described.)

      Liked by 1 person

  4. Great example and explanation, Jon. There may be even more complex scenarios (say, train schedule between various European countries), but fortunately, for most apps, local time is nothing more than a UI pleasantry, so an hour more or less is not going to kill them, but for the ones that would care, it’s nice to have options. Thanks for sharing. I wish we had all countries (and states) switch to using UTC, though. :-)

    Liked by 1 person

  5. “if you find this one difficult to read”

    Your presentation was very clear, but apparently some people “read” an article by glancing at it while their attention is focused on something else.

    Like

  6. Just a matter of semantics but would it make sense, provided database field name lengths doesn’t matter, to rename from
    LocalStart: …
    UtcStart: …
    to e.g.
    LocalStart: …
    UtcStartDerived: …
    to better show the source -> symptomatic data connection? (I usually like to use such naming — also, “somethingSomethingCached” — to make intent extra clear for later maintenance.)

    Like

  7. Fwiw nobody stores UTC — at least not anybody sensible. The Unix timestampe for example is seconds since the epoch. That’s just a quantity of seconds which is the same everywhere on Earth and doesn’t depend on UTC or any other time zone definition.

    Liked by 1 person

    1. Leaving aside leap seconds, that is a meaningless distinction. Unix timestamps, aka “seconds since 1970-01-01T00:00:00Z”, is just a concrete representation of UTC as a single number.

      Liked by 2 people

    1. What do you expect the difference between “epoch time” and “UTC datetime” value to be? They’re equivalent, in terms of being time-zone-neutral. (And therefore a change in time zone rules without a corresponding change in epoch time means a change in observed local time.)

      Like

      1. I think I got it now. I was thinking that what is the harm saving a timestamp. I didn’t realize it is not the issue with the timestamp, it is the issue is that destination of that alarm’s timezone changed as well.

        Cool post.

        Like

  8. I work with industrial process data a lot, and I’ve always gone down the “always store in UTC” approach. I think that storing in UTC is likely to be the best approach whenever you are dealing with events up to the current time, but I think you make a compelling case when your system might be dealing with timestamps in the future. Thanks for an interesting read!

    Like

  9. Well said. UTC makes sense for point-in-time history, but future things must be stored in local time. That’s my big peeve with Google Calendar. If I import an .ics file, they immediately convert everything in it to UTC which is just flat wrong. If instead of quickly editing a text file and importing it, I go through the tedium of picking a TZ in their UI, then it does the right thing. But considering noone else can make an .ics parser that understands recurring events, it’s the best option available (I’ve even played with a few that convert all events to 1 hour duration on import! or ignore events with a recurrence)

    Liked by 1 person

  10. I store UTC and an offset to real local time … to accurately account for local day-light-saving and weird time-zone considerations … and I only do this when dealing with “global” apps or apps involving US an another country (of another time-zone).

    Like

  11. Great article! But “spring forward” will occur in London on March 31th 2019, not March 30th 2019.
    Just a minor thing, though.

    Like

  12. Well, it is a silver bullet but without a proper weapon, it can not be fired.
    Here how it goes.
    Keep UTC always as double. Full stop. Use PROPER value, not a string which actually renders a value!
    And the weapon is to have LAT, LON as two doubles in the database as well.
    Use function ConvertUTC2LocalTimeBasedOnLatLon.
    This way all of the grief is gone.
    We have been using this methodology for years with great success and it never failed.
    (Keep things as simple as possible but not more than that AA)

    Like

    1. Keep UTC always as double. Full stop. Use PROPER value, not a string which actually renders a value!

      I don’t think using binary floating point value is a good idea, unless you really want precision to vary over time. (I’ve seen an issue based on this just recently.) An integer number of nanoseconds/microseconds/whatever is a better underlying representation, IMO. But fundamentally the user shouldn’t need to care, most of the time – that should be hidden by the database and whatever date/time library you’re using.

      But you don’t mention storing anything about which version of the rules you’re using – so in your scheme, how do you propose to update the stored UTC time when the time zone rules change?

      We have been using this methodology for years with great success and it never failed.

      Do you store future date/time values? How have you adjusted them based on rules changing over time? That’s the entire focus of this post, and I don’t see how you’re addressing it at the moment.

      Like

    2. Sure, storing ZULU Time in milliseconds as long integer is what we actually use :-)
      As far as rules are concerned they are defined by ZULU (UTM) time. Rules do not change that often.
      Mind you daylight saving (DLS – that is a painful rule) can be a problem when showing local time :-)
      Half an hour before DLS and half an hour after DLS does not make sense when shown in local time, particularly when switching to winter time.
      Regardless how TIME is defined (past, present or future, DLS active or not) adding coordinate Lat Lon to that TIME, the function will pull out appropriate rule and deliver local time (Lat Lon based), whatever the rule was at that specific time.
      It actually means that you do not need to store local time, you calculate it and render to output on the fly.
      This way your visitors from abroad can easily see what is the local time is at home and local time at the place of the event abroad.
      It is good practice to have visitors home Lat Lon coordinates available. No need for high precision at all.
      Local time is a function of UTC (ZULU if you wish), Lat Lon and the rule.
      .

      Like

      1. As far as rules are concerned they are defined by ZULU (UTM) time. Rules do not change that often.

        But the whole point of this post is that the rules are likely to change for much of the European Union in the next couple of years.

        Please read the post again – your comment appears to be completely ignoring the scenario presented here. Either that, or you think it’s fine to store a UTC value that ends up not representing the local time originally entered by the conference organizer.

        Like

        1. Sure rules do change. For the future. Not for the past.
          However, which rule to pick up for an event yet to come is defined by future UTC(ZULU), Lat Lon and staring rule ZULU time :-)
          And it works without any problems. You can always calculate local time using ZULU time. And calculate related local time for any point in the world using Lat Lon. ZULU time tells which rule to apply (past, present or future), based on Lat Lon.
          TZ database is the key to success as Todd explained. It is not an operating system (Unix, iOS, Windows, etc.) but TZ database to stick with. Sure some programming is needed. Not just OS call.

          Like

          1. Sure rules do change. For the future. Not for the past.

            Well they do change for the past, but less often. And it’s far from unlikely that you might store a time in the past without having received the latest version of the TZ database. See the penultimate section.

            But 90% of the post explicitly discusses a concrete example of the future.

            However, which rule to pick up for an event yet to come is defined by future UTC(ZULU), Lat Lon and staring rule ZULU time :-)

            Only if the start time is fixed in UTC, rather than fixed in local time. You seem to have still missed the point of the post: the conference start time is defined in local time, and that means you need to know how and when to change the value if you’ve actually stored UTC.

            If you have only stored UTC and lat/lng in your database, you’re missing the information of the rules version that you used to perform the conversion. If you also store the rules version, you end up having to do two conversions rather than one, with more complex code – compare options 2 and 3 in the post.

            TZ database is the key to success as Todd explained.

            I’m very well aware of the TZ database, as the author of the Noda Time package that exposes it for .NET developers…

            Like

  13. Or, you could use .NET Core on Linux. The reason is that unlike Windows, Linux keeps a zoneinfo or tz database that contains historical time zone definitions from 1970 on. So if you store in UTC, you can always convert back to a local time that is correct given the period, and that really is the heart of your problem.

    Like

    1. Windows has had historical support for time zone data for quite a while now. (And you can still use TZDB on Windows, via Noda Time or other projects.)

      But all of that misses the point: the rule changes are still in the future at this point. Not just the period of time that they will affect, but the changes themselves. We don’t yet know whether Europe/Amsterdam will be observing UTC+1 or UTC+2 in July 2022. Therefore if you perform the conversion from local time to UTC now, you can do the wrong thing.

      Please read the example again, carefully.

      Liked by 1 person

  14. I think you miss understand the point of storing in UTC, because the point is to prevent these problems specifically. secondly you’re not supposed to use UTC for calculations. so say the start time is 2022-07-10T07:00:00Z when calculating time until the conference it should be converted to the current local time then calculated. this is to introduce and time zone changes currently in effect. The point of storing in UTC is specifically intended to solve these problems. Yes today the future time might be wrong based on time zone changes that haven’t happened yet, but the day of the event by converting it to local time the count down would correct for daylight savings or and other time zone changes that occurred.

    Like

    1. I think you miss understand the point of storing in UTC, because the point is to prevent these problems specifically.

      How does it prevent the problem of “changes to rules mean that the UTC date/time corresponding to a local date/time changes due to changes in rules, at a later time than it was specified”.

      Yes today the future time might be wrong based on time zone changes that haven’t happened yet, but the day of the event by converting it to local time the count down would correct for daylight savings or and other time zone changes that occurred.

      No, it wouldn’t. That’s the point. On the day of the event, if you just keep the original UTC value and then convert into the local time, you’d end up predicting that the conference starts at 8am, when the conference organizer said it starts at 9am.

      How is that preventing the problem? It’s specifically causing the problem – whereas storing the originally-specific local time instead is what prevents the problem.

      Storing UTC means storing an instant in time. The time zone rule changes (which are only known after the values are initially stored) change the instant at which the conference will start (if we expect that it will still actually start at 9am, which is what I do expect), therefore the value stored is incorrect.

      Like

    2. That is true only if you do not need to use/access/display dates in the future. If you need to show a time in the future, now, then you need to know the rules and transformation required to correctly translate the future date. This is of course how most of us use dates.

      That said, depending on what you’re doing, handling the problem in code might not be the most optimal solution business-wise. It’s complicated to get right, requires more state in the database for every date, and is generally painful to even think about. Having slightly incorrect times for a while might be a worthwhile tradeoff compared to the investment it takes to do things correctly. Again, depends on what you do and how much effort you can throw at the problem.

      Like

  15. 1) If the event is moved in abosolute time, e.g. because the time zone (rules) are changed, and it is desired to move the event accordingly, any UTC reference to the event obviously must be changed. Problems occur only if you are completely unaware of the move – you never noticed that you had to adjust your clock due to summer/winter time changes, time zone rule changes or that you move to another time zone.

    2) In the transition period from summer/winter time to fixed UTC offset (as disciussed in the article) you may not know yet if the event will move in absolute time. If you try to make a guess before anything has been decided, it may be wrong. You should not update the absolute time until you know that the event will be moved in absolute time, but then you should do it.

    This applies to any future time zone rule change, anywhere: If an event moves in absolute time, then you change its absolute time. Trying to avold it by pretending that there is no move, according to some other time reference that moves back and forth arbirtrarily due to some local timzone rules, is bound to fail.

    You must be prepared for events – like meetings, conferences, celebrations, … – to be moved in time due to lots of other reasons than time zone issues, even when given in local time. So you must anyway have in place the mechanism for changing the time. A UTC time stamp is not carved in stone: If the event is moved, change the UTC time!

    China (“which we call Red China”…) abandoned time zones; there is a single national time. I think that is a great idea. Rather than messing with time zone changes, summer/winter time etc. we should abandon local time zones altogether and set our clocks to UTC. It is perfectly fine if I go to work at 06:00, the Brits start at 08:00, and the American guys between 13:00 and 18:00 depending on location. That would be much more informative than everbody start working at 08:00

    Liked by 1 person

  16. The application design for this scenario should have a Conference entity that is the only entity that stores the local time zone for the conference. The daily schedules and all of that should be in local times, possibly just strings. Mon 8:00.

    Once the new time zones become published, you will have to update only the single field in the Conference entity.

    Like

    1. There’s only one entity discussed in this post. But yes, the whole point of the post is that storing the local time is okay, because that doesn’t change; storing just UTC (without any record of how the conversion to UTC was performed) loses information.

      Like

  17. Very unhelp, UTC counts every seconds for the next 100 years. What you are really high lighting is that converting to and from local time we be problematic in Europe as OS do not yet have TZs for the future.

    What should the user should do if when and if the EU change a countries TZ, simple, if the originator is not happy that the EU has moved his appointment then he should reschedule.

    Like

    1. I’m not really sure what you’re trying to say with this comment, I’m afraid. Do you disagree that the issue I’ve highlighted is a real concern that goes against the “Just store UTC in every case” advice I see so often?

      if the originator is not happy that the EU has moved his appointment then he should reschedule.

      I agree that if the conference organizer doesn’t like the resulting change in instant, then they should modify the entry – but if they specified a local time, I’d expect that local time to be preserved. I expect that to be the most common real-life result – the local time continues as it was, and the instant in time is changed. But if you store just the UTC value and think that means you no longer have to worry about time zones, then the opposite happens – the instant stays the same, and the local time changes.

      Liked by 1 person

  18. The logic in this article is simply wrong.

    First, yes, always store dates as UTC – but they must be dates captured as UTC! In .NET that is DateTime.UtcNow. Most DBs have a data type that corresponds to UTC – if not use unix timestamps or an literal int64 offset.

    Naive way of the start time in UTC as a crude example: desiredLocalTime – DateTime.Now + DateTime.UtcNow

    Second, always convert to local time at the last possible point for display. For reporting, that would be before date aggregation/bucketing, and for UI that’s just a dynamic conversion at the presentation layer.

    Third, in this scenario where a chosen date/time falls after a new timezone rule change has been implemented, there is no issue! The conference will still occur at the same UTC time.

    So if that used to correspond to 8 AM local, and after the new rule it corresponds to 7 AM local, that is correct and expected.

    This of it this way: for people living in another timezone, the conference may be at 9 AM or 10 AM, and maybe their local timezone rules have not changed. Just because the organizer’s timezone rules changed does not imply any specific requirement about what should be done.

    Instead, the schema could track the organizer’s original requested start time and timezone. Then some orchestrator could manually scan, grouping by timezones, to see it if any UTC date using the current rules does not convert to the original local start date. If a mismatch is found, alerts could be sent to all organizers in that timezone. The organizers should be required to decide what to do: if the organizer wants to reschedule the attendees would need to be notified.

    Alternatively, this can be handled by a setting for the meeting: is this meeting scheduled using local time zone rules? In that case, still record the UTC start time as NULL and record only the local start time with timezone info (or have a column that records the choice and an orchestrator that does a periodic scan and updates the affected UTC timestamps). If the NULL approach is used, a view can be created that dynamically calculates all missing UTC times by converting the local start times.

    In the the case of a local in office meeting, the organizer would use the local time option to ensure that the meeting starts at their 8 AM regardless of rule changes.

    This is how Outlook and most modern calender apps function…

    Like

    1. but they must be dates captured as UTC!

      So what you mean is that the conference organizer has to bow to your approach because it makes things easier for the application? No, that’s not the way life works.

      Just because the organizer’s timezone rules changed does not imply any specific requirement about what should be done.

      I very much disagree on this. When time zone rules change, generally people keep using the same local times as they were using before – just like people still get up at the same local time before and after DST transitions.

      Alternatively, this can be handled by a setting for the meeting: is this meeting scheduled using local time zone rules? In that case, still record the UTC start time as NULL and record only the local start time with timezone info

      You means exactly as option 3 does? Doesn’t this entirely go against the start of your comment?

      I’m asserting that this scenario is effectively a meeting scenario, in which case you should not store UTC as the source of truth – and it looks like by this point in your comment, you’ve agreed. So in what way is “the logic in the article simply wrong”?

      Like

      1. Wow. Fast reply. I don’t know how to use fancy formatting so I will just reply to the comments above.

        My point about how to store UTC is objectively correct: precisely because of local time rules, UTC cannot be derived downstream without running into ambiguities – which you well know.

        The rest is about how the system should function. The post does not go into details about that, and that is the crux of the issue.

        Yes, I agree with you completely in spirit on what the remedies are to achieve the automatically adjusting local schedule times. Again, that is really a requirements question though.

        And you are 100% correct on this technical point: if the user’s local time and UTC are not both captured at the of scheduling, there won’t be a good objectively correct way of detecting discrepancies.

        My suggestion would be to put a simple explanation of the expected system function at the beginning of the article that clarifies those points. Because almost all scheduling software involves use cases that span multiple time zones and/or governing authorities.

        So, while the “this only applies to local time, period” rationale does produce the awkward situation you described, but it would be an extremely niche use case – and also very likely to cause scaling problems down the road.

        Your opinion 3 is similar to what I recommend but not exactly the same. And the differences are mainly due to differing interpretations of what the expected system behavior would be like.

        Proper calendering / scheduling systems require an orchestror process to keep the derived data in sync, and to send users alerts of changes to the derived data. That’s how outlook et al work today.

        You do raise an interesting concern about caching the previously used time zone conversion rule. Because you are correct that those are the interesting level of change… But I don’t think that data point actually adds any value.

        The most efficient orchestrator I can think of at the moment would cache all of the known rules and periodically check for changes to the rules. When a change is detected, it would need to build a map of the change impact. That way the affected derived dates could efficiently be updated as a slice using min/max dates. That logic would be extremely efficient in the best and worst cases.

        Like

  19. Here’s a much simpler version of my criticism: this scenario does not present any special problem for persistent date storage.

    Rather, it is a requirements problem: what should the system do?

    As always, the system will need to persist exactly as much data as required in order to implement the expected functions.

    Like

    1. But anyone who always follows “always store UTC” without thinking about it will then have a problem. The whole point is to stop people either following or recommending that as a universal rule.

      Like

      1. Not at all. And I explained why in my longer post. If the requirement is to provide adjustments and alerts to the derived times, then the local time must also be persisted in addition to the UTC time. (Regardless, the UTC time should always be persisted.)

        It is just a question of what the system is supposed to do and whether it should worry about changes to derived local times or derived UTC times.

        Like

        1. By the way, apologies if my tone came off as critical. These were meant as constructive feedback. I sent them from my phone though while on the move, and my intent might not be obvious.

          I appreciate the work put into this article, and I agree that the scenario you described is a common pitfall where developers working in environments without precise unit tests to specifically tests for these time zone nuances could easily get this implemented incorrectly. As such, it is wise to provide some best practices to the community.

          Like

          1. I did feel “The logic in this article is simply wrong” was a wee bit critical, yes :) But we’re a lot closer together now, I think.

            I do believe that there’s no concrete need to store the UTC value in this case – it’s purely an optimization, and could even be left out entirely until a need was proven. (For some reason my Noda Time benchmarks don’t appear to have tests for InZoneLeniently – I’ll need to add those, so I can give some reasonably concrete order-of-magnitude figures about the scale of optimization. I can edit that into option 3 when I’ve got the data.)

            I’ll add in an extra section about requirements. The interesting thing about the requirements here are that it’s really unlikely that “Handles changes in time zone rules in the future” would ever come up on a requirements doc – it’s a requirement all along, but an unrecognized one.

            Like

            1. Hah! If we’re talking about how to write good requirements / user stories, we’re stepping into the business analyst world. Yes, the best developers should also always have their own business analyst hats on and take matters such as these into account. And yes, most developers will do exactly what the requirements ask, and most business analysts would make the incorrect assumption that such things are patently obvious and thus don’t need to be in the requirements list. And yes, this communication gap inevitably results in faulty software.

              As with most things in software development, the hardest part here is locking down the real requirements and ensure proper unit tests exist to validate working software. The original article you linked to by Lau Taarnskov does a good job of very quickly and concisely introducing the specific problem the author is concerned about. Having read that article, your post makes a perfect sense in that context. My suggestion to add a bit more context to your article is to help your article stand on its own.

              I have repeated this scenario is about requirements, because there are situations (where people from multiple time zones, etc., are coordinating) where the changes to local time should be ignored. For example, let’s say we have a daily conference call with our US and Chinese developers (a common occurrence). Is there any objective way to say when that meeting should take place when the US goes into DST?

              For some companies, their primary stakeholders are in China and thus maybe they want to have the meeting follow Chinese time (which doesn’t have time zones or DST). For some companies the opposite would be true. For most companies, it would be case by case depending on who organized the meeting. For some situations, using the UTC time will be the most efficient.

              The approach I’ve recommended is to have three data points:
              1) the desired time in UTC <– this is valuable to always store because it completely eliminates the possibility of ambiguous local times
              2) the desired time in the target local time zone
              3) a boolean flag that determines which time is authoritative and which is derived

              Then the system would have an orchestrator that monitors the rules for changes, maps out which date ranges are impacted by detected changes, and then efficiently updates both derived local times and derived UTC times in bulk using simple shifts across each affected range using the range’s min/max as the filter criteria. That completely eliminates the need to do any per-record logic.

              DB psuedo-code update example:

              update appointments a
              set a.utcTime = a.utcTime + @utcShift
              where
              a.isLocalTime = 1 and a.utcTime >= @minAffectedUtcTime and a.utcTime <= @maxAfffectedUtcTime

              update appointments a
              set a.localTime = a.localTime + @localShift
              where
              a.isLocalTime = 0 and a.localTime >= @minAffectedLocalTime and a.utcTime <= @maxAfffectedLocalTime

              There would just need to be some loop in the orchestrator that issues those two update commands for each detected change. Assuming proper indexing, those could be incredibly efficient. Similarly, selects could be executed before the updates to find a list of records that need to be changed if users need to confirm first, etc.

              Like

  20. My second psuedo-code update example had a typo, but I’m sure you get the idea.

    update appointments a
    set a.localTime = a.localTime + @localShift
    where
    a.isLocalTime = 0 and a.localTime >= @minAffectedLocalTime and a.localTime <= @maxAfffectedLocalTime

    Like

  21. Most languages have two functions to convert between local and utc. You should always use them.

    This is a common problem, it you set your computer to the wrong timezone by mistake and make an appointment. When you correct your timezone the appointment should and will have moved. It is important that only your appointment is moved because everyone else you sent the appointment to accepted the UTC time of the appointment regardless of which country they are in. And of course your colleague sitting next to your who had the correct timezone on his computer.

    So when any country leaves the CET it means anyone in that country with international appointments must expected their appointments to move.

    Now should national appointments move too ?
    Yes, because you do not know the other commitments of the people (country citizens) that have already accepted the appointment. They might have an international conference call so can not make your new appointment time.

    Like

  22. A good article. I wonder if part of the issue is that the conference start is a logical time like the java LocalDate class used primarily for logical dates like birthdays. By contrast, the UTC time represents an actual point in time. So the logical time needs to be stored and then mapped to the relevant time instant which is essentially what you have done.

    Like

  23. I am guilty of giving the always UTC advice. I know why it makes sense at least in some contexts: for the wrong reasons. I show up at some client and need to work on some time series. I find date fields with no time offset. They tell me: we standardize on local time. I say whose? Silence. Maybe the clients’. HQ or installation site? Silence. Probably installation, as we have big customers crossing time zones. Makes sense. Where is that stored? Silence. There should be an address somewhere. You get the gist. The problem is that with local time you can mix things that should not be mixed. With UTC, you can’t. In that sense, it would be the same to standardize on any offset. But your point about future time points being invariant in local time, not UTC, is well taken. For historical data, maybe UTC supporters have a point. But there are recurring rules that are easy to express only in local, be them about the past or the future, like “peak tariff starts at 5pm local for 2018”. Now you have consumption data in UTC and need to come up with a bill. It’d be easier if you had everything in local. OK, conversion libs are there for that purpose. But for a flight, if you have arrival and departure in local, with a time zone change in between, and there is a change in offset at one end, then the flight time is off by an hour. People could die. Human time has to bend to physical time in this case. One of the two local times can not be invariant. It gets worse. If your conference started at 2am local time of the day of the transition to solar time (non daylight savings), the start time would be ambiguous because all the times between 2 and 3 am map to two possible UTCs. I know, nothing happens at 2am. Mostly. Actually, it’s time for a heart surgery. All resources need to line up for 2:30 am local, or the patient will die, but 2:30 local occurs twice in one night. If you code it wrong, the surgical team won’t assemble. If you are coding a library, you can’t exclude use for life-critical applications. Sure the lawyers write a disclaimer, but you still have a conscience. You want to make a promise that difference between local times supplemented with a time zone will be correct in your library. Makes sense right? Convert to UTC, diff, result in seconds.Wrong, during the backward transitions you have ambiguity. You need to specify an offset. There is no bijection between local + time zone and UTC. If we abandoned daylight savings, there would be one. OK so when an ambiguous local time is passed to the library, at creation time, as early a possible, a big fat exception is generated and passed hurriedly up the stack until it hits top level, and from there customer support. It’s 2 am and the patient is on life support. The triage nurse needs to create a surgery event for 2:30 am (the first of the two) and all you have to offer is an exception? Customer support escalates, pagers go off (remember those?) you get a call in the middle of the night and say “just schedule it for 3 am and it will work. Only problem is between 2 and 3”. Client says “That’s in 2 hours (because now it’s two and whole hour between 2 and 3 is going to happen twice) and the patient will be dead by then”. OK now the CTO is on the line saying the classic “How can I help? What do you need to fix this?” which roughly translates to “This is our biggest client, I can’t believe you can’t code some f** calendar app your head is rolling faster than the last breath of that poor chap waiting for surgery” and you feel dizzy, the memories of the Therac 25 come back to you and think: why wasn’t this caught in testing? (answer: there are more than 8000 possible hours in a year, and with CI testing has to take less than three minutes. Not a fat chance). Next day, the nurse managed to find a fall back to paper forms and phone calls, patient made it, CTO calmed down in meeting says: “We need to fix this. Can’t happen again”. You explain the bijection in layman terms. People scratch their heads. You say “We need to ask the time in UTC in those cases”. Product manager says: “can’t do that, not user friendly. Sometimes you enter local, sometimes UTC, not possible, imagine the complaints”. Then the CTO says: “wait a minute, why don’t we standardize on UTC?” and the cycle restarts.

    Liked by 1 person

  24. SQL Server (starting with 2016) supports this natively with the “AT TIME ZONE” command, which converts an inputdate to the corresponding datetimeoffset value in the target time zone. It relies on a Windows mechanism to convert datetime values across time zones meaning that on each windows update you get all the timezone specific convertion rules updated on the spot, no extra effort. You can wrap up this logic into a function with schema binding, it works with consecutive “at time zone” commands, which helps to convert datetimes to datetimes with offset automatically.

    Example:
    DECLARE @Date1 datetime = ‘2018-11-04’
    DECLARE @Date2 datetime = ‘2018-11-05’
    declare @UTCTz nvarchar(256) = ‘UTC’
    declare @CentralAmerizaTZ nvarchar(256) = ‘Central Standard Time’ — “-6”
    declare @SingaporeAmerizaTZ nvarchar(256) = ‘Singapore Standard Time’ — “+8”

    select @Date1 as UTC,
    @Date1
    at time zone @UTCTz
    at time zone @CentralAmerizaTZ as CentralAmerica,
    @Date1
    at time zone @UTCTz
    at time zone @SingaporeAmerizaTZ as Singapore,
    @Date2 as UTC,
    @Date2
    at time zone @UTCTz
    at time zone @CentralAmerizaTZ as CentralAmerica,
    @Date2
    at time zone @UTCTz
    at time zone @SingaporeAmerizaTZ as Singapore

    Like

    1. This is basically option 4, but it comes with caveats.

      meaning on each windows update you get all the timezone specific convertion rules updated on the spot, no extra effort

      The rules are updated, but the data won’t be. You’d still need to store the time zone ID separately, and then any time the rules change, reapply all the conversions, because the offset could have changed. I dare say you could do all of that in SQL rather than in code, which is nice – but you still need to be aware of it as a regular task to perform. (And you need to make sure you’re aware of when there are time zone data updates…)

      Like

  25. Excellent article, and the point can be made for more than just time, but really for any ‘coordinate’ (for lack of a better word) that is relative to another, potentially changeable coordinate. The conference in your example takes place in the RAI, but in your example you store the address with the event. If the RAI moves to Rotterdam in the meantime, we need to update the address in the database. Would it be better to store ‘RAI (wherever that may be at the time of the conference)’?

    Of course, the example above would be solved when the address is an entry in a relational database or something, but the point remains.

    Like

    1. Yes, in an ideal world there’d be a canonical “reference value” with whatever levels of indirection are required – whether RAI is moved or renamed, for example. In the real world there are always limitations, of course – but it’s always a good idea to at least think about it.

      Like

  26. Good points here Jon. While working on a Greenfield project recently I decided to use local time and address to get the timezone and create a UTC time derived from that. As per your post this is not safe and is actually making me think about storing both local time.

    Timezone info, Timezone DB v) and UTC (derived from local + timezone info). I should have known better to store the local time as a snapshot and this new EU proposal is a good example why I should. At least we know what the user intended as a fall back.

    Thank you for the post.

    Like

  27. Hi Jon,
    thanks for such a detailed article.

    If I understood correctly, you have suggested in option 3 storing UTC time is optional and we can just be better off having just local time. I do understand and agree if local time is the time you have to show or use it then it makes perfect sense to capture that, However In case of business requirements like, send email notification with navigation details or venue just before the start of conference. How this can be done if you only have local time ? Notification service or process what ever you build should be aware of local time, this make sense if you have a server running in the same place and you have only one timezone where you do conference.

    But, when you think of at national level in America which has 3 time zones, just having a single local time does help in showing users what time is conference. But, once you want to automate things to send notifications with relative to time then what would suggest ?

    Currently, i have a team which is going through similar problem. Its a ecomm platform which allows users to select a store and pick up a delivery windows. At the moment all of the dates are stored based on what users selects. for example 04/22/2019 9:00 AM . This data is same regardless of store being in PT, CT or EST time zone. given this is an old system, the work flow is .. every store only sees there own orders for the day and someone in store in PT will check 7 AM and start packing orders for 9 AM, This work flow works perfectly fine regardless of store being in CT or EST time zone since the times are local to them.

    However, now we have systems which will create best truck routes 2 hours before the delivery windows. If you only have local times, how do you know all orders that are due in 2 hours. since current data only captures 04/22/2019 9AM for order in PT zone for store 1, and same date time for store 2 in CT and EST zone. give they are anywhere between 3 hours behind if you set your server in EST then PT order route will be calculated a hour before.. 2 hour window..

    My current understanding is, having a UTC time will help you go automate things with machines and no one really need to be aware of local time. A machine hosted in us-east could still process a notification with 2 hours before or after, send route data to trucks.

    I believe, having only local or UTC only decision should be left based on the context of the work being performed. I believe, if you have to automate things with time, then it make sense to time in UAC as Well. In my example of ecom delivery, I believe having both Local and UTC time is Important.

    Appreciate your thoughts on above usecase

    Like

    1. If you need to schedule emails, then yes, potentially it would be useful to store the UTC time as well, just for simplicity of querying. But it’s still only derived data… you could iterate over all the events and convert the local time to UTC each time you need it. You’re not losing information that way. Note that in the example scenario, the countdown timer doesn’t need to show local time, but it’s fine to do the conversion on-demand. For systems performing queries or scheduling, that could be too inefficient – but it’s worth mentally separating the information that’s inherently required from information you’re only storing for optimization.

      The point of the post is that one piece of advice that is dispensed as if it were universally true is “Just store everything in UTC and then you don’t have any time zone problems” – and that’s simply not right. As you say, it depends on the context – and that’s what’s always ignored when this advice is given. There’s an assumption that if you’ve got the UTC information, that’s all you ever need, and that assumption is based on a further assumption that time zone rules never change.

      Like

  28. Thanks Jon for your quick reply. If you store delivery time in local time, which could be PT time for store 1, CT time store 2 and ET in Store 3. If I mistaken, we should store delivery time with TImeZone Information in order to go back to UTC time later. I believe, if you store just the local delivery time without any timezone information then I dont see you can actually get back your true UTC time.?
    Does my understanding sounds correct?
    I guess at the moment, current system is just storing delivery time without any timezone information. Assumption is people in the local time will see there deliveries and orders either user or packaging team they never felt it was required. Since we want to do some sort of automation to calculate truck routes 2 hours before now things are looking little out of context…

    I am preference to have delivery time in local time and have a UTC derived time in system. So that automation or third parties can always rely on UTC time to do machine specific automatons. .. and Local time will keep things consistent ..

    Any thoughts?

    Like

    1. I don’t want to try to give advice for specific systems without more context, although I would say that you really really need to store the time zone if the value you’re representing is intended to be considered in one specific zone.

      Like

Leave a Reply to Fabian Wetzel Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s