Tracking localtime but storing GMT problem

Question

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?

Answer 1

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.