4 ms·

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

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.
      - karmakaze 6y ago
        Docs aren't 100% explicit but it seems that it uses tzdata etc to convert to offset as necessary and stores offset--that wouldn't affect correctness as the conversion is done now not 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.

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.
    - karmakaze 6y ago
      Docs aren't 100% explicit but it seems that it uses tzdata etc to convert to offset as necessary and stores offset--that wouldn't affect correctness as the conversion is done now not 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.