0

We have a date time policy that date time values are stored in GMT (ISO-8601) with offset. However, users tend to work in localtime. When you store data in the database in GMT, you lose the timezone that the datetime was originally created in. You might say "Well, just apply the client timezone to the date being read from the stored datetime in GMT." The problem is, if the client stored the data when it was in standard time, but now when they query the data it is daylight savings, the actual stored datetime will be offset by an hour to account for the current timezone. How do we ensure that when the queried datetime is displayed, that it is displayed in the original timezone? Is the best practice for this to basically also store the timezone that was used when the datetime was created since we can't reconstruct the original local time with the stored offset?

GregH
  • 12,278
  • 23
  • 73
  • 109
  • 1
    "The problem is, if the client stored the data when it was in standard time, but now when they query the data it is daylight savings, the actual stored datetime will be offset by an hour to account for the current timezone." Not if you apply the client time zone *to the value you're formatting*. Note that users don't change time zone when their time zone goes between standard time and daylight time. I'm in the Europe/London time zone and right now the UTC offset is +0. In the summer, I'll still be in the Europe/London time zone and the UTC offset will be +1. – Jon Skeet Jan 28 '22 at 16:08
  • 1
    Note that if you're storing *future* values in UTC, you could be storing up trouble for yourself anyway: see https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ – Jon Skeet Jan 28 '22 at 16:09
  • 1
    But fundamentally, "adjust the given UTC instant to a user's time zone" is a fairly easy thing to get right - and if you provide the code you're using that gets it *wrong*, we should be able to help you fix it. – Jon Skeet Jan 28 '22 at 16:09
  • @JonSkeet regarding *storing future values in UTC*, the problem isn't UTC, it's the conversion to UTC, right? – FObersteiner Jan 28 '22 at 16:13
  • 1
    @MrFuppes: Yes. It's the assumption that you can convert a future local time to UTC now, then apply the reverse conversion later and get back to the original local time. There are "obvious" potential issues with ambiguities and skipped times, but the more subtle one is if the offset you predict now isn't the same as the one you know later, e.g. because of laws around DST changing. Fundamentally, if you store "all the data the user has provided" (local time, time zone or location) and potentially derive extra data for efficiency (e.g. predicted UTC instant) you can fix it later. – Jon Skeet Jan 28 '22 at 16:16
  • 1
    @JonSkeet ok thanks for destroying my wonderful UTC-world ^^ Seriously, that problem wasn't clear to me before. And it's definitively relevant to this question as well. – FObersteiner Jan 28 '22 at 16:25
  • @JonSkeet However since the value is stored in GMT, you lose the offset (always +0000), so the only thing you can do is apply a timezone to apply the timezone offset. You either have to apply the current local timezone to the datetime or know the original timezone. Maybe I'm answering my own question here. I guess then if you don't store it in GMT but store it in localtime (with the offset) then I am guaranteed to always show the "original" time regardless of the user's current timezone. – GregH Jan 28 '22 at 16:27
  • 1
    @GregH: Yes, if you store the local time and offset then you can always display that original local time. Is that always *actually* what you want though? If the user was originally in London but is now in San Francisco, do you want to display "the local time in London" or "the local time in San Francisco"? Do you understand how the DST part is a red herring, or that if you *are* seeing DST applied incorrectly, that's probably an implementation bug rather than an inherent bug in the approach? It would really help if you could provide more context and the code that's failing. – Jon Skeet Jan 28 '22 at 16:29
  • Timezone names are also another thing that has confused me. Is the timezone name (in the case of US Pacific) "Pacific Daylight Time", "PDT", or "Pacific Time" (which is ambiguous depending on if daylight or standard time applies)? – GregH Jan 28 '22 at 16:33
  • @JonSkeet, this is really a thought experiment at this point. There is no working code. Trying to work through the problem before anything is coded. In your example, if the person is now in San Fran, I would want the London time to show. I don't think Standard/Daylight time is a red herring since that's the main use case I'm considering. The user stores a datetime. They aren't even aware of timezones. Just want that original datetime to be returned when they look it up. That's why I think storing the datetime with the offset solves this. However, this goes against the concept that – GregH Jan 28 '22 at 16:40
  • datetimes should always be stored in GMT. I guess we should say "generally stored in GMT" and not "always". – GregH Jan 28 '22 at 16:42
  • 1
    "I don't think Standard/Daylight time is a red herring since that's the main use case I'm considering" - yes, it is, because of the details in my first comment. You don't adjust a UTC instant to a local time by saying "what's the current UTC offset" - you do it by saying "what was the offset at that UTC instant" (in that time zone). That way it doesn't matter what the *current* offset is at all. – Jon Skeet Jan 28 '22 at 16:44
  • 1
    @GregH - I'm still confused - are you recording present/past timestamps of events that are occurring or have occurred? Or are you storing future dates and times that something *will* occur? Also, some of this is already covered in [this old post](https://stackoverflow.com/a/2532962/634824) – Matt Johnson-Pint Jan 28 '22 at 17:19
  • @MattJohnson-Pint - Could be past, present, or future timestamps. Trying to come up with a general policy for how we store datetime values. It just seems like storing date time values with the offset provides more information and fidelity than storing them in UTC without paying any price. That is a good post by the way. – GregH Jan 28 '22 at 17:27

1 Answers1

0

From your post:

The problem is, if the client stored the data when it was in standard time, but now when they query the data it is daylight savings, the actual stored datetime will be offset by an hour to account for the current timezone.

That should never happen, as the offset to apply is the one based on the timestamp stored - not whatever happens to be current when you run the query.

But also, from your comment:

Could be past, present, or future timestamps. Trying to come up with a general policy for how we store datetime values.

Having one policy for everything can be problematic, as not all dates and times have the same context. Differentiating between past and future is important.

  • For present or past timestamps, you can store any of the following:

    • A UTC-based date and time only, such as 2022-01-28T18:41:02Z. This a good option when you don't care about the original time zone. For example, recording the time someone visited your website in the web server's activity logs.

    • A local-based date, time, and offset, such as 2022-01-28T10:41:02-08:00. This works because at the time you store it, you can know for certain that the offset is correct for that point in time. You can easily convert back to UTC, or to any other time zone. This is a good option when both the original local time and the exact point in time matter. For example, recording the time that an hourly employee punches in for work at a physical location.

    • Either of the above options, supplemented with a time zone identifier (such as America/Los_Angeles), which can be stored either per timestamp or in some associated single value (such as a user's or location's information). This is a good idea if it is possible to edit a recorded value. For example, say in the previous example, the employee forgot to punch in when their shift started, so their manager later corrects the time. If the employee is working overnight at the time of a DST transition, it's possible their new offset is different than the old one, and you'll need a time zone identifier to recompute the correct new offset.

  • For future date and time values, you cannot know for certain what the offset will be. You may be able to take an educated guess based on what the current offsets and DST rules are, but the further out that guess is - the more likely something could happen between now and then. Governments around the world often fiddle with their time zone and daylight saving time policies. Sometimes, some of them don't even give enough notice for you to work that into your programming.

    Thus, for future dates and times, store values on the basis that the user provides them, and store a time zone identifier when applicable.

    • For example, if I am scheduling a meeting on a specific date and time in the future, I would store 2030-01-01T07:00, America/Los_Angeles

    • Or if the meeting is recurring at the same time every day, I would just store 07:00, America/Los_Angeles.

    • Sometimes, a time zone is not applicable. For example, the alarm on my phone goes off at the same local time even when I travel. I store 07:00 and nothing more. No conversions to UTC, no DST adjustments, nothing but the original information.

Lastly, I'll say that you should avoid applying time zone conversions when time is not applicable. For example, a birthday of 1976-08-27 is just a date. It should not be stored or represented as 1976-08-27T00:00 or 1976-08-27T00:00Z or 1976-08-27T00:00-08:00. Storing it with time can lead to invalid time zone conversions that shift the date by a day in one direction or the other. If the language/platform you're using doesn't give you a data type for working with simple dates, then be certain to ignore the time portion of such values and watch out for accidental time zone conversions.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • Thanks...all good input. Your comment "That should never happen, as the offset to apply is the one based on the timestamp stored - not whatever happens to be current when you run the query."...this assumes the offset or the timezone is stored with the associated date time value. If it were stored in UTC then there would be no way to know the exact localtime unless additional information were stored with it. – GregH Jan 29 '22 at 01:25
  • If you have the UTC value stored, and you also have the time zone identifier known, you can always convert from UTC to that time zone to get back the exact local time. In that conversion, it's still the recorded UTC-based timestamp that is used to determine which offset is to be applied. It's never just the one that is current at the time of the query. – Matt Johnson-Pint Jan 29 '22 at 01:35
  • As an example, if I stored `2021-07-01T12:00Z`, and I know the time zone is `America/Los_Angeles`, then I can use the time zone database to convert to `2021-07-01T05:00-07:00` (Pacific Daylight Time). The result is always the same, even if I do the conversion today, which happens to be in Pacific Standard Time (-08:00). – Matt Johnson-Pint Jan 29 '22 at 01:39