Three things I got wrong about timezones
Every engineer knows to store timestamps in UTC. Almost nobody knows what to do afterwards, which is where the bugs live.
One: "today" is a political question
A user in Auckland asks for today's orders. Their today starts fourteen hours
before mine. If your query is WHERE created_at >= current_date, you are
answering your own question, in your own timezone, on behalf of someone else.
The fix is boring and total: store the user's zone, compute the local day boundary, convert once, and index the column you filter on.
Two: weeks do not start on Monday everywhere
Reports that group by week are wrong in at least one country, and it is usually a country with a large customer. If your product has weekly aggregates, decide the rule explicitly and write it down.
Three: elapsed time is not clock time
"Three hours ago" is not the same as "three hours ago in wall-clock terms" across a DST transition. For durations use monotonic clocks and convert only for display; for schedules use a timezone-aware library and stop doing arithmetic on strings.
A timestamp without a timezone is not a time, it is an opinion.
I have written that sentence in three code review comments this month, which suggests the problem is not going away.