Date-Time Analysis: SQL Analysis Chapter 7

Why this chapter matters

Time logic is a common source of reporting bugs, especially around time zones and period boundaries.

What you will learn

Truncate timestamps into day, week, and month buckets.
Apply interval arithmetic for retention and cycle-time metrics.
Handle timezone-aware analysis using PostgreSQL timestamp types.

Understand the core ideas

Date and time analysis in PostgreSQL requires clear assumptions about timezone, period boundaries, and row grain. Decide first whether your metric is event-time or report-time, then standardize timestamps accordingly. timestamp with time zone stores a specific instant, while rendering can vary by session timezone, so analysts should normalize windows explicitly. For weekly reporting, use consistent bucket logic such as date_trunc('week', occurred_at AT TIME ZONE 'UTC')::date when business definitions use UTC weeks. Mixing local and UTC assumptions is a frequent source of off by one day errors. Intervals should also be explicit: interval '7 days' reads better than arithmetic on extracted date parts and is less error prone. When validating period metrics, compare boundary-day samples manually to confirm events land in the expected bucket.

Retention and cycle-time calculations depend on matching the right event pairs at the correct grain. If each row in a source table is one support ticket event, derive ticket-level milestones before computing durations. For example, first create one row per ticket with opened and closed timestamps, then subtract to get elapsed intervals. Applying interval math directly on raw event streams often duplicates or mispairs events. In PostgreSQL, use AGE, subtraction of timestamps, or EXTRACT(EPOCH FROM ...) depending on output units needed for reporting. Keep bucket and duration calculations separate so each piece can be tested independently. A practical QA check is to pick five known records and verify their bucket assignment and duration by hand. Time logic becomes stable when timezone conversion, bucketing, and duration math are each deliberate and documented.

Key terms

timestamptz: PostgreSQL timestamp type that represents an exact instant with timezone-aware interpretation.
date_trunc: Function that truncates timestamp values to a specified precision such as week or month.
interval: A PostgreSQL duration type used for time arithmetic like adding days or hours.
period boundary: The exact start and end points that define inclusion in a reporting window.

UTC weekly opened and closed support tickets

Use tickets(ticket_id, opened_at, closed_at) with one row per ticket and timestamptz columns. Need weekly opened and closed counts aligned to UTC week starts, one row per week.

Create an opened_weekly CTE grouped by date_trunc('week', opened_at AT TIME ZONE 'UTC')::date to count opened tickets per UTC week.
Create a closed_weekly CTE grouped by date_trunc('week', closed_at AT TIME ZONE 'UTC')::date, excluding null closed_at values.
Combine week series with a full join on week_start so weeks with only opens or only closes are retained.
Use COALESCE for counts and order by week_start ascending to produce a complete trend table.

Result:

WITH opened_weekly AS (SELECT date_trunc('week', opened_at AT TIME ZONE 'UTC')::date AS week_start, COUNT(*) AS opened_count FROM tickets GROUP BY date_trunc('week', opened_at AT TIME ZONE 'UTC')::date), closed_weekly AS (SELECT date_trunc('week', closed_at AT TIME ZONE 'UTC')::date AS week_start, COUNT(*) AS closed_count FROM tickets WHERE closed_at IS NOT NULL GROUP BY date_trunc('week', closed_at AT TIME ZONE 'UTC')::date) SELECT COALESCE(o.week_start, c.week_start) AS week_start, COALESCE(o.opened_count, 0) AS opened_count, COALESCE(c.closed_count, 0) AS closed_count FROM opened_weekly o FULL JOIN closed_weekly c ON c.week_start = o.week_start ORDER BY week_start;

returns one row per UTC week with consistent boundaries.

A common misconception

Claim: Using date_trunc('week', timestamp) always matches business weeks automatically.

Correction: It matches PostgreSQL week truncation rules in the current context. You still must define timezone and boundary policy explicitly to align business reporting.

Lessons in this chapter

Time data typesDistinguish date, timestamp, and timestamptz behavior in PostgreSQL. Read the full guide →
Period bucketingUse date_trunc and casting patterns for consistent reporting windows.
Timezone correctnessConvert and compare timestamps with explicit timezone assumptions.
Retention interval logicMeasure elapsed time and lagged events with interval-aware SQL.

Study task

Generate a PostgreSQL weekly trend of support tickets opened and closed, aligned to UTC week boundaries.

Chapter checkpoint

Which PostgreSQL function is commonly used to bucket timestamps by month?

date_trunc('month', timestamp_column) is commonly used to bucket timestamps by month.

Date-Time Analysis