re:re:actable
Урок 073 минуты чтения

Как группировать данные по датам

На этой странице
Кратко

Для группировки по периодам в PostgreSQL используют date_trunc('day' | 'week' | 'month', ts) — он «обрезает» момент до начала периода, сохраняя тип и правильный порядок сортировки. Три важных момента: (1) часовой пояс — date_trunc для timestamptz режет по поясу сессии, поэтому «день» для пользователя из Москвы задают явно: date_trunc('day', ts AT TIME ZONE 'Europe/Moscow') (PostgreSQL 12+: date_trunc('day', ts, 'Europe/Moscow')); (2) периоды без данных пропадут из результата — их добавляют через generate_series и LEFT JOIN; (3) фильтр по диапазону дат пишут полуоткрытым интервалом по «чистому» столбцу, чтобы работал индекс.

Вопрос

Как группировать данные по дням, неделям и месяцам?

date_trunc

SELECT date_trunc('month', created_at) AS month,
       count(*)                       AS orders,
       sum(total)                     AS revenue
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
GROUP BY 1
ORDER BY 1;
ЕдиницаРезультат для 2026-10-24 15:30
hour2026-10-24 15:00
day2026-10-24 00:00
week2026-10-19 00:00 (понедельник, ISO)
month2026-10-01 00:00
quarter2026-10-01 00:00
year2026-01-01 00:00

Часовой пояс

-- день по московскому времени, а не по UTC сервера
SELECT date_trunc('day', created_at AT TIME ZONE 'Europe/Moscow') AS day_msk,
       count(*)
FROM orders
GROUP BY 1
ORDER BY 1;
 
-- PostgreSQL 12+
SELECT date_trunc('day', created_at, 'Europe/Moscow') AS day, count(*) FROM orders GROUP BY 1;

Заказ в 01:30 МСК — это 22:30 UTC предыдущего дня; без явного пояса он попадёт «не в тот» день. Подробнее — даты и часовые пояса.

Дни без данных

SELECT d.day::date, COALESCE(count(o.id), 0) AS orders
FROM generate_series('2026-10-01'::date, '2026-10-31'::date, interval '1 day') AS d(day)
LEFT JOIN orders o
  ON o.created_at >= d.day AND o.created_at < d.day + interval '1 day'
GROUP BY d.day
ORDER BY d.day;

extract и to_char

-- распределение по дням недели и часам (не временной ряд, а «профиль»)
SELECT extract(isodow FROM created_at) AS weekday, extract(hour FROM created_at) AS hour, count(*)
FROM orders GROUP BY 1, 2;
 
-- форматирование для отображения — после группировки
SELECT to_char(date_trunc('month', created_at), 'YYYY-MM') AS month, count(*)
FROM orders GROUP BY date_trunc('month', created_at) ORDER BY date_trunc('month', created_at);

Группировать по to_char(...) можно, но строка 'Oct 2026' сортируется по алфавиту — лучше группировать по date_trunc, а форматировать на выводе или в приложении.

Произвольные интервалы

-- 15-минутные бакеты (PostgreSQL 14+)
SELECT date_bin('15 minutes', created_at, '2026-01-01') AS bucket, count(*)
FROM events GROUP BY 1 ORDER BY 1;

Частые ошибки

  • Группировка без учёта часового пояса пользователя.
  • WHERE date_trunc('month', created_at) = ... — индекс не используется.
  • Пропуски на графике из-за дней без данных.
  • extract(month ...) без года — октябрь 2025 и 2026 склеиваются.

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

  • date_trunc — основной инструмент временных рядов.
  • Часовой пояс задают явно.
  • Пустые периоды — generate_series + LEFT JOIN.

Мини-шпаргалка

  • date_trunc('day', ts).
  • ts AT TIME ZONE 'Europe/Moscow'.
  • date_bin('15 minutes', ts, origin).
  • generate_series(start, end, '1 day').
  • extract(isodow | hour FROM ts).

Документация

PostgreSQL: Date/Time Functions.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам