How to truncate date in PostgreSQL

How do I truncate a timestamp to day, hour, or month in PostgreSQL?

DATE_TRUNC() rounds or cuts a timestamp down to the specified unit (second-to-millennium), letting you ignore lower-precision parts like hours or minutes.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does DATE_TRUNC() do in PostgreSQL?

DATE_TRUNC() returns the input timestamp with all fields smaller than the requested unit set to their minimal values. Use it to strip the time from a timestamp, round to the nearest hour, or group data into calendar buckets.

How do I remove the time portion from a timestamp?

Select date_trunc('day', order_date). The function zeroes hours, minutes, and seconds, so 2024-05-15 13:42:10 becomes 2024-05-15 00:00:00. Cast to DATE if you need a pure date.

What is the syntax of DATE_TRUNC()?

Put the unit first, then the timestamp expression. Valid units include microsecond, millisecond, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.

DATE_TRUNC ( 'unit', timestamp_expression )

Parameters explained

unit – text literal specifying the precision. timestamp_expression – any TIMESTAMP, TIMESTAMPTZ, or INTERVAL value.

Practical example: daily revenue by customer

The query rounds order dates to midnight, groups by customer and day, and sums revenue.

SELECT c.id, c.name, DATE_TRUNC('day', o.order_date) AS order_day, SUM(o.total_amount) AS daily_sales FROM Customers c JOIN Orders o ON o.customer_id = c.id GROUP BY c.id, c.name, order_day ORDER BY order_day, c.id;

When should I use DATE_TRUNC() instead of CAST()?

Use DATE_TRUNC() whenever you need flexible granularities (hour, week, month) or want to keep a timestamp data type. CAST() only converts to DATE, losing sub-day context.

Common mistakes when using DATE_TRUNC()

Mistake 1: Using an unsupported unit

Passing 'hours' or 'mins' raises invalid input syntax. Use the singular form: 'hour', 'minute'.

Mistake 2: Applying DATE_TRUNC() to DATE values

DATE_TRUNC() expects a timestamp or interval. Cast the DATE column to TIMESTAMP or skip truncation altogether.

Best practices for performance

Truncate in a derived table so indexes on the raw timestamp remain usable. For example, use a CTE, then group on the truncated result.

Related functions

DATE() casts to date; DATE_PART() extracts a single field; TO_CHAR() formats timestamps for display.

Why How to truncate date in PostgreSQL is important

How to truncate date in PostgreSQL Example Usage


-- Weekly product sales totals
SELECT DATE_TRUNC('week', o.order_date)    AS sales_week,
       p.id,
       p.name,
       SUM(oi.quantity * p.price)          AS weekly_revenue
FROM   Orders      o
JOIN   OrderItems  oi ON oi.order_id  = o.id
JOIN   Products    p  ON p.id         = oi.product_id
GROUP  BY sales_week, p.id, p.name
ORDER  BY sales_week, weekly_revenue DESC;

How to truncate date in PostgreSQL Syntax


DATE_TRUNC ('unit', timestamp_expression)

-- Daily order totals
SELECT DATE_TRUNC('day', order_date) AS order_day,
       SUM(total_amount) AS revenue
FROM   Orders
GROUP  BY order_day
ORDER  BY order_day;

Common Mistakes

Using plural unit names like 'hours' or 'months'. PostgreSQL only accepts the singular form (e.g., 'hour', 'month'). Fix by supplying the correct literal.
Calling DATE_TRUNC() on a DATE column, which already lacks time data. This adds unnecessary casting and can break indexes. Cast to TIMESTAMP only when needed or skip truncation.