I’ve been building Vivoldi, a multilingual SaaS for link management and analytics, and one of the hardest engineering challenges we faced was timezone conversion. I wanted to share some lessons learned that may help others running global systems.
1. Aggregation tables need hour-level precision To support accurate timezone conversion, every aggregated record must include a timestamp with at least the hour. Minutes/seconds are not strictly necessary, but without the hour, conversions break. This significantly increases the size of aggregated datasets, so query optimization becomes mandatory.
2. Standardize server time, convert on the frontend All database records are stored in KST (Korea Standard Time), since that’s where our ops team is based. On the frontend, data is always converted into the user’s local timezone. This makes operations easier for admins while still providing the right context for global users.
3. Query tuning is not optional Timezone conversion adds unavoidable complexity, so database tuning is critical. Poorly optimized queries will choke when datasets grow large. Indexing, partitioning, and minimizing unnecessary conversions made a huge difference in our case.
I’d love to hear how others have handled timezone issues in their systems. Do you normalize everything to UTC, or do you also keep server-local time for operations?