How to TRUNCATE DATE in PostgreSQL

How do I truncate dates in PostgreSQL?

DATE_TRUNC shortens a timestamp or interval to a chosen precision (year, month, day, etc.) so you can group or filter data at that level.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does DATE_TRUNC solve?

DATE_TRUNC lets you drop the lower-order time fields—hours, minutes, seconds—so records that share the same day, month, or year line up. This is essential for quick GROUP BY queries and calendar-based reports.

What is the exact DATE_TRUNC syntax?

Use DATE_TRUNC(text precision, timestamp source). Precision is a quoted string like 'day', 'month', 'quarter', or 'week'. The source can be a column, literal, or expression.

How do I group Orders by month?

Wrap order_date with DATE_TRUNC('month', order_date) and GROUP BY the result. All orders from June 2024 share the same truncated timestamp 2024-06-01 00:00.

Can I truncate to custom calendar periods?

PostgreSQL supports year, quarter, month, week, day, hour, minute, and second. For fiscal calendars, truncate first and then add offsets (e.g., + '-1 month'::interval).

What are best practices for DATE_TRUNC?

Always alias the truncated column (AS month_start) for readability. Index the raw timestamp; PostgreSQL can still use the index when you call DATE_TRUNC in GROUP BY.

Common mistakes to avoid

Passing an unquoted precision string or using TRUNC() instead of DATE_TRUNC() both raise errors. Cast your columns to timestamp when working with date types.

Why How to TRUNCATE DATE in PostgreSQL is important

How to TRUNCATE DATE in PostgreSQL Example Usage


-- Monthly revenue per customer
SELECT c.id,
       c.name,
       DATE_TRUNC('month', o.order_date) AS month_start,
       SUM(o.total_amount)               AS monthly_spend
FROM Customers AS c
JOIN Orders    AS o ON o.customer_id = c.id
GROUP BY c.id, c.name, month_start
ORDER BY month_start, c.id;

How to TRUNCATE DATE in PostgreSQL Syntax


DATE_TRUNC('precision', source_timestamp)
-- precision: 'year' | 'quarter' | 'month' | 'week' | 'day' | 'hour' | 'minute' | 'second'
-- source_timestamp: TIMESTAMP or INTERVAL expression

-- Example in ecommerce context
SELECT DATE_TRUNC('day', order_date) AS day_start, SUM(total_amount)
FROM Orders
GROUP BY day_start
ORDER BY day_start;

Common Mistakes

Using an unquoted precision: DATE_TRUNC(day, order_date) fails. Always quote: DATE_TRUNC('day', order_date).
Confusing DATE_TRUNC with TRUNC: TRUNC works for numbers, not timestamps. Use DATE_TRUNC for time values.