Use date and timestamp functions to bucket events into consistent periods and calculate durations. Be explicit about timezone handling so trend metrics remain comparable across teams and runs.
- Define one reporting timezone before grouping by day or week.
- Use DATE_TRUNC for period buckets and age or interval math for durations.
- Keep inclusive and exclusive date boundaries explicit in filters.
Build period buckets that stay consistent
Northstar support leaders review ticket resolution trends weekly. If timestamps are stored in UTC but grouped in local time without conversion, day boundaries can shift and trend lines become misleading.
A stable approach is to convert to the reporting timezone first, then apply DATE_TRUNC('week', ...).
Measure durations with clear definitions
Cycle-time metrics need a clear start and end event. For fulfillment, that might be order_date to shipped_at. For support, it may be opened_at to resolved_at.
Exclude unresolved records intentionally or include them with NULL-safe logic, depending on how the metric is defined in policy.
- Use >= start_date and < next_period for clean boundaries.
- Store raw timestamps; format only in presentation layers.
- Document whether weekend hours are included in duration metrics.
Track weekly average fulfillment hours
orders includes order_date and shipped_at timestamps for Northstar ecommerce shipments.
- Filter to paid and shipped orders within the quarter.
- Convert timestamps to one reporting timezone and DATE_TRUNC to week.
- Compute AVG(EXTRACT(EPOCH FROM shipped_at - order_date) / 3600.0) grouped by week.
Common mistakes
- Grouping UTC timestamps by local date without conversion.
- Using BETWEEN with ambiguous end-of-day boundaries.
- Mixing date and timestamp types without explicit casting intent.
- Comparing durations before handling unresolved or null end times.
Try one
Why is a < next_period boundary often safer than <= end_date for monthly reporting?
It avoids missing late-night timestamps and prevents off-by-one boundary issues when time components are present.
Sources
- PostgreSQL 18 Docs: Date/Time Functions and OperatorsOfficial date, time, interval, and truncation behavior in PostgreSQL.