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.

▲

wodenokoto an hour ago | parent | next [-]

So how are you solving the example in the article where they join on timestamps? Read both tables from the DB into the application?

	▲	beybol 5 minutes ago \| parent [-]
		I read raw data (UTC timestamps) from the db and timezone for user profile and calculate it on the fly.

▲

threatofrain 39 minutes ago | parent | prev | next [-]

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.

▲

ahoka an hour ago | parent | prev | next [-]

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.

▲

GJim an hour ago | parent [-]

Why would the dates need rewriting?

	▲	sokoloff an hour ago \| parent [-]
		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.

▲

GJim an hour ago | parent | prev [-]

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.