How do you handle timezones for a global daily sales report?
This tests your ability to translate ambiguous business needs (a "day") into a robust data model. First, clarify the business definition of a day. Then, store all event times in UTC and convert to the target timezone at query time for reporting.
What's really being asked
This question tests your ability to handle ambiguity in business requirements and translate them into a concrete, scalable data model. The interviewer is looking for a senior engineer who clarifies requirements before implementing a solution. They are evaluating your understanding of data storage best practices (UTC everywhere), the limitations of timestamps alone, and how to design a system that can answer future, unstated business questions.
The full answer
First, clarification. Ask what "a day" means for the business. Is it a rolling 24-hour window? Is it midnight-to-midnight in a single timezone like UTC or the company's HQ (e.g., 'America/Los_Angeles')? Or is it midnight-to-midnight in the local timezone of each transaction? The answer dictates the entire technical approach.
Second, storage strategy. State the best practice: store all timestamps in a timezone-aware format, normalized to UTC. A TIMESTAMP WITH TIME ZONE data type or a 64-bit Unix timestamp (milliseconds since epoch) are excellent choices. This creates a single, unambiguous source of truth.
Third, required event data. To enable flexible reporting, especially for local-day analysis, you must store more than just the UTC timestamp. The event record must include the IANA timezone name of where the event occurred (e.g., 'Europe/Berlin'). Storing just the UTC offset (e.g., '+01:00') is brittle because it doesn't account for Daylight Saving Time changes.
Fourth, querying strategy. Explain how to generate the report based on the clarified requirement. For a report based on the HQ's timezone, the query would be ... GROUP BY DATE(CONVERT_TZ(event_utc_timestamp, 'UTC', 'America/Los_Angeles')). For a report based on the event's local day, it would be ... GROUP BY DATE(CONVERT_TZ(event_utc_timestamp, 'UTC', event_iana_timezone)).
The mistakes people make
Failing to ask for clarification on the definition of a "day." This is the biggest red flag, showing a lack of business sense.
Suggesting storing timestamps in local, non-UTC timezones in the database. This makes cross-region aggregation nearly impossible and error-prone.
Confusing a timezone (like 'America/New_York') with a fixed offset (like EST/-05:00). A fixed offset does not handle DST, leading to incorrect data for half the year.
Assuming the UTC timestamp alone is sufficient for all reporting needs. This is true only if the business agrees to run everything on a UTC calendar, which is rare.
What usually comes next
"How would you handle backfilling this data if we were only storing local timestamps before?" (Discuss a painful migration script, potential data loss, and making educated guesses about DST based on the date).
"What if the database doesn't support timezone conversions?" (This pushes the logic into the application layer, which is less efficient. You'd need a robust library to handle IANA timezone conversions before grouping).
"Our sales team in Sydney complains the report cuts off their day early. How do you fix it?" (This is a prompt to build a user-facing feature that lets them select their desired timezone for the report).
A concrete example
A sale happens at 2023-04-10 01:00:00 in Berlin ('Europe/Berlin'). At that time, Berlin is in CEST, which is UTC+2. The UTC timestamp stored in the database is 2023-04-09 23:00:00. The event record should be { "sale_id": 123, "amount": 99.99, "event_timestamp_utc": 1681081200000, "event_timezone": "Europe/Berlin" }. If the business report runs on a US Pacific Day ('America/Los_Angeles', UTC-7), this sale occurred on April 9th. If the report is based on the local Berlin day, it occurred on April 10th. Storing both UTC time and the local timezone allows for both calculations.
Interview question
To generate a sales report grouped by each transaction's local calendar day, what is the minimum required information to store for each event?
- a.The event's timestamp stored in the local time of the transaction.
- b.The event's timestamp in UTC and the IANA timezone name of the event's location (e.g., 'America/New_York').Correct
- c.The event's timestamp stored in UTC.
- d.The event's timestamp in UTC and its UTC offset at that moment (e.g., '-05:00').
Why? this is the answer
To correctly group by local day, you need both the unambiguous UTC timestamp and the IANA timezone, which handles Daylight Saving Time rules. Storing only the UTC offset is brittle as it doesn't account for DST changes.
Just read this? Test yourself on what you have been reading.
Read the original → tinybird.co
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data modeling — each one lists the topics its interview covers.
See open roles