Hacker News Reader

Comment by aserafini | Hacker News Reader

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!

View on HN

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.

> 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.

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.

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?".

> 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.

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.

Sounds like the problem was images being uploaded with a timestamp without a timzeone, in which case neither solution would work.

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.

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.

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.

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.

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.

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.

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.

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.

That is the difference between a clock reading and a timestamp.

Reply on news.ycombinator.com