Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Timezone bug in daily partitions

Pipelines & scenarios · Production Scenarios

Timezone bug in daily partitions

Mediumpipelines-50
scenariotimezoneutcdaily-partitionsdst

Question

Daily numbers are off around midnight. You suspect a timezone problem. How do you investigate and fix it?

Solution

When daily totals are wrong only near midnight, the pipeline probably cuts the day at a different boundary from the one the business uses. Events between, say, 18:30 and 24:00 UTC belong to the next day in India, and land in the wrong partition. Find which clocks are involved, then fix the rule and recompute.

Investigate

  • What timezone is the raw event timestamp in? Check the source system and its documentation, and look at a few known events (an order you can find in the app, with a time you know). Does the stored time match the real time, or is it off by a fixed amount such as 5 hours 30 minutes?
  • Is the timestamp stored with a timezone (a timestamptz or ISO string with offset), or as a plain local time with no zone? Plain times are the usual cause.
  • How is the partition or date column derived? DATE(event_ts) uses whatever zone the session or the engine uses. In some engines that is UTC, in others the server's local zone, so the same query can give different dates in different environments.
  • Which day does the business mean? Revenue for "March 1 India time" is 18:30 UTC on Feb 28 to 18:30 UTC on March 1.
-- compare the same events under two day definitions
SELECT DATE(event_ts) AS utc_day,
       DATE(event_ts AT TIME ZONE 'Asia/Kolkata') AS ist_day,
       COUNT(*)
FROM events GROUP BY 1, 2 ORDER BY 1, 2;

A mismatch in the counts near the cut shows the problem directly.

Fix

  • Store and process timestamps in UTC, with the zone explicit. Convert to local time only when computing business dates.
  • Derive a separate business_date column in the zone the business uses, using a named zone (Asia/Kolkata), not a fixed offset. Named zones handle daylight saving time for countries that have it, such as the US or Europe, where a day can have 23 or 25 hours.
  • Partition by the date that queries filter on, or by UTC date with a business-date column used for reporting. Either way, write the convention down.
  • Recompute the affected partitions, since earlier data was bucketed wrongly. Use an idempotent overwrite per day.

Prevent it

Add tests with events at 23:59 and 00:01 in the business zone and across a DST change. Put the convention in the data dictionary, and have one shared function or model produce business dates, so every pipeline uses the same rule.

PreviousNext