How to Truncate Date in Redshift

What does DATE_TRUNC do in Redshift?

DATE_TRUNC rounds a timestamp or date down to the specified time unit—second, minute, hour, day, week, month, quarter, or year—returning a new timestamp with lower-order fields zeroed.

How do I write the DATE_TRUNC syntax?

Call DATE_TRUNC('unit', timestamp_expression). The unit must be a quoted string like 'day'. The function returns a TIMESTAMP without timezone.

Which units are most useful for reporting?

Day, week, month, and year summarise metrics nicely for dashboards.Use month for revenue, week for active users, and day for operational alerts.

How can I truncate order dates to month?

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

Full ecommerce example

SELECT DATE_TRUNC('month', o.order_date) AS month, p.name, SUM(oi.quantity * p.price) AS revenue FROM Orders o JOIN OrderItems oi ON oi.order_id = o.id JOIN Products p ON p.id = oi.product_id GROUP BY 1, 2 ORDER BY 1, 2;

Best practices for DATE_TRUNC

Quote units, cast VARCHAR columns to TIMESTAMP first, and include DATE_TRUNC in GROUP BY to guarantee correct aggregation.Pre-compute for large tables if query speed matters.

Common mistakes and how to avoid them

Unquoted units raise errors: use 'month', not month. Truncating to 'hour' on a DATE adds midnight; cast to TIMESTAMP before calling. Forgetting GROUP BY with DATE_TRUNC causes aggregation mismatch.

FAQ

How do I truncate to the start of the week?

DATE_TRUNC('week', timestamp) returns the previous Sunday at 00:00.

Can I truncate to quarter?

Yes.DATE_TRUNC('quarter', timestamp) returns the first day of the quarter at midnight.

Is DATE_TRUNC deterministic?

Yes, it is IMMUTABLE in Redshift, so it always returns the same value for the same input.

.