DATE_TRUNC()

Syntax

How it works

DATE_TRUNC('month', ts) takes a timestamp and throws away everything smaller than the unit you name, returning the very start of that period. Truncating 2026-07-04 13:47:12 to month gives 2026-07-01 00:00:00; truncating it to day gives 2026-07-04 00:00:00. It is a floor operation on time, which is exactly what you want when grouping a time series into equal buckets.

This is the point most people miss: truncation is not the same as pulling out a component. EXTRACT() returns a single number such as the month (7), throwing away the year, so rows from July 2025 and July 2026 collapse together. DATE_TRUNC() keeps a full timestamp, so those two Julys stay in separate buckets. Use EXTRACT for a part, DATE_TRUNC for a bucket.

Support is uneven across engines. PostgreSQL has had DATE_TRUNC(unit, ts) for a long time. SQL Server 2022 added DATETRUNC(unit, ts) with keyword units, and earlier versions must emulate it with DATEADD and DATEDIFF. MySQL has no DATE_TRUNC at all, so you emulate it with DATE_FORMAT() or DATE(). See the window functions guide for pairing truncated buckets with running totals.

Examples

Truncate a timestamp to the start of the month (PostgreSQL)

SELECT DATE_TRUNC('month', TIMESTAMP '2026-07-04 13:47:12') AS month_start;

month_start
-------------------
2026-07-01 00:00:00

Start of the day and start of the hour

SELECT
  DATE_TRUNC('day',  ts) AS day_start,
  DATE_TRUNC('hour', ts) AS hour_start
FROM events
WHERE id = 42;

day_start           | hour_start
--------------------+--------------------
2026-07-04 00:00:00 | 2026-07-04 13:00:00

Group revenue into monthly buckets (PostgreSQL)

SELECT DATE_TRUNC('month', created_at) AS month,
       SUM(amount)                    AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

month               | revenue
--------------------+---------
2026-05-01 00:00:00 |   84200
2026-06-01 00:00:00 |   91050
2026-07-01 00:00:00 |   77300

Same monthly bucket in each engine

-- PostgreSQL
SELECT DATE_TRUNC('month', created_at) AS month FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, created_at) AS month FROM orders;

-- SQL Server before 2022 (emulate start of month)
SELECT DATEADD(month, DATEDIFF(month, 0, created_at), 0) AS month FROM orders;

-- MySQL (no DATE_TRUNC; emulate with DATE_FORMAT)
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month FROM orders;

month
-------------------
2026-07-01 00:00:00

Common mistakes

-- Wanted July 2026 as its own bucket, but EXTRACT
-- returns just the number 7, merging every July
SELECT EXTRACT(MONTH FROM created_at) AS m, SUM(amount)
FROM orders
GROUP BY EXTRACT(MONTH FROM created_at);

-- DATE_TRUNC keeps the year, so each month is distinct
SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount)
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- MySQL has no DATE_TRUNC function
SELECT DATE_TRUNC('month', created_at) FROM orders;

-- Emulate: format to the first of the month,
-- or use DATE() for the start of the day
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month,
       DATE(created_at)                  AS day
FROM orders;

-- Assuming the week always starts on Monday
SELECT DATE_TRUNC('week', created_at) FROM orders;

-- Know your engine: PostgreSQL weeks start Monday (ISO).
-- If you need Sunday, shift before and after truncating.
SELECT DATE_TRUNC('week', created_at + INTERVAL '1 day')
         - INTERVAL '1 day' AS week_start_sunday
FROM orders;

Performance

Wrapping the column in DATE_TRUNC() inside a WHERE clause makes the predicate non-sargable: the database must compute the function for every row and cannot use a plain index on the timestamp. To keep an index range scan, compare the raw column to a range instead, for example created_at >= '2026-07-01' AND created_at < '2026-08-01'.

When you truncate only in the SELECT and GROUP BY, the cost is small and paid once per row. For heavy dashboards, consider an expression index in PostgreSQL, such as CREATE INDEX ON orders (DATE_TRUNC('month', created_at)), or a generated column that stores the bucket so the value is precomputed.

Interview questions

SELECT DATE_TRUNC('month', created_at) AS bucket,
       EXTRACT(MONTH FROM created_at)   AS month_number
FROM orders;

SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month
FROM orders;

SELECT DATEADD(month, DATEDIFF(month, 0, created_at), 0) AS month_start
FROM orders;