Comment by beybol
12 hours ago
I don't rely on time calculations at the database level. I handle them in the application based on UTC time stored in the database and the user's time zone. This shifts the problem to the application code, where I can control it precisely and make conscious decisions about how to handle specific business requirements, such as when a day ends or how to deal with events across different time zones. It also makes it possible to properly test all cases with unit tests.
Ruby on Rails would agree with you. I've been working in web development for a decade now, across a couple of languages and frameworks, and I've only ever stored UTC. I currently work a lot with time-series data, and I'd rather not know what hack makes it possible to track events that occur during a DST transition.
So how are you solving the example in the article where they join on timestamps? Read both tables from the DB into the application?
I read raw data (UTC timestamps) from the db and timezone for user profile and calculate it on the fly.
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.
1 reply →
We can think of the split between DB and application in terms of DX or code, or we can think of it in terms of colocation of data and compute. The latter case will be compelling sometimes.
This is the only way. To do otherwise smacks of poor programming practice and is very often a sign the coder doesn't habitually consider the world outside their own timezone.
It only works backwards (though you can probably get away with it if your future dates are not too far forward). There are ~10 tzdb updates a year due to rule changes and if you bake a date on the other side of one to UTC too early then your app is going to have incorrect datetimes
I've seen a product where they used UTC to store opening hours. They had to rewrite all dates via a script twice a year.
I don't know the exact use case, but it sounds weird. If UTC is the single source of truth, then when you display the data and want to change some business assumptions, you only need to change the application logic. The underlying data remains unchanged.
One scenario I can imagine is when you want to store the results of business calculations in the database. In that case, once the algorithm changes, you may also need to update the stored data. This may be necessary for performance reasons.
In all other cases, calculating the result on the fly solves the problem and does not require a database update when the business rules change.
Why would the dates need rewriting?
I suspect they meant more generally “the date&time field”, but a store in Boston that opens at 7:30 AM and closes at 7:30 PM Eastern time (ET) closes on different UTC date than it opens when ET is EST but opens and closes on the same date when ET is EDT, so it’s plausible that the dates actually needed to be updated.