Resources · 82
Reliable reports: time zones, cutoffs and late events
Avoid shifted reporting days, duplicate counts and inconsistent comparisons between exports and dashboards.
· 3 min
What this guide helps achieve
- Describe instants and business days
- Define period boundaries
- Handle late arrivals
- Reconcile equivalent scopes
- Test reconstruction
Quick check
- Is converting everything to UTC enough?
- Why does a total change after export?
- Is a late event a duplicate?
Step-by-step method
- 01
Describe instants and business days
Separate event, ingestion and modification timestamps from business dates. A UTC instant alone does not define a store’s trading day. Retain a named business time zone when needed. PostgreSQL converts timestamps with time zone to UTC without retaining the original zone; store that zone separately if required.
Deliverable: date and time-zone dictionary.
- 02
Define period boundaries
Use an inclusive start and exclusive end. Calculate boundaries in the business time zone before conversion. Test daylight-saving changes, month ends and dates without times. A local day does not always have the same duration; do not assume every day lasts twenty-four hours.
Deliverable: period-assignment examples.
- 03
Handle late arrivals
Decide which report may change, for how long and with what label. Retain ingestion time and identifiers to distinguish delay from duplication. Do not silently overwrite a distributed report: provide a dated snapshot, revision or correction suited to its use.
Deliverable: revision policy and discrepancy log.
- 04
Reconcile equivalent scopes
Compare sources using identical periods, business states and cutoffs. Separate created, paid, cancelled and refunded orders. Examine individual records near day boundaries before concluding that collection lost data.
Deliverable: record-level discrepancy reconciliation.
- 05
Test reconstruction
Replay an interval containing a clock change and late events. A reconstruction at the same cutoff should explain any differences. Display time zone, freshness and provisional or closed status beside results.
Deliverable: reconstruction test and report legend.
Reusable worksheet
Complete with your authorised observations. These fields are a working template, not observed results.
| Field | Information to record |
|---|---|
| Calendar | Named zone, week start and period boundaries |
| Timestamps | Event, ingestion and modification times; precision and origin |
| Cutoff | Snapshot time, revision window and late-arrival handling |
| Reconciliation | Included states, identifiers and explained differences |
Worked example
Illustrative situation
Fictional example: two teams compare Monday sales using UTC and Europe/Paris respectively.
Decision and expected evidence
Reconciliation isolates orders at day boundaries, then adopts a common business period and identical cutoff.
Distinguish the mechanisms
| Mechanism | Purpose | Check or limitation |
|---|---|---|
| Business date | Assign activity to a calendar | Is not an instant without a convention |
| Event time | Measure when activity occurred | Can arrive late or use an inaccurate clock |
| Ingestion time | Monitor pipeline freshness | Is not necessarily the activity date |
Management indicators
| Indicator | What it measures | First action |
|---|---|---|
| Ingestion delay | Time between activity and pipeline availability | Define an appropriate provisional window |
| Period revisions | Changes between comparable dated snapshots | Separate delays from corrections |
| Boundary discrepancies | Records assigned to different days | Correct the time convention |
Common pitfalls
- Is not an instant without a convention
- Can arrive late or use an inaccurate clock
- Is not necessarily the activity date
Frequently asked questions
Is converting everything to UTC enough?
No. UTC helps compare instants; days, weeks and cutoffs still require a business time zone and calendar.
Why does a total change after export?
New events, corrections or business-state changes may be included. Check cutoff and revision policy before comparing.
Is a late event a duplicate?
No. Check its identifier and content; ingestion delay does not establish repetition.
Official references
References consulted: . The method and worksheet propose checks to adapt to your context; they do not constitute certification.
Continue exploring
Related analysis
