5 ms·

In long-lived systems (systems that run for many years) it's almost impossible to choose the "right data structures" for the ages. The sources and uses of your

In long-lived systems (systems that run for many years) it's almost impossible to choose the "right data structures" for the ages. The sources and uses of your data will not last nearly as long as the data itself. What to do about this? Two things: STORE YOUR TIMESTAMPS IN UTC. NOT US Pacific or any other local timezone. If you start out with the wrong timezone you'll never be able to fix it. And generations of programmers will curse your name. Keep your data structures simple enough to adapt to the future. Written another way: respect the programmers who have to use your data when you're not around to explain it. And, a rule that's like the third law of thermodynamics. You can never know when you're designing data how long it will last. Written another way: your kludges will come back to bite you in the xxx.

aserafini 6y ago
Sometimes storing in UTC is simply not correct. For example a shop opening time. The shop opens 10am local time, whether DST or not. Their opening time is 10am local time all year but their UTC opening time actually changes depending on the time of year!
- wvenable 6y ago
  I made that mistake early my career following this exact advice and I ended up with a lot things were randomly 1 hour off depending on when the record was created and the date entered.
- Benjammer 6y ago
  Totally. "Store everything in UTC" is just another flavor of "pick a timezone to store everything." In a lot of cases, you probably need to go ahead and just store the fully qualified date including timezone/offset for each record.
  - account42 6y ago
    Even storing offset or timezone might not be enough if what you really want is some future date and time at a particular location. Timezones do change, including the regions they cover. Still, for things that have already happended, storing them as a UTC timestmp is almost always the correct thing to do.
- karmakaze 6y ago
  The most interesting case of this I encountered was for photo 'timestamps' on a global sharing site. UTC was being used and I was proposing a change to local time. There was great debate as many drank the UTC juice and stopped thinking. It was when I showed them that we also have a 'shot at' location then proceeded to show Christmas eve photos showing the UTC time converted to the viewers local timezone (not always evening, not always Dec 24) alongside where the photo was taken. Just as in space-time a photo needs both a time and a place.
  - rurounijones 6y ago
    Sounds like the problem was images being uploaded with a timestamp without a timzeone, in which case neither solution would work.
    - karmakaze 6y ago
      The timezone could be inferred from uploader's geoip as a fallback. The problem was that even if the timezone was known at time of upload it was converted to UTC and lost when stored.
  - Joker_vD 6y ago
    For historical events, where the local time is important, the combination of "UTC timestamp" and "local time offset in effect at the moment the timestamp was taken" seems to be the choice. Allows you to easily learn what time the wall clock was showing at the moment.
    - karmakaze 6y ago
      Databases have support for a single type that encodes exactly like this. In postgresql a timestamptz shows as 'yyyy-mm-dd hh:mm:ss.123456+1234' but internally it's stored as UTC unixtime and tz offset.
      - Joker_vD 6y ago
        Doesn't it actually store the timezone's IANA name and uses the tzdata to do conversions? That implies slightly more work than storing just the effective timezone offset, but is probably more correct when it comes to the timestamps in the future.
- wtetzner 6y ago
  But a shop opening time is not a timestamp, so I think the original advice is still good. A timestamp is the time at which some event happened, which is different than a date/time used for specifying a schedule. For example, if you wanted to track the history of when the shop actually opened, it would make sense to store a UTC timestamp.
  - TheCoelacanth 6y ago
    > A timestamp is the time at which some event happened, which is different than a date/time used for specifying a schedule. Correct, but that makes this a rule with much more limited applications than many people are going to interpret it as.
  - account42 6y ago
    Yes, but scheduled times can look like timestamps. It might be tempting to store a date+time+location as just a UTC timestamps but timezones can and do change so the UTC timestamps for that scheduled time is not fixed.
  - Joker_vD 6y ago
    And the time offset that was in effect when the event happened, allows you to easily answer questions like "Did the shop open late, i.e. after 10 AM local time?".
  - dragonwriter 6y ago
    > A timestamp is the time at which some event happened, It's important to the advice to make explicit that the use of “timestamp” in that sense is intended, because “timestamp” is also in many contexts “the data type that combines date and time of day and, perhaps optionally, time zone information”. The application of “timestamps” in the latter sense is not limited to when they represent “timestamps” in the former sense.
- drvd 6y ago
  That is the difference between a clock reading and a timestamp.
zelly 6y ago
> STORE YOUR TIMESTAMPS IN UTC. NOT US Pacific or any other local timezone. What difference does it make if the timestamp includes the timezone? The UTC value can be recovered. In some applications the timezone is useful e.g. when intraday times matter.
- bluGill 6y ago
  Odds are that you forgot to record the timezone at all, and it didn't matter until you already have users in different timezones who have saved data and they don't remember which timezone they saved everything in. If you record the timezone you can convert. Even then, it is easier to use UTC just because everyone else does and so you can feed UTC into any third party library and it will work.
- swift532 6y ago
  Daylight savings might get in the way, especially if daylight savings rules change some time later.
OliverJones 6y ago
People ask "why UTC"? Good question. Here's why: You can always translate UTC to a local time in a given timezone. With IANA zoneinfo, you can do that correctly even for historical data in places where timezone rules changed in the past. You can always calculate elapsed times correctly by taking differences between UTC timestamps. With local times you can't. Because daylight time transitions. If you started with a local service, UTC lets you expand globally without explaining to your new customers why your timestamps are not in their timezones. Daylight time transition days. Because daylight time. Oddball daylight transition rules. Because Indiana USA, from the legislature that almost wrote a law declaring the value of π to be 22/7. Because almost everybody will understand your decision, even after you're gone.
combatentropy 6y ago
PostgreSQL stores it as UTC if the data type is "timestamp with time zone" --- https://www.postgresql.org/docs/current/datatype-datetime.html https://www.postgresql.org/docs/current/datatype-datetime.ht...
pjettter 6y ago
In our system, contracts have an expiration date/time. The only actors are Swedish. 12:59:59 must always be 12:59:59 on a certain date. It may never become 6:59:59 when someone in the company traveled to NY and prints out the contract.

aserafini 6y ago

Sometimes storing in UTC is simply not correct. For example a shop opening time. The shop opens 10am local time, whether DST or not. Their opening time is 10am local time all year but their UTC opening time actually changes depending on the time of year!

wvenable 6y ago
I made that mistake early my career following this exact advice and I ended up with a lot things were randomly 1 hour off depending on when the record was created and the date entered.
Benjammer 6y ago
Totally. "Store everything in UTC" is just another flavor of "pick a timezone to store everything." In a lot of cases, you probably need to go ahead and just store the fully qualified date including timezone/offset for each record.
- account42 6y ago
  Even storing offset or timezone might not be enough if what you really want is some future date and time at a particular location. Timezones do change, including the regions they cover. Still, for things that have already happended, storing them as a UTC timestmp is almost always the correct thing to do.
karmakaze 6y ago
The most interesting case of this I encountered was for photo 'timestamps' on a global sharing site. UTC was being used and I was proposing a change to local time. There was great debate as many drank the UTC juice and stopped thinking. It was when I showed them that we also have a 'shot at' location then proceeded to show Christmas eve photos showing the UTC time converted to the viewers local timezone (not always evening, not always Dec 24) alongside where the photo was taken. Just as in space-time a photo needs both a time and a place.
- rurounijones 6y ago
  Sounds like the problem was images being uploaded with a timestamp without a timzeone, in which case neither solution would work.
  - karmakaze 6y ago
    The timezone could be inferred from uploader's geoip as a fallback. The problem was that even if the timezone was known at time of upload it was converted to UTC and lost when stored.
- Joker_vD 6y ago
  For historical events, where the local time is important, the combination of "UTC timestamp" and "local time offset in effect at the moment the timestamp was taken" seems to be the choice. Allows you to easily learn what time the wall clock was showing at the moment.
  - karmakaze 6y ago
    Databases have support for a single type that encodes exactly like this. In postgresql a timestamptz shows as 'yyyy-mm-dd hh:mm:ss.123456+1234' but internally it's stored as UTC unixtime and tz offset.
    - Joker_vD 6y ago
      Doesn't it actually store the timezone's IANA name and uses the tzdata to do conversions? That implies slightly more work than storing just the effective timezone offset, but is probably more correct when it comes to the timestamps in the future.
wtetzner 6y ago
But a shop opening time is not a timestamp, so I think the original advice is still good. A timestamp is the time at which some event happened, which is different than a date/time used for specifying a schedule. For example, if you wanted to track the history of when the shop actually opened, it would make sense to store a UTC timestamp.
- TheCoelacanth 6y ago
  > A timestamp is the time at which some event happened, which is different than a date/time used for specifying a schedule. Correct, but that makes this a rule with much more limited applications than many people are going to interpret it as.
- account42 6y ago
  Yes, but scheduled times can look like timestamps. It might be tempting to store a date+time+location as just a UTC timestamps but timezones can and do change so the UTC timestamps for that scheduled time is not fixed.
- Joker_vD 6y ago
  And the time offset that was in effect when the event happened, allows you to easily answer questions like "Did the shop open late, i.e. after 10 AM local time?".
- dragonwriter 6y ago
  > A timestamp is the time at which some event happened, It's important to the advice to make explicit that the use of “timestamp” in that sense is intended, because “timestamp” is also in many contexts “the data type that combines date and time of day and, perhaps optionally, time zone information”. The application of “timestamps” in the latter sense is not limited to when they represent “timestamps” in the former sense.
drvd 6y ago
That is the difference between a clock reading and a timestamp.

zelly 6y ago

> STORE YOUR TIMESTAMPS IN UTC. NOT US Pacific or any other local timezone. What difference does it make if the timestamp includes the timezone? The UTC value can be recovered. In some applications the timezone is useful e.g. when intraday times matter.

bluGill 6y ago
Odds are that you forgot to record the timezone at all, and it didn't matter until you already have users in different timezones who have saved data and they don't remember which timezone they saved everything in. If you record the timezone you can convert. Even then, it is easier to use UTC just because everyone else does and so you can feed UTC into any third party library and it will work.
swift532 6y ago
Daylight savings might get in the way, especially if daylight savings rules change some time later.

OliverJones 6y ago

People ask "why UTC"? Good question. Here's why: You can always translate UTC to a local time in a given timezone. With IANA zoneinfo, you can do that correctly even for historical data in places where timezone rules changed in the past. You can always calculate elapsed times correctly by taking differences between UTC timestamps. With local times you can't. Because daylight time transitions. If you started with a local service, UTC lets you expand globally without explaining to your new customers why your timestamps are not in their timezones. Daylight time transition days. Because daylight time. Oddball daylight transition rules. Because Indiana USA, from the legislature that almost wrote a law declaring the value of π to be 22/7. Because almost everybody will understand your decision, even after you're gone.

combatentropy 6y ago

PostgreSQL stores it as UTC if the data type is "timestamp with time zone" --- https://www.postgresql.org/docs/current/datatype-datetime.html https://www.postgresql.org/docs/current/datatype-datetime.ht...

pjettter 6y ago

In our system, contracts have an expiration date/time. The only actors are Swedish. 12:59:59 must always be 12:59:59 on a certain date. It may never become 6:59:59 when someone in the company traveled to NY and prints out the contract.