How to Truncate Date in PostgreSQL

How do I truncate a date or timestamp in PostgreSQL?

DATE_TRUNC cuts a DATE or TIMESTAMP down to a chosen precision (year, month, day, etc.) so you can group or compare dates easily.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does DATE_TRUNC solve?

DATE_TRUNC removes the smaller time portions from a DATE or TIMESTAMP, letting you compare or aggregate rows on the same calendar boundary without manual string work.

How do I truncate to month, day, or hour?

Use DATE_TRUNC('precision', column). Precisions include millennium, century, decade, year, quarter, month, week, day, hour, minute, second.

Why use DATE_TRUNC for reporting?

Sales dashboards often group orders by month.Truncating order_date to month gives repeatable bucket edges and speeds GROUP BY queries.

Can I truncate in the SELECT list?

Yes—return both the truncated value for display and an aggregate, e.g., monthly revenue. The function is immutable and index-friendly on expression indexes.

Monthly revenue example

SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) FROM Orders GROUP BY 1 ORDER BY 1;

Daily active customers example

SELECT DATE_TRUNC('day', created_at) AS day, COUNT(DISTINCT customer_id) FROM Customers GROUP BY 1;

Does DATE_TRUNC work with time zones?

Yes.For TIMESTAMPTZ, PostgreSQL converts to the session time zone before truncation. Set TIME ZONE 'x' to control results.

Best practices for performance?

Create an expression index on DATE_TRUNC result when filtering large tables repeatedly. Keep precision strings lowercase for readability and consistency.

Why How to Truncate Date in PostgreSQL is important

How to Truncate Date in PostgreSQL Example Usage


-- How many "T-Shirt" units sold each week?
SELECT DATE_TRUNC('week', o.order_date) AS week_start,
       SUM(oi.quantity)                AS tshirt_units
FROM   Orders      o
JOIN   OrderItems  oi ON oi.order_id = o.id
JOIN   Products    p  ON p.id = oi.product_id
WHERE  p.name = 'T-Shirt'
GROUP  BY week_start
ORDER  BY week_start;

How to Truncate Date in PostgreSQL Syntax


DATE_TRUNC('precision', source)

precision: text literal such as 'year', 'quarter', 'month', 'day', 'hour', 'minute', 'second'
source   : a DATE, TIMESTAMP, or TIMESTAMPTZ expression

-- Monthly order totals in ecommerce context
SELECT DATE_TRUNC('month', order_date) AS month_bucket,
       SUM(total_amount)               AS monthly_sales
FROM   Orders
GROUP  BY month_bucket
ORDER  BY month_bucket;

Common Mistakes

Using string functions like TO_CHAR instead of DATE_TRUNC for grouping. TO_CHAR forces text comparison and prevents index use. Fix by replacing TO_CHAR with DATE_TRUNC and grouping on the expression.
Forgetting time zones when truncating TIMESTAMPTZ. Results may shift if the session time zone is wrong. Always SET TIME ZONE explicitly or cast to TIMESTAMP to avoid surprises.