SQL DATE_TRUNC: Rounding Timestamps Down to Buckets for Time Series

В реальных проектах почти все события хранятся с точным временем: пользователь зарегистрировался в 14:32:09, заказ оплатили в 18:07:41, письмо открыли в 09:15:03.

Для базы данных это нормально. Но для отчёта такие значения неудобны.

Представьте, что вы хотите построить график регистраций по дням. Если группировать данные по полю created_at как есть, база будет считать почти каждую секунду отдельной группой. Вместо аккуратной таблицы «день → количество регистраций» вы получите сотни или тысячи строк с разными моментами времени.

Именно здесь помогает DATE_TRUNC.

Функция DATE_TRUNC берёт дату и время, а затем «обрезает» их до нужного уровня: часа, дня, недели, месяца, года и так далее. Благодаря этому события можно складывать в понятные календарные группы.

Например:

• все события с 10:00:00 до 10:59:59 попадут в один час;
• все события за 17 июня попадут в один день;
• все события июня попадут в один месяц.

То есть DATE_TRUNC превращает хаотичный поток точных временных меток в удобные периоды для аналитики.

Что делает DATE_TRUNC простыми словами

DATE_TRUNC используется, когда нам нужно отбросить лишнюю точность у даты.

Например, у нас есть точное время:

2026-06-17 14:32:09

Если обрезать его до месяца, получится начало месяца:

2026-06-01 00:00:00

Если обрезать до дня, получится начало дня:

2026-06-17 00:00:00

Если обрезать до часа, получится начало часа:

2026-06-17 14:00:00

Важно: DATE_TRUNC не округляет дату до ближайшего часа или месяца. Она именно отсекает всё, что меньше выбранного периода.

Пример в PostgreSQL:

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;

Результат будет примерно таким:

month_start          | day_start            | hour_start
---------------------+----------------------+---------------------
2026-06-01 00:00:00  | 2026-06-17 00:00:00  | 2026-06-17 14:00:00

Можно думать об этом так:

DATE_TRUNC приводит дату к началу выбранного периода.

Это очень удобно для отчётов, графиков, продуктовой аналитики, когорт, расчёта выручки и любых задач, где нужно смотреть данные не по каждой секунде, а по нормальным временным отрезкам.

Общий синтаксис DATE_TRUNC

В PostgreSQL функция обычно выглядит так:

DATE_TRUNC('field', source_ts)

Первым аргументом мы указываем, до какого уровня нужно обрезать дату.

Частые варианты:

DATE_TRUNC('hour', created_at)   -- truncate to hour
DATE_TRUNC('day', created_at)    -- truncate to day
DATE_TRUNC('week', created_at)   -- truncate to week
DATE_TRUNC('month', created_at)  -- truncate to month
DATE_TRUNC('year', created_at)   -- truncate to year

Допустим, в таблице users есть поле created_at, где хранится дата и время регистрации пользователя:

id | email           | created_at
---+-----------------+---------------------
1  | a@example.com   | 2026-06-17 10:15:22
2  | b@example.com   | 2026-06-17 10:48:03
3  | c@example.com   | 2026-06-17 11:05:10

Если применить DATE_TRUNC('hour', created_at), первые два пользователя попадут в один и тот же час:

2026-06-17 10:00:00

А третий пользователь попадёт уже в следующий:

2026-06-17 11:00:00

Так база понимает: эти события относятся к разным часовым корзинам.

Пример 1. Регистрации пользователей по месяцам

Один из самых частых сценариев — посчитать, сколько пользователей зарегистрировалось в каждом месяце.

Допустим, у нас есть таблица users:

id | email           | created_at
---+-----------------+---------------------
1  | a@example.com   | 2026-06-03 12:10:00
2  | b@example.com   | 2026-06-17 14:32:09
3  | c@example.com   | 2026-07-01 09:05:44
4  | d@example.com   | 2026-07-21 18:40:11

Нам не важно точное время регистрации. Нам важно понять, сколько регистраций было в июне, сколько в июле и так далее.

Запрос:

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

Результат:

month                | signups
---------------------+--------
2026-06-01 00:00:00  | 2
2026-07-01 00:00:00  | 2

Что здесь происходит:

1. DATE_TRUNC('month', created_at) превращает любую дату внутри месяца в первое число этого месяца.
2. GROUP BY собирает одинаковые месяцы в группы.
3. COUNT(*) считает количество пользователей внутри каждой группы.
4. ORDER BY month сортирует результат по времени.

То есть все июньские регистрации превращаются в 2026-06-01 00:00:00, а все июльские — в 2026-07-01 00:00:00.

Именно поэтому база может сложить их в две понятные группы.

Пример 2. Выручка по дням

Теперь пример ближе к бизнесу.

Есть таблица orders с заказами:

id | amount | status | created_at
---+--------+--------+---------------------
1  | 1500   | paid   | 2026-06-17 10:15:00
2  | 2300   | paid   | 2026-06-17 18:40:00
3  | 900    | paid   | 2026-06-18 11:05:00
4  | 700    | cancel | 2026-06-18 12:20:00

Нужно посчитать выручку по дням. Берём только оплаченные заказы, группируем их по дню и суммируем amount.

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;

Результат:

day                  | revenue
---------------------+---------
2026-06-17 00:00:00  | 3800
2026-06-18 00:00:00  | 900

Почему 17 июня получилось 3800?

Потому что в этот день было два оплаченных заказа:

1500 + 2300 = 3800

А заказ со статусом cancel не попал в расчёт, потому что мы отфильтровали только:

WHERE status = 'paid'

Такой запрос уже можно использовать для отчёта, дашборда или графика дневной выручки.

Частая ошибка: группировать по исходному created_at

Новички часто пишут запрос примерно так:

SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) AS orders_count
FROM orders
GROUP BY created_at;

На первый взгляд кажется, что всё нормально: в SELECT мы же вывели день через DATE_TRUNC.

Но проблема в том, что группировка идёт по сырому created_at, то есть по точному времени заказа.

Если один заказ был создан в 10:15:00, а второй в 10:16:00, база посчитает их разными группами, хотя они относятся к одному дню.

Правильно группировать по тому же выражению, которое превращает дату в день:

SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) AS orders_count
FROM orders
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;

Главное правило:

Если вы хотите получить статистику по дням, группируйте по дню. Если хотите статистику по месяцам, группируйте по месяцу. Не группируйте по исходной временной метке, если точность до секунд вам не нужна.

Можно ли писать GROUP BY короче

В PostgreSQL часто можно использовать номер колонки из SELECT.

Например, вместо такого запроса:

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

можно написать так:

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

GROUP BY 1 означает: группировать по первой колонке из SELECT.

В нашем случае первая колонка — это:

DATE_TRUNC('month', created_at)

Такой стиль часто встречается в рабочих запросах, потому что он короче. Но для обучения лучше сначала писать выражение полностью. Так понятнее, что именно происходит в запросе.

Пример 3. События по часам

Допустим, у нас есть таблица events, куда попадают действия пользователей: открытие страницы, клик по кнопке, отправка формы.

id | event_name  | created_at
---+-------------+---------------------
1  | page_view   | 2026-06-17 10:01:15
2  | page_view   | 2026-06-17 10:12:44
3  | button_click| 2026-06-17 10:59:01
4  | page_view   | 2026-06-17 11:03:22

Хотим понять активность пользователей по часам.

SELECT
  DATE_TRUNC('hour', created_at) AS hour,
  COUNT(*) AS events_count
FROM events
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour;

Результат:

hour                 | events_count
---------------------+-------------
2026-06-17 10:00:00  | 3
2026-06-17 11:00:00  | 1

Все события с 10:00 до 10:59 попали в группу 10:00. Событие в 11:03 попало уже в группу 11:00.

Так можно строить почасовые графики нагрузки, активности пользователей, количества ошибок, платежей или любых других событий.

DATE_TRUNC и неделя

С неделями нужно быть внимательнее.

В PostgreSQL при использовании:

DATE_TRUNC('week', created_at)

неделя начинается с понедельника.

Например:

SELECT DATE_TRUNC('week', TIMESTAMP '2026-06-17 14:32:09');

Результат:

2026-06-15 00:00:00

Почему 15 июня?

Потому что 17 июня 2026 года — это среда, а понедельник этой недели — 15 июня.

Это важный момент для бизнес-отчётов. В одних компаниях неделя считается с понедельника, в других — с воскресенья, а в некоторых отчётах используют вообще свой финансовый календарь.

Поэтому перед тем как строить недельную аналитику, лучше уточнить:

• с какого дня начинается неделя;
• совпадает ли календарная неделя с бизнес-неделей;
• нужно ли учитывать часовой пояс.

Иначе цифры в SQL могут быть правильными технически, но не совпадать с ожиданиями бизнеса.

Как заполнить пропущенные дни

Обычный GROUP BY показывает только те периоды, где есть данные.

Например, если 19 июня не было ни одного заказа, то в результате запроса этого дня просто не будет.

day                  | revenue
---------------------+---------
2026-06-17 00:00:00  | 3800
2026-06-18 00:00:00  | 900
2026-06-20 00:00:00  | 4100

Для таблицы это иногда нормально. Но для графика — плохо.

Пользователь смотрит на график и не понимает: 19 июня действительно было 0 заказов или этот день просто потерялся?

Чтобы показать все дни, даже пустые, в PostgreSQL удобно использовать generate_series.

Сначала создаём календарь дней, а потом присоединяем к нему заказы.

SELECT
  cal.day,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
       DATE '2026-06-01',
       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;

Что здесь происходит:

1. generate_series создаёт список всех дней с 1 по 30 июня.
2. LEFT JOIN присоединяет заказы к нужному дню.
3. Если заказов в какой-то день не было, день всё равно остаётся в результате.
4. COALESCE(SUM(o.amount), 0) заменяет NULL на 0.

Без COALESCE день без заказов дал бы NULL, потому что суммировать нечего. А для отчёта обычно понятнее видеть честный ноль.

Пример результата:

day                  | revenue
---------------------+---------
2026-06-01 00:00:00  | 1200
2026-06-02 00:00:00  | 0
2026-06-03 00:00:00  | 3400
...
2026-06-30 00:00:00  | 900

Такой подход особенно полезен для графиков, BI-систем и витрин данных, где временная ось должна быть непрерывной.

Часовые пояса: место, где легко ошибиться

С датами всё становится интереснее, когда появляются часовые пояса.

Допустим, заказ был создан:

2026-06-01 01:30:00 UTC

Для UTC это 1 июня. Но для другого региона этот же момент времени может относиться к другому локальному дню.

Например, если бизнес смотрит отчёты по московскому времени, нужно сначала перевести время в нужный часовой пояс, а уже потом обрезать до дня.

В PostgreSQL это можно сделать через AT TIME ZONE:

SELECT
  DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Moscow') AS day_msk,
  COUNT(*) AS orders_count
FROM orders
GROUP BY DATE_TRUNC('day', created_at AT TIME ZONE 'Europe/Moscow')
ORDER BY day_msk;

Здесь порядок важен:

1. Сначала переводим created_at в нужную временную зону.
2. Потом применяем DATE_TRUNC.
3. Потом группируем.

Если сделать наоборот, можно получить неверные границы дней, особенно около полуночи.

Главный вопрос, который нужно задать перед отчётом:

В каком часовом поясе бизнес хочет видеть данные?

Это может быть:

• часовой пояс клиента;
• часовой пояс магазина или склада;
• часовой пояс финансовой системы;
• единый часовой пояс компании;
• UTC, если отчёт технический.

Без этого договориться о «правильной» дневной или месячной статистике сложно.

Произвольные интервалы: например, каждые 15 минут

DATE_TRUNC хорошо работает с привычными календарными периодами:

• час;
• день;
• неделя;
• месяц;
• год.

Но иногда нужен нестандартный шаг. Например, сгруппировать события не по часам, а по 15 минутам:

10:00
10:15
10:30
10:45
11:00

Для таких задач в PostgreSQL лучше использовать date_bin.

Пример:

SELECT
  date_bin(
    INTERVAL '15 minutes',
    created_at,
    TIMESTAMP '2026-01-01 00:00:00'
  ) AS period_start,
  COUNT(*) AS events_count
FROM events
GROUP BY period_start
ORDER BY period_start;

Здесь:

• INTERVAL '15 minutes' — размер интервала;
• created_at — дата события;
• TIMESTAMP '2026-01-01 00:00:00' — точка, от которой строится сетка интервалов.

Для новичка можно запомнить так:

DATE_TRUNC — для обычных календарных периодов. date_bin — для произвольных интервалов вроде 5, 10 или 15 минут.

Аналоги DATE_TRUNC в MySQL

Функция DATE_TRUNC есть не во всех СУБД.

В PostgreSQL она есть. В MySQL функции DATE_TRUNC в таком виде нет, поэтому используются другие способы.

Например, чтобы сгруппировать пользователей по дням, можно использовать DATE():

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS signups
FROM users
GROUP BY DATE(created_at)
ORDER BY day;

DATE(created_at) берёт только дату и отбрасывает время.

Для группировки по месяцам часто используют DATE_FORMAT:

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

Здесь выражение:

DATE_FORMAT(created_at, '%Y-%m-01')

превращает любую дату месяца в строку вида:

2026-06-01

То есть идея остаётся той же: привести разные даты одного месяца к одному общему значению.

Меняется только синтаксис.

Аналоги DATE_TRUNC в ClickHouse

В ClickHouse для таких задач есть отдельные функции.

Например:

toStartOfHour(created_at)
toStartOfDay(created_at)
toStartOfMonth(created_at)
toStartOfYear(created_at)

Пример группировки регистраций по месяцам:

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

Для группировки по дням:

SELECT
  toStartOfDay(created_at) AS day,
  count() AS signups
FROM users
GROUP BY day
ORDER BY day;

А если нужен произвольный интервал, например 15 минут, можно использовать toStartOfInterval:

SELECT
  toStartOfInterval(created_at, INTERVAL 15 minute) AS period_start,
  count() AS events_count
FROM events
GROUP BY period_start
ORDER BY period_start;

ClickHouse часто используют для аналитики и больших объёмов событий, поэтому такие функции там встречаются очень часто.

Что важно запомнить

DATE_TRUNC нужна, когда у вас есть точное время события, но анализировать данные нужно по более крупным периодам.

Например:

• регистрации по дням;
• выручка по месяцам;
• ошибки по часам;
• активность пользователей по неделям;
• заказы по годам.

Главная идея простая:

Сначала приводим дату к началу нужного периода, потом группируем.

Если нужно посчитать регистрации по месяцам:

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

Если нужна дневная выручка:

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;

Если нужны все дни, даже без заказов, добавляем календарь через generate_series.

Если в проекте есть часовые пояса, сначала переводим дату в нужную зону, а уже потом применяем DATE_TRUNC.

Короткий вывод

DATE_TRUNC — одна из самых полезных функций для работы с датами в SQL.

Она помогает превратить точные временные метки в понятные периоды: часы, дни, недели, месяцы и годы. Благодаря этому можно строить нормальные отчёты, графики и продуктовые метрики.

Для начинающего SQL-разработчика или аналитика важно не просто запомнить синтаксис, а понять саму идею:

база хранит события точно, а человек обычно анализирует их периодами.

DATE_TRUNC как раз и помогает перейти от точных событий к понятной аналитике.