ORIGINAL TECHNICAL PRACTICE
Choose the right time for a reporting bucket
A report counts daily orders across several countries. Which timestamp and timezone choices must be explicit?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Decide whether the report groups by a global reporting day or each order’s local business day. Event time, processing time and settlement time can all be valid for different questions.
Work through it
- Store or preserve enough timestamp information to identify the actual instant and the applicable business timezone. Avoid assuming a fixed offset handles daylight-saving changes.
- Use half-open interval filters, including the start and excluding the next boundary, to avoid double-counting records at midnight.
- Test daylight-saving transitions, events arriving late and records exactly on a boundary. Document how restatements and incomplete recent buckets are handled.
Tradeoffs to discuss
- Local-day reporting is intuitive for operations but complicates cross-region comparisons. UTC reporting simplifies a global timeline while shifting some local activity into a different date.
Common pitfalls
- Casting to a date before applying the intended timezone can move events into the wrong bucket. A day is not always exactly24 elapsed hours in a local timezone.