alt Hacker NewsI 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.
Replies
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.
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.
So how are you solving the example in the article where they join on timestamps? Read both tables from the DB into the application?