Comment by WorldMaker | Hacker News Reader

Comment by WorldMaker | Hacker News Reader

Parent Full thread

I've seen too many ways storing everything as UTC goes wrong/gets confusing. Round-tripping at least UTC offset (which unfortunately Postgres `timestamptz` in this case does not do and is effectively the same as storing as UTC) gives you more debugging tools for events in the past and more opportunities to do the right thing for future events in worst cases (strange, unexpected DST shifts). The best option is if you can also round trip exact time zones such as IANA strings like "America/Los_Angeles". Even fewer databases support that natively right now (as a single optimized column).

When UTC offsets roundtrip you can do all your date math as if everything was in UTC, but still not lose information from the user about what time they thought an event occurred at or might next occur at.

t depends on the system. Keeping a time zone alongside a UTC timestamp may be useful in some cases.

For example, if your system is supposed to remind a user to do something, such as take a pill, and the user changes time zones while travelling, you may want the reminder to occur at 9:00 local time wherever they currently are. In that case, storing the original time zone together with the event may not be necessary, because the relevant time zone is the user's current one.

Everything depends on the context. For me, however, there is no doubt about one thing: I store timestamps in UTC. Whether I also store a time zone, and where I store it, depends on the system and its business requirements.

Keeping a UTC offset is two extra bytes and with a UTC offset you can do all of your time comparisons and time math as if everything was in UTC, but you have extra debugging information in the UTC offset. It's still sometimes useful to store a time zone as offsets to timezones certainly are not 1:1 (esp. with DST math), but some places like logs and past dates UTC offset is sufficient and you don't even need to store timezone.

Postgres doesn't a native type that supports UTC offsets, but some other databases do and it is extremely useful. At this point in my career, I would never choose UTC storage over UTC Offset storage.

Reply on news.ycombinator.com