How do you handle timezones for a daily global sales report?
This tests your understanding of time data modeling and business requirements. A good answer stores events in UTC with a timezone identifier, then converts to the business's chosen 'day' at query time. A red flag is storing local time without context.
What's really being asked
Your understanding of data modeling for time, the separation of business and technical concerns, and the trade-offs in designing data pipelines for global operations. The interviewer is checking if you can establish a robust, unambiguous source of truth that serves business needs flexibly.
The full answer
A great answer has four parts. First, clarification: ask stakeholders to define "a day." Is it based on the company's HQ timezone (e.g., PST), a financial center (e.g., EST), or a simple UTC day? The technical solution depends on this business decision. Second, storage strategy: the best practice is to store two key pieces of information for every event: the precise timestamp in UTC (e.g., using a timestamp with time zone data type) and the IANA timezone name of where the event occurred (e.g., Europe/Paris). Third, querying strategy: perform all aggregations and date truncations at query time. The reporting layer takes the UTC timestamps and converts them to the business-defined "day." For a PST business day, a query might look like: GROUP BY DATE(event_timestamp AT TIME ZONE 'America/Los_Angeles'). Fourth, explain why this is robust: it allows you to answer the primary question and future questions like "What were sales during local business hours (9am-5pm) in each country?"
The mistakes people make
A major red flag is suggesting storing only local timestamps. This creates ambiguity, especially around Daylight Saving Time changes. For example, 2023-11-05 01:30:00 happened twice in many US timezones. Another mistake is storing just the offset (-07:00) instead of the IANA timezone name (America/Los_Angeles), as offsets don't account for DST. A candidate who defines the "business day" as UTC without discussion shows a failure to consider business requirements. Finally, suggesting destructive transformations on ingest, like converting all times to PST and discarding the original UTC timestamp, is a poor practice as it's irreversible and limits future analysis.
What usually comes next
Expect questions like, "How would your database schema look for the sales event table?" (Answer should include utc_timestamp and event_timezone columns). Or, "What if the report needs to run in under a second? Your query-time conversion sounds slow." (Discuss pre-aggregation, materialized views, or OLAP databases). They might also ask, "How do you get the user's timezone reliably?" (Discuss client-side detection vs. user profiles vs. Geo-IP).
A concrete example
A sale happens in Berlin on March 31st at 01:30 local time (CEST, UTC+2). At the same instant, a sale happens in New York (EDT, UTC-4). The correct storage for both events is the UTC timestamp March 30th, 23:30:00Z. The first event record includes event_timezone = 'Europe/Berlin', the second has event_timezone = 'America/New_York'. If the business day is defined by US Eastern Time, the Berlin sale is counted as part of the March 30th report. This storage model supports any business definition of a "day" without losing information.
Interview question
For a global sales report, what is the most robust strategy for handling timezones to define and aggregate daily sales?
- a.Store each sale's timestamp in its local time, assuming the reporting tool will correctly interpret the "day."
- b.Store event timestamps in UTC and include only the UTC offset (e.g., +02:00) for the event's location.
- c.Store all sales timestamps in the company's headquarters timezone and use that as the standard "business day."
- d.Store event timestamps in UTC along with the IANA timezone name of the event's origin, then convert to the business-defined "day" during query time.Correct
Why? this is the answer
The correct approach stores precise UTC timestamps and IANA timezone names, creating an unambiguous source of truth. This allows flexible definition of a "business day" at query time, unlike storing local times or just offsets which introduce ambiguity.
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