SQL DATE_TRUNC: Rounding Timestamps Down to Buckets for Time Series

DATE_TRUNC discards every part of a timestamp finer than the period you ask for and returns the start of that period. It is the workhorse of any time-based analytics: millisecond-precise rows collapse into clean buckets by hour, day, week, month or year.

What DATE_TRUNC actually does

The first argument is the field ('hour', 'day', 'week', 'month', 'year' and so on); the second is a timestamp or date. The result is rounded down to the start of the period, with the smaller units zeroed out.

SELECT
  DATE_TRUNC('month', TIMESTAMP '2026-06-17 14:32:09') AS month_start,
  DATE_TRUNC('day',   TIMESTAMP '2026-06-17 14:32:09') AS day_start,
  DATE_TRUNC('hour',  TIMESTAMP '2026-06-17 14:32:09') AS hour_start;
-- 2026-06-01 00:00:00 | 2026-06-17 00:00:00 | 2026-06-17 14:00:00

Key properties:

• It always floors, never rounds to nearest: 14:32 with 'hour' gives 14:00.
• It returns the same timestamp type, so the result is easy to compare and group by.
• 'week' in PostgreSQL starts on Monday (ISO), a common surprise if you expect Sunday.

Bucketing a time series

The headline use case is computing a metric per period. How many users signed up each month?

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*)                        AS signups
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

Same idea for daily revenue, now from orders and filtered by status:

SELECT
  DATE_TRUNC('day', created_at) AS day,
  SUM(amount)                   AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

Group by the DATE_TRUNC(...) expression, not the raw created_at, otherwise every distinct second becomes its own row.

Filling gaps with generate_series

GROUP BY only returns periods that have data. If a day had no orders, the row simply vanishes and your chart shows a broken line. To get a continuous axis, generate a calendar and LEFT JOIN against it.

SELECT
  cal.day,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
       DATE_TRUNC('day', DATE '2026-06-01'),
       DATE_TRUNC('day', DATE '2026-06-30'),
       INTERVAL '1 day'
     ) AS cal(day)
LEFT JOIN orders o
  ON DATE_TRUNC('day', o.created_at) = cal.day
 AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;

Now all 30 days appear, and days without orders honestly report 0.

Time zones: the big gotcha

The two-argument DATE_TRUNC(field, ts) does not compute in UTC. When ts is a timestamptz, PostgreSQL truncates according to the session TimeZone setting, so the same query can land an order in different calendar days depending on who runs it. An order at 2026-06-01 01:30 UTC already belongs to June 1 for a session in Moscow, but for one in UTC-5 it is still May 31. That shifts your monthly and daily buckets right on the day boundary.

So always make the zone explicit instead of trusting the session default. Convert with AT TIME ZONE first and then truncate:

SELECT
  DATE_TRUNC('day', created_at AT TIME ZONE 'America/New_York') AS day_ny,
  COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('day', created_at AT TIME ZONE 'America/New_York');

Modern PostgreSQL (14+) also offers a three-argument form, DATE_TRUNC(field, ts, zone), that truncates a timestamptz in the zone you name and removes any doubt about the session setting:

SELECT
  DATE_TRUNC('day', created_at, 'America/New_York') AS day_ny,
  COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('day', created_at, 'America/New_York');

For an arbitrary step (say 15 minutes) reach for date_bin('15 minutes', created_at, TIMESTAMP '2026-01-01').

MySQL and ClickHouse equivalents

DATE_TRUNC is not universal, and the syntax varies.

• MySQL has no DATE_TRUNC. Truncate to a month with DATE_FORMAT, to a day with DATE().

SELECT
  DATE_FORMAT(created_at, '%Y-%m-01') AS month,
  COUNT(*)                            AS signups
FROM users
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01');

• ClickHouse ships dedicated functions: toStartOfMonth, toStartOfDay, toStartOfHour, plus toStartOfInterval for an arbitrary step.

SELECT
  toStartOfMonth(created_at) AS month,
  count()                    AS signups
FROM users
GROUP BY month
ORDER BY month;

Remember one thing: the intent is identical everywhere, zero out the tail of the timestamp so rows snap into buckets. Only the function name changes.