Даты и время в SQL: отрезки, группировка по дням и арифметика

← назад к разделу

Почти любой вопрос к базе рано или поздно упирается в дату. Заказы за первую неделю августа. Выручка по дням. Оплата, которая висит вторые сутки. Сколько минут проходит от заказа до оплаты. Всё это выглядит просто — пока первый же запрос «покажи заказы за пятое августа» не возвращает ноль строк, хотя глазами в таблице эти заказы видно.

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

Что лежит в колонке: дата или момент

DATE хранит только дату — «5 августа 2026», без часов. TIMESTAMP хранит дату вместе со временем: «5 августа 2026, 10:30:00». Разница кажется мелкой, но именно из-за неё ломаются фильтры.

В нашей песочнице маркетплейса время хранится вместе с датой: created_at у заказа, paid_at у оплаты, occurred_at у события. И вот что происходит:

live example

SELECT id, created_at FROM orders WHERE created_at = '2026-08-05';
-- 0 строк

Запрос верен синтаксически и бесполезен по сути. Строка '2026-08-05' для колонки со временем означает момент 2026-08-05 00:00:00 — ровно полночь. Ни один заказ не был создан ровно в полночь: они созданы в 09:00 и в 10:30. Равенство с датой сработало бы только для колонки типа DATE.

Есть ещё TIMESTAMPTZ. Название обманывает: часовой пояс внутри он не хранит. Он держит момент в UTC, а показывает его в поясе той сессии, которая спрашивает, — поэтому один и тот же заказ коллега в Москве и коллега в Новосибирске увидят с разным временем на часах, хотя в базе лежит одно значение. Для отчётов по одному городу разница незаметна, а как только появляются продавцы из разных часовых поясов, она становится главной. Подробности — в статье Время и таймзоны в PostgreSQL; здесь достаточно помнить, что в колонке лежит момент, а не «дата».

Сноска про песочницу курса: она работает в UTC, и это видно запросом SHOW timezone. Сделано нарочно — чтобы примеры давали одни и те же числа у всех, кто их запускает, независимо от того, где сидит читатель. На боевых базах так же поступают почти всегда: сервер держат в UTC, а в местное время переводят на экране. Поэтому заказ, созданный в Москве в 01:30, в базе лежит вчерашним числом — и отчёт «за сутки», написанный без учёта пояса, у московской команды разойдётся с ожиданием ровно на эти три часа.

День — это не точка, а отрезок

Раз в колонке момент, то «за пятое августа» — это все моменты от полуночи пятого до полуночи шестого. В SQL такой отрезок записывают двумя границами:

live example

SELECT id, customer_id, status, created_at
FROM orders
WHERE created_at >= '2026-08-05'
  AND created_at <  '2026-08-06'
ORDER BY created_at;

Начало включаем, конец — нет. Это называется полуоткрытый интервал, и он главный приём в работе с датами: у соседних дней нет ни щели, ни нахлёста.

Неделя пишется так же — двумя границами, а не семью условиями:

WHERE created_at >= '2026-08-01' AND created_at < '2026-08-08'

Почему не BETWEEN. BETWEEN '2026-08-01' AND '2026-08-07' включает обе границы, то есть заканчивается в полночь седьмого — и весь седьмой день, кроме первой секунды, теряется. Попытка починить это концом дня (AND '2026-08-07 23:59:59') выбрасывает последнюю секунду суток: заказ, созданный в 23:59:59.4, не попадёт никуда. BETWEEN хорош для дат без времени; для моментов надёжнее два знака сравнения.

А вот с колонкой типа DATE (или с приведением к ней) BETWEEN работает ровно так, как читается, потому что у даты нет времени и обе границы — целые дни:

live example

SELECT id, created_at::date AS day
FROM orders
WHERE created_at::date BETWEEN DATE '2026-08-01' AND DATE '2026-08-07'
ORDER BY day;

Оба конца включены, седьмое августа на месте. Цена такого удобства — в следующем разделе: приведение created_at::date считается функцией над колонкой, и обычный индекс по created_at для такого условия не работает. Поэтому правило звучит так: BETWEEN — для дат, полуоткрытый интервал — для моментов, и он же для больших таблиц.

Сегодня, вчера и «за последние семь дней»

Отчёты редко пишут с фиксированными датами: обычно нужно «от текущего момента назад». Для этого есть CURRENT_DATE — сегодняшняя дата без времени — и now() — текущий момент. К ним прибавляют и от них отнимают интервал:

-- заказы за последние 7 суток
WHERE created_at >= now() - interval '7 days'

-- заказы за сегодня
WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1

-- оплаты, которых нет дольше двух суток
WHERE paid_at IS NULL AND created_at < now() - interval '2 days'

interval '7 days' читается как есть и понимает всё то же самое в других единицах: '12 hours', '30 minutes', '1 mon'. Прибавление целого числа к дате — это прибавление дней: CURRENT_DATE + 1 — завтрашняя полночь.

Группировка по дням: отрезать время

«Сколько заказов создаётся в день» — вопрос про группировку, а GROUP BY группирует по точному значению. Моменты 10:30 и 10:31 — разные значения, и группировка по created_at даст почти столько же строк, сколько заказов. Значит, время нужно отрезать:

live example

SELECT CAST(created_at AS DATE) AS day,
       COUNT(*) AS orders
FROM orders
GROUP BY CAST(created_at AS DATE)
ORDER BY day;

CAST(created_at AS DATE) выбрасывает часы и оставляет дату — все заказы одного дня схлопываются в одну группу. Тот же приём короче пишется как created_at::date.

Когда нужен не день, а неделя или месяц, применяют date_trunc — он «усекает» момент до начала периода:

live example

SELECT date_trunc('month', created_at) AS month, SUM(total_amount) AS revenue
FROM orders
WHERE paid_at IS NOT NULL
GROUP BY date_trunc('month', created_at)
ORDER BY month;

date_trunc('month', '2026-08-14 10:30') даёт 2026-08-01 00:00:00: не число месяца, а момент его начала. Это удобно — по нему можно сортировать, и соседние месяцы разных лет не перемешаются, как перемешались бы номера месяцев.

С неделями всё то же, но добавляется договорённость о начале недели: в PostgreSQL неделя начинается с понедельника, поэтому date_trunc('week', DATE '2026-08-14') даёт 2026-08-10 — понедельник той недели. Это первый спор при недельных отчётах: в интерфейсах и в некоторых других базах неделя начинается с воскресенья, и тогда цифры за «неделю» у двух отчётов не сойдутся, хотя оба посчитали правильно. Нужна неделя с воскресенья — сдвигают дату на день: date_trunc('week', created_at + INTERVAL '1 day') - INTERVAL '1 day'.

Типичная ошибка — группировать по EXTRACT(MONTH FROM created_at). Функция возвращает просто число 8, и август 2025 года сольётся с августом 2026-го в одну строку. EXTRACT хорош, когда номер и нужен: «в какие часы суток чаще заказывают» — это EXTRACT(HOUR FROM created_at).

Вторая беда отчётов по дням — дни, в которые ничего не происходило. GROUP BY строит группы из того, что есть в таблице: если третьего августа заказов не было, строки за третье не появится вовсе, и график молча склеит второе с четвёртым — провал станет невидимым. Недостающие дни берут не из заказов, а из generate_series — функции, которая порождает ряд значений с заданным шагом:

live example

WITH days AS (
    SELECT generate_series(DATE '2026-08-01', DATE '2026-08-10', INTERVAL '1 day') AS d
)
SELECT d::date AS day, COUNT(o.id) AS orders
FROM days
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + INTERVAL '1 day'
GROUP BY d
ORDER BY day;

Ряд дат здесь — левая таблица, заказы присоединяются к ней, и COUNT(o.id) даёт честный ноль там, где заказов не было. Соединение обязано быть LEFT, а считать надо по колонке заказа, не COUNT(*): звёздочка посчитала бы саму строку-день и выдала единицу вместо нуля. Тем же приёмом делают «выручку по месяцам за год» с пустыми месяцами и «активность по часам суток» с провалом ночью.

Сколько времени прошло

Две даты можно просто вычесть друг из друга — в PostgreSQL получится целое число дней:

live example

SELECT id,
       status,
       DATE '2026-08-25' - CAST(created_at AS DATE) AS days_waiting
FROM orders
WHERE status IN ('PENDING_PAYMENT', 'EXPIRED')
ORDER BY days_waiting DESC;

Здесь считаются пересечённые границы суток, а не полные сутки: от 14 августа 09:00 до полуночи 25-го прошло десять суток с хвостиком, но границ дня пересечено одиннадцать.

Дата в примере зашита литералом DATE '2026-08-25' нарочно: так числа в выдаче не расползаются день ото дня и с ними можно сверяться. В настоящем отчёте на её месте стоит CURRENT_DATE.

С моментами арифметика другая: разность двух timestamp — это интервал (00:10:00), а не число. Чтобы получить минуты или секунды, из интервала достают секунды и делят:

live example

SELECT id,
       created_at,
       paid_at,
       (EXTRACT(EPOCH FROM (paid_at - created_at)) / 60)::int AS minutes_to_pay
FROM orders
WHERE paid_at IS NOT NULL
ORDER BY minutes_to_pay DESC;

EXTRACT(EPOCH FROM интервал) — это «сколько всего секунд в этом интервале». Делим на 60 — минуты, на 3600 — часы, на 86400 — сутки.

Оговорка на будущее: честно это работает для разности двух моментов — там интервал складывается из суток, часов и секунд. А у интервала, записанного в месяцах, точным секундам взяться неоткуда, и база считает месяц за тридцать суток: EXTRACT(EPOCH FROM interval '1 month') вернёт 2 592 000 секунд и в феврале, и в июле.

Два правила, на которых спотыкаются чаще всего. Первое: вычитаемое и уменьшаемое местами не меняют — created_at - paid_at даст отрицательные минуты. Второе: любое действие с NULL даёт NULL, поэтому неоплаченные заказы нужно отсечь условием paid_at IS NOT NULL, иначе они приедут в отчёт пустыми строками и испортят среднее (про это подробно — в статье NULL и типы данных).

Первый и последний заказ покупателя — это обычные агрегаты по времени:

live example

SELECT customer_id,
       MIN(created_at) AS first_order,
       MAX(created_at) AS last_order,
       CAST(MAX(created_at) AS DATE) - CAST(MIN(created_at) AS DATE) AS days_between
FROM orders
GROUP BY customer_id;

Отдельно про age. Эта функция возвращает не число, а интервал в человеческом виде: age(paid_at, created_at) даст 00:05:00, а для далёких дат — 1 mon 3 days. Это удобно показать в отчёте и неудобно считать: складывать и сравнивать такие интервалы можно, а делить на 60 уже нет, и «месяц» внутри них всё равно условный. Правило простое: человеку показываем age, считаем через EXTRACT(EPOCH FROM …). У age есть и однорукая форма — age(created_at), «сколько прошло от этой даты до сегодня».

И про то, как дата попадает в запрос из приложения. Строка '2026-08-05' — формат ISO, его PostgreSQL понимает всегда и одинаково. Строка '05.08.2026' разбирается по настройке DateStyle, а она у сервера по умолчанию ISO, MDY: сначала месяц. Запрос не упадёт — он молча поймёт это как восьмое мая, и SELECT DATE '05.08.2026' честно вернёт 2026-05-08. Такая ошибка не ловится тестами на однозначных датах ('01.02.2026' разберётся в любом порядке) и всплывает на числах больше двенадцати. Поэтому даты передают либо в ISO, либо параметром нужного типа через плейсхолдер драйвера ($1 у pgx, ? у JDBC, %s у psycopg), а не склейкой строк.

Почему быстрый запрос вдруг стал медленным

Условие с функцией на колонке база не может проверить по индексу. Такой запрос формально верен и работает — но читает всю таблицу:

-- индекс по created_at не поможет
WHERE CAST(created_at AS DATE) = '2026-08-05'
WHERE EXTRACT(YEAR FROM created_at) = 2026

База умеет искать по индексу, только если слева от знака сравнения стоит сама колонка. Поэтому фильтр записывают отрезком — тем самым полуоткрытым интервалом, с которого статья началась:

WHERE created_at >= '2026-08-05' AND created_at < '2026-08-06'

В SELECT и в GROUP BY функции по датам применять можно и нужно — там нет поиска. Опасно именно в WHERE. Подробнее про то, когда индекс работает, а когда нет — в статье Почему запрос медленный.

Коротко

• В колонке TIMESTAMP лежит момент, а не дата: равенство с '2026-08-05' означает полночь и почти всегда даёт ноль строк.
• День и неделя — это отрезок: >= начало и < следующая граница. BETWEEN для моментов прихватывает чужую полночь.
• «За последние N дней» — now() - interval '7 days'; сегодня — CURRENT_DATE.
• Группировка по дню — CAST(created_at AS DATE), по месяцу — date_trunc. EXTRACT(MONTH …) склеит августы разных лет.
• Разность дат — число дней; разность моментов — интервал, секунды из него достаёт EXTRACT(EPOCH FROM …).
• Функция на колонке в WHERE выключает индекс: фильтруйте отрезком.
• Дни без событий в отчёт не попадают: ряд дат берут из generate_series и присоединяют к нему данные через LEFT JOIN.
• Неделя в PostgreSQL начинается с понедельника; age — для показа человеку, EXTRACT(EPOCH …) — для арифметики; дату в запрос передают в ISO или параметром.

Что почитать дальше

• Агрегаты: COUNT, GROUP BY и HAVING — как считать по дням и фильтровать уже посчитанное.
• NULL и типы данных: где все спотыкаются — почему пустая дата портит арифметику и средние.
• Время и таймзоны в PostgreSQL — что менять, когда появляются разные часовые пояса.
• Почему запрос медленный: индексы на пальцах — что происходит с индексом, когда на колонку надевают функцию.