SQL - DATE_TRUNC() - Truncate Date

Key Insights

• DATE_TRUNC() rounds timestamps down to a specified precision level, making it essential for grouping time-series data in reports and dashboards
• The function works consistently across PostgreSQL, Snowflake, BigQuery, and Redshift, though MySQL and SQL Server require alternative approaches
• Always apply DATE_TRUNC() in your SELECT and GROUP BY clauses rather than WHERE clauses to preserve index usage on timestamp columns

Introduction to DATE_TRUNC()

Date truncation is the process of rounding a timestamp down to a specified level of precision. When you truncate 2024-03-15 14:32:45 to the month level, you get 2024-03-01 00:00:00. The time component and day are reset to their minimum values while preserving the year and month.

This operation solves a fundamental problem in data analysis: how do you group events that happened at different times into meaningful buckets? Raw timestamps are too granular for most reporting needs. You don’t want to see revenue for 2024-03-15 14:32:45—you want revenue for March 2024.

DATE_TRUNC() gives you clean, consistent boundaries for time-based aggregations without the string manipulation hacks that plagued SQL developers for years.

Syntax and Parameters

The standard DATE_TRUNC() syntax follows this pattern:

DATE_TRUNC(precision, timestamp_expression)
-- or in some databases
DATE_TRUNC('precision', timestamp_expression)

The precision parameter accepts these common values:

• year - First day of the year (January 1st)
• quarter - First day of the quarter (January 1st, April 1st, July 1st, October 1st)
• month - First day of the month
• week - First day of the week (typically Monday, but database-dependent)
• day - Midnight of that day
• hour - Start of the hour
• minute - Start of the minute
• second - Truncates sub-second precision

The function returns the same data type as the input—a timestamp in, a timestamp out. This consistency makes DATE_TRUNC() composable with other date functions and arithmetic operations.

-- PostgreSQL/Redshift syntax (precision as string, then timestamp)
SELECT DATE_TRUNC('month', order_timestamp) AS order_month
FROM orders;

-- Snowflake syntax (same pattern)
SELECT DATE_TRUNC('month', order_timestamp) AS order_month
FROM orders;

-- BigQuery syntax (precision without quotes for some versions)
SELECT DATE_TRUNC(order_timestamp, MONTH) AS order_month
FROM orders;

Common Use Cases

DATE_TRUNC() appears constantly in three scenarios: periodic reporting, time-series dashboards, and timestamp normalization.

Monthly revenue reports are the classic example. Finance teams want to see total revenue by month, not by individual transaction timestamp:

SELECT 
    DATE_TRUNC('month', order_date) AS revenue_month,
    SUM(order_total) AS monthly_revenue,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY revenue_month;

This query produces clean monthly buckets regardless of when individual orders occurred. The output might look like:

Timestamp normalization matters when comparing events across systems. If your payment processor records transactions at 14:32:45.123456 but your inventory system logs at 14:32:45, truncating both to the minute level creates comparable values:

SELECT 
    DATE_TRUNC('minute', payment_timestamp) AS normalized_time,
    payment_id,
    inventory_update_id
FROM payments p
JOIN inventory_updates i 
    ON DATE_TRUNC('minute', p.payment_timestamp) = DATE_TRUNC('minute', i.update_timestamp)
    AND p.order_id = i.order_id;

Precision Levels in Practice

Understanding what each precision level produces helps you choose the right granularity. Here’s the same timestamp truncated at every common level:

-- Source timestamp: 2024-03-15 14:32:45

SELECT 
    '2024-03-15 14:32:45'::timestamp AS original,
    DATE_TRUNC('year',   '2024-03-15 14:32:45'::timestamp) AS truncated_year,
    DATE_TRUNC('quarter','2024-03-15 14:32:45'::timestamp) AS truncated_quarter,
    DATE_TRUNC('month',  '2024-03-15 14:32:45'::timestamp) AS truncated_month,
    DATE_TRUNC('week',   '2024-03-15 14:32:45'::timestamp) AS truncated_week,
    DATE_TRUNC('day',    '2024-03-15 14:32:45'::timestamp) AS truncated_day,
    DATE_TRUNC('hour',   '2024-03-15 14:32:45'::timestamp) AS truncated_hour,
    DATE_TRUNC('minute', '2024-03-15 14:32:45'::timestamp) AS truncated_minute;

Results:

Notice that year and quarter produce the same result here because March falls in Q1. The week truncation returns March 11th because that’s the Monday of the week containing March 15th (in PostgreSQL’s default ISO week handling).

DATE_TRUNC() with Aggregations

The real power of DATE_TRUNC() emerges when combined with aggregate functions. Here are patterns you’ll use repeatedly.

Daily active users:

SELECT 
    DATE_TRUNC('day', login_timestamp) AS login_day,
    COUNT(DISTINCT user_id) AS daily_active_users
FROM user_sessions
WHERE login_timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', login_timestamp)
ORDER BY login_day;

Weekly order totals with running comparison:

SELECT 
    DATE_TRUNC('week', order_date) AS week_starting,
    COUNT(*) AS orders,
    SUM(order_total) AS revenue,
    AVG(order_total) AS avg_order_value,
    LAG(SUM(order_total)) OVER (ORDER BY DATE_TRUNC('week', order_date)) AS prev_week_revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_starting;

Hourly traffic patterns:

SELECT 
    DATE_TRUNC('hour', request_timestamp) AS hour_bucket,
    COUNT(*) AS requests,
    COUNT(*) FILTER (WHERE status_code >= 500) AS errors,
    ROUND(100.0 * COUNT(*) FILTER (WHERE status_code >= 500) / COUNT(*), 2) AS error_rate
FROM api_logs
WHERE request_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', request_timestamp)
ORDER BY hour_bucket;

Database-Specific Variations

While DATE_TRUNC() works similarly across PostgreSQL, Snowflake, BigQuery, and Redshift, the syntax varies slightly. MySQL and SQL Server don’t have DATE_TRUNC() at all.

PostgreSQL and Redshift:

SELECT DATE_TRUNC('month', created_at) AS month
FROM users
GROUP BY DATE_TRUNC('month', created_at);

Snowflake:

-- Same syntax as PostgreSQL
SELECT DATE_TRUNC('month', created_at) AS month
FROM users
GROUP BY DATE_TRUNC('month', created_at);

BigQuery:

-- Different argument order and unquoted precision for TIMESTAMP
SELECT DATE_TRUNC(created_at, MONTH) AS month
FROM users
GROUP BY DATE_TRUNC(created_at, MONTH);

-- For DATE type, use DATE_TRUNC with DATE
SELECT DATE_TRUNC(DATE(created_at), MONTH) AS month
FROM users
GROUP BY 1;

MySQL (alternative approach):

-- Use DATE_FORMAT for similar results
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month
FROM users
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01');

-- Or use LAST_DAY and date arithmetic
SELECT DATE_SUB(created_at, INTERVAL DAYOFMONTH(created_at) - 1 DAY) AS month_start
FROM users;

SQL Server (alternative approach):

-- SQL Server 2022+ has DATETRUNC
SELECT DATETRUNC(month, created_at) AS month
FROM users
GROUP BY DATETRUNC(month, created_at);

-- Older versions use DATEFROMPARTS
SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) AS month
FROM users
GROUP BY DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1);

Performance Considerations

DATE_TRUNC() is a function call, which means the database can’t use a standard B-tree index on your timestamp column when you filter with it directly.

Bad for performance—prevents index usage:

-- This won't use an index on order_date
SELECT COUNT(*)
FROM orders
WHERE DATE_TRUNC('month', order_date) = '2024-03-01';

Good for performance—preserves index usage:

-- This can use an index on order_date
SELECT COUNT(*)
FROM orders
WHERE order_date >= '2024-03-01' 
  AND order_date < '2024-04-01';

The rule: use DATE_TRUNC() in SELECT and GROUP BY clauses freely, but avoid it in WHERE clauses when filtering large tables. Instead, use explicit range comparisons.

For dashboards that repeatedly query the same truncated values, consider creating a computed column or a materialized view:

-- PostgreSQL: Add a generated column
ALTER TABLE orders 
ADD COLUMN order_month timestamp 
GENERATED ALWAYS AS (DATE_TRUNC('month', order_date)) STORED;

-- Then index it
CREATE INDEX idx_orders_month ON orders(order_month);

This shifts the DATE_TRUNC() computation to write time rather than query time, and the index makes filtering by month instantaneous.

DATE_TRUNC() is one of those functions that seems simple but unlocks sophisticated time-series analysis. Master the precision levels, understand the database-specific syntax, and respect the indexing implications. Your reporting queries will be cleaner, faster, and easier to maintain.