DATE_TRUNC Purpose

Technical Explanation

DATE_TRUNC truncates a timestamp or date to a specified precision (year, month, day, hour, etc.), effectively rounding it down to the beginning of that unit. It's essential for aggregating data by time periods.

Syntax

DATE_TRUNC('unit', timestamp_or_date)

Available Units

Unit	Truncates To	Example
century	Start of century	2026-01-01
decade	Start of decade	2020-01-01
year	January 1st	2026-01-01
quarter	First day of quarter	2026-01-01
month	First day of month	2026-04-01
week	First day of week (Monday)	2026-03-30
day	Midnight of that day	2026-04-04
hour	Top of the hour	2026-04-04 14:00:00
minute	Top of the minute	2026-04-04 14:30:00
second	The exact second	2026-04-04 14:30:45

Code Examples

Using the CatCafe dataset:

-- Table: orders
-- id, customer_id, cat_id, order_date, total_amount, status

-- Monthly revenue aggregation
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Weekly trends (ISO week)
SELECT
    DATE_TRUNC('week', order_date) as week_start,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_start;

-- Quarter analysis
SELECT
    DATE_TRUNC('quarter', order_date) as quarter,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('quarter', order_date)
ORDER BY quarter;

-- Daily with hour breakdown
SELECT
    DATE_TRUNC('hour', order_date) as hour_bucket,
    COUNT(*) as orders
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('hour', order_date)
ORDER BY hour_bucket;

-- Comparing periods: This month vs same month last year
SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(total_amount) as revenue
FROM orders
WHERE status = 'completed'
    AND order_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Time-based cohort: Monthly cohorts (more in Cohort Analysis topic)
SELECT
    DATE_TRUNC('month', order_date) as order_month,
    COUNT(DISTINCT customer_id) as cohort_size,
    SUM(total_amount) as cohort_revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

The Cat Analogy

Think of DATE_TRUNC like a cat's age milestone tracker:

Instead of saying "Whiskers is 847 days old," you truncate:

DATE_TRUNC('year') = "Whiskers is 2 years old" (born in 2024) DATE_TRUNC('month') = "Whiskers is 27 months old" (truncated to month) DATE_TRUNC('day') = "Whiskers was born on 2024-02-15"

For analysis:

Without trunc: Every single day is unique (365 buckets)
DATE_TRUNC('month'): 12 buckets (Jan-Dec)
DATE_TRUNC('quarter'): 4 buckets (Q1-Q4)
DATE_TRUNC('year'): 1 bucket (All year)

You're grouping time into meaningful periods, not destroying the exact moment, just truncating the precision.

Common Pitfalls

Inconsistent Truncation

-- Mixing 'month' and 'month' can still cause issues if timezone differs
-- Always use DATE_TRUNC consistently in GROUP BY and WHERE
SELECT
    DATE_TRUNC('month', order_date) as month,
    SUM(total_amount)
FROM orders
GROUP BY order_date;  -- OOPS: grouping by original column, not truncated!
-- Use:
GROUP BY DATE_TRUNC('month', order_date);

Week Truncation Start Day

-- PostgreSQL: Week starts on Monday (ISO 8601)
-- Some databases: Week starts on Sunday
-- Be explicit about your intent if week matters:
SELECT
    DATE_TRUNC('week', order_date) as week_start,
    ...
FROM orders;
-- If you need Sunday starts, check your database's defaults

Exercises

Exercise 1

Write a query showing monthly revenue for 2024, ordered chronologically.

Exercise 2

What does DATE_TRUNC('year', '2026-06-15') return?

Exercise 3

Write a query showing which day of the week (Monday, Tuesday, etc.) has the highest average revenue.

Exercise 4

Write a query to compare Q1 vs Q2 of 2024 revenue.

Exercise 5

Why might you use DATE_TRUNC instead of just GROUP BY date_column?

Key Takeaways

DATE_TRUNC rounds a date/time down to a specified unit
Essential for aggregating data by time periods
Common units: year, quarter, month, week, day, hour
Always use the same DATE_TRUNC expression in SELECT and GROUP BY
Week truncation start day varies by database (Monday in PostgreSQL)
Combine with COUNT, SUM, AVG for time-based analytics