> The potential for timezone rule changes mean that future events specified in local timezones are fundamentally ambiguous with regard to any absolute timeframe. Thus, normalization of such events to UTC can result in errors.
Ok but you still dont use TIMESTAMP WITH TIME ZONE to store those either. For future events where you want to protect against time zone changes, you store the intended time which is what this thread is calling "wall clock time". I think you shouldn't use a timestamp object for such a time because it's not a timestamp at all, it's a human description of something that has a moving linkage to a timestamp. However, this makes being able to search and filter for these events more challenging if the database lacks functions to coerce these values into current UTC timestamps, or if you are trying to optimize for indexes that don't make use of these functions.
> Consider TicketMaster. They have a database of future events around the globe, sometimes years in advance.
Most of my datetime experience is from working at MLB.com with Ticketmaster servers in the early 2000s. We used Oracle and we stored the next season's worth of baseball schedules in straight UTC, in addition to storing the original "wall clock plus timezone" version of that time, after our first year online where we were stupidly storing in Oracle's default timezone which was of course EST, and on October 30th at 2 AM the whole site broke because in the perspective of some of our database queries time had moved one hour backwards.
It was not an option to store only "wall clock time" here because we needed to be able to search and sort these events in the database and Oracle did not have the calendaring / indexing capabilities to make it feasible to be coercing from wall clock to UTC on the database side. We were not too worried about timezones being changed within the US/Canada within the next year, as in the rare occurrence that this happened, we simply ran an UPDATE for the UTC timestamps in question based on their desired timezone. I notice the blog post we are talking about here makes no mention of future dates or the challenges in storing them accurately while still being able to query them efficiently; for future dates we had to store both the UTC and the wall clock data.
> Normalizing to UTC makes sense in so many cases that it can seem to be a silver bullet for all cases.
my disagreement with this blog post was first triggered in that it is an "You should always do X" kind of post. The word "future" doesn't even appear in the text of this post nor do the words "index" or "sort", which is a funny thing for an article that seems to assure the inexperienced reader that this is all you need to know about timestamps in databases.