1. 15

  2. 7

    Wow, no, the better way is to store and retrieve nothing but UTC. Do anything that isn’t UTC at the presentation layer (since 90% of the time the only relevance of time zones is to display local time to a user) or rarely the application layer (e.g., for a calendar event that has a constant local time). Never mention time zones to the database. At least I’ve learned that’s the only way I can remain sane when dealing with time zones!

    1. 2

      Generally, I agree with you. I am a huge zealot for UTC.

      Here’s a riddle for you. Let’s say your building an analytics system that shows aggregate data per day. You can’t store the raw data points as rows; you can only afford to store daily roll-ups.

      But, your customers are spread all over the world, so the notion of a ‘24-hour day’ shifts depending on local timezone. For example, the 24-hour day in Iceland is midnight UTC to midnight UTC, but the 24-hour day in New York is UTC 8pm to UTC 8pm (right now – depends on DST).

      How do you get away with “only storing UTC” then?

      Answer: you cheat. You call the day “2016-08-01”, even though it so happens to store a 24-hour period spread across 2016-07-31 @ 8pm - 2016-08-01 @ 8pm UTC for New York customers, but midnight to midnight for Iceland customers. Even then, one’s mind bends at the implications. And then, you still have to deal with DST, and 23-hour days! And India, and 30-minute offset timezones! And Nepal, with a :45 offset! And sudden – sometimes politically-inspired – timezone changes, that happen <48 hours notice in countries with no reliable news services! Ugh!

      1. 1

        That is a great example of what I’m saying to put at the application layer. What you’re calling a “day” is clearly a concept that makes sense only based on some obscure and ever-changing user requirements. None of that stuff can change the fact that a data event occurred at a certain instant in time (i.e., at a UTC timestamp).

        In this example, when you do a roll-up for a “day”, the definition–and name–of that “day” is determined entirely by the roll-up algorithm. If it wants to name those “days” as if they were dates (“2016-08-01”), then fine, but don’t ever confuse that (in your mind, or–much worse–in the database) with an actual timestamp. Otherwise chaos and madness ensues.