DATE_BIN в PostgreSQL: как группировать время по 15 минут, 6 часов и любому удобному шагу

DATE_BIN — это функция PostgreSQL для группировки времени по корзинам произвольной ширины.

Проще говоря, она отвечает на вопрос:

К какому интервалу относится эта дата и время?

Например, у нас есть событие:

2024-01-01 14:37:09

Если мы группируем события по 15 минут, эта метка попадёт в корзину:

2024-01-01 14:30:00

Потому что интервал с 14:30 до 14:45 — это та самая 15-минутная корзина, внутри которой находится событие.

DATE_BIN появился в PostgreSQL 14. До него такие корзины приходилось собирать вручную: переводить дату в секунды, делить на размер интервала, округлять вниз, потом собирать время обратно. Работало, но выглядело тяжело и неприятно для чтения.

С DATE_BIN всё стало намного проще.

Он особенно полезен для:

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

Если DATE_TRUNC — это аккуратная нарезка по стандартным единицам вроде часа, дня и месяца, то DATE_BIN — это нарезка по вашему собственному шагу: 5 минут, 15 минут, 6 часов, 10 дней и так далее.

Зачем нужен DATE_BIN

Допустим, у нас есть таблица заказов.

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL,
    amount numeric(10, 2) NOT NULL,
    status text NOT NULL,
    created_at timestamp NOT NULL
);

Мы хотим построить график: сколько оплаченных заказов было каждые 15 минут.

Обычный человек формулирует задачу так:

Разложи все заказы по 15-минутным отрезкам и посчитай количество заказов в каждом отрезке.

В PostgreSQL это можно сделать так:

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count,
    SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;

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

bucket              | orders_count | revenue
--------------------+--------------+---------
2024-01-01 10:00:00 | 12           | 1840.00
2024-01-01 10:15:00 | 9            | 1260.00
2024-01-01 10:30:00 | 17           | 3100.00

Каждая строка — это одна временная корзина.

Синтаксис DATE_BIN

У DATE_BIN три аргумента:

DATE_BIN(stride, source, origin)

Разберём их по-человечески.

stride — ширина корзины.

Например:

INTERVAL '15 minutes'

или:

INTERVAL '6 hours'

source — дата и время, которые нужно положить в корзину.

Например:

created_at

origin — точка отсчёта, от которой PostgreSQL начинает нарезать временную линейку на интервалы.

Например:

TIMESTAMP '2024-01-01'

Полный пример:

SELECT DATE_BIN(
    INTERVAL '15 minutes',
    TIMESTAMP '2024-01-01 14:37:09',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-01-01 14:30:00

Событие произошло в 14:37:09. Если нарезать день на куски по 15 минут, оно попадает в интервал, который начинается в 14:30.

Простая аналогия с линейкой

Представьте длинную линейку времени.

На ней отмечены деления:

14:00
14:15
14:30
14:45
15:00

Событие произошло в 14:37.

DATE_BIN смотрит на линейку и говорит:

14:37 лежит между 14:30 и 14:45. Значит, начало корзины — 14:30.

Именно начало корзины функция и возвращает.

Это похоже на округление вниз.

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

Чем DATE_BIN отличается от DATE_TRUNC

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

Например:

SELECT DATE_TRUNC('hour', TIMESTAMP '2024-01-01 14:37:09');

Результат:

2024-01-01 14:00:00

Можно округлить до часа, дня, месяца, года.

Но нельзя красиво сказать:

round to 15 minutes

или:

round to 6 hours

Вот здесь и нужен DATE_BIN.

Например, корзина по 15 минут:

SELECT DATE_BIN(
    INTERVAL '15 minutes',
    TIMESTAMP '2024-01-01 14:37:09',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-01-01 14:30:00

Корзина по 6 часов:

SELECT DATE_BIN(
    INTERVAL '6 hours',
    TIMESTAMP '2024-01-01 14:37:09',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-01-01 12:00:00

Потому что если день нарезан на интервалы по 6 часов, то будут такие корзины:

00:00
06:00
12:00
18:00

Время 14:37 попадает в корзину, которая начинается в 12:00.

Когда использовать DATE_TRUNC, а когда DATE_BIN

Если вам нужны обычные календарные единицы, чаще подходит DATE_TRUNC.

Например:

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

Это хороший запрос для отчёта по дням.

Для отчёта по месяцам:

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

А вот если нужен нестандартный шаг, берите DATE_BIN.

Например, по 15 минут:

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
GROUP BY bucket
ORDER BY bucket;

По 6 часов:

SELECT
    DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
GROUP BY bucket
ORDER BY bucket;

По 10 дней:

SELECT
    DATE_BIN(INTERVAL '10 days', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
GROUP BY bucket
ORDER BY bucket;

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

• DATE_TRUNC — для часа, дня, недели, месяца, года;
• DATE_BIN — для произвольного фиксированного шага.

Пример: заказы по 15 минут

Допустим, нужно построить график нагрузки магазина: сколько заказов приходит каждые 15 минут.

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
WHERE created_at >= TIMESTAMP '2024-06-01 00:00:00'
  AND created_at < TIMESTAMP '2024-06-02 00:00:00'
GROUP BY bucket
ORDER BY bucket;

Такой запрос удобен для графика.

Он вернёт строки вида:

bucket              | orders_count
--------------------+-------------
2024-06-01 00:00:00 | 3
2024-06-01 00:15:00 | 7
2024-06-01 00:30:00 | 5

Каждая строка — это один 15-минутный отрезок.

Пример: выручка по 30 минут

Теперь посчитаем не только количество заказов, но и выручку.

SELECT
    DATE_BIN(INTERVAL '30 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count,
    SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;

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

Например:

10:00 | 18 orders | 4200.00
10:30 | 22 orders | 5100.00
11:00 | 15 orders | 2900.00

Для бизнеса это гораздо нагляднее, чем просто общая сумма за день.

Пример: регистрации по 6 часов

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

SELECT
    DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01') AS shift,
    COUNT(*) AS signups
FROM users
GROUP BY shift
ORDER BY shift;

Получатся корзины:

00:00
06:00
12:00
18:00

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

Зачем нужен origin

Третий аргумент DATE_BIN называется origin.

Он задаёт точку, от которой PostgreSQL начинает нарезать время на корзины.

Например:

SELECT DATE_BIN(
    INTERVAL '15 minutes',
    TIMESTAMP '2024-01-01 14:37:09',
    TIMESTAMP '2024-01-01'
);

Здесь origin — это:

TIMESTAMP '2024-01-01'

Для интервала в 15 минут обычно не так важно, какой именно день взять как точку отсчёта. Корзины всё равно красиво попадут на 00, 15, 30 и 45 минут.

Но для более необычных интервалов origin становится очень важным.

Например, корзины по 7 дней:

SELECT DATE_BIN(
    INTERVAL '7 days',
    TIMESTAMP '2024-03-20 10:00:00',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-03-18 00:00:00

Почему так?

Потому что PostgreSQL начинает отсчёт недель не «по календарю вообще», а от указанного origin.

Если origin — 1 января, то каждая 7-дневная корзина строится от этой даты.

Origin задаёт фазу сетки

Представьте две линейки.

Первая начинается в полночь:

00:00
06:00
12:00
18:00

Вторая начинается в 09:00:

09:00
15:00
21:00
03:00

Шаг у обеих одинаковый — 6 часов. Но корзины разные, потому что разная точка отсчёта.

Это и есть фаза сетки.

Например, если рабочий день в компании начинается в 09:00, можно задать такой origin:

SELECT DATE_BIN(
    INTERVAL '24 hours',
    created_at,
    TIMESTAMP '2024-01-01 09:00:00'
) AS business_day
FROM orders;

Теперь сутки будут считаться не с полуночи, а с 09:00.

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

Важно использовать один и тот же origin

Если в разных отчётах использовать разные значения origin, корзины могут не совпасть.

Например, в одном запросе:

DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01')

а в другом:

DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01 03:00:00')

Шаг одинаковый, но сетка другая.

В первом случае корзины будут начинаться в 00:00, 06:00, 12:00, 18:00.

Во втором — в 03:00, 09:00, 15:00, 21:00.

Поэтому для стабильных метрик лучше договориться о едином origin и использовать его везде.

Ограничение: нельзя использовать месяцы и годы

У DATE_BIN есть важное ограничение: ширина корзины должна быть фиксированной.

Подходят:

INTERVAL '15 minutes'

INTERVAL '6 hours'

INTERVAL '10 days'

Не подходят:

INTERVAL '1 month'

INTERVAL '1 year'

Почему?

Потому что месяц — не фиксированная величина. В январе 31 день, в феврале 28 или 29, в апреле 30.

DATE_BIN работает с ровной линейкой времени, где корзина имеет постоянную длину.

Для месяцев используйте DATE_TRUNC.

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

Типы timestamp и timestamptz

В PostgreSQL есть два похожих типа:

• timestamp;
• timestamptz.

timestamp хранит дату и время без часового пояса.

timestamptz хранит момент времени с учётом часового пояса.

При использовании DATE_BIN важно, чтобы source и origin были согласованы по типу.

Если created_at имеет тип timestamp, используйте TIMESTAMP для origin.

SELECT
    DATE_BIN(
        INTERVAL '15 minutes',
        created_at,
        TIMESTAMP '2024-01-01'
    ) AS bucket
FROM orders;

Если created_at имеет тип timestamptz, используйте TIMESTAMPTZ.

SELECT
    DATE_BIN(
        INTERVAL '15 minutes',
        created_at,
        TIMESTAMPTZ '2024-01-01 00:00:00+00'
    ) AS bucket
FROM orders;

Так запрос будет понятнее и безопаснее.

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

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

Если created_at хранится как timestamptz, PostgreSQL работает с конкретным моментом времени. Отображение этого момента может зависеть от часового пояса сессии.

Для интервалов вроде 15 минут это обычно не вызывает проблем.

Но для корзин вроде 6 часов, 24 часа или «рабочие сутки с 09:00» важно понимать, в каком часовом поясе вы хотите строить отчёт.

Допустим, заказы хранятся в timestamptz, а отчёт нужен по московскому времени.

Можно сначала перевести время в локальное представление, а потом применить DATE_BIN.

SELECT
    DATE_BIN(
        INTERVAL '6 hours',
        created_at AT TIME ZONE 'Europe/Moscow',
        TIMESTAMP '2024-01-01'
    ) AS shift_msk,
    COUNT(*) AS orders_count
FROM orders
GROUP BY shift_msk
ORDER BY shift_msk;

Здесь created_at AT TIME ZONE 'Europe/Moscow' превращает момент времени в локальное московское время без часового пояса, и корзины строятся уже по этой локальной сетке.

Это особенно важно на границах суток и при переходах на летнее время в тех странах, где он есть.

DATE_BIN и WHERE: как не потерять индекс

DATE_BIN часто используют в GROUP BY.

Это нормально:

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
GROUP BY bucket
ORDER BY bucket;

Но есть важный момент про производительность.

Если вы фильтруете данные по дате, лучше оставлять колонку в WHERE в чистом виде.

Хорошо:

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
WHERE created_at >= TIMESTAMP '2024-06-01 00:00:00'
  AND created_at < TIMESTAMP '2024-06-02 00:00:00'
GROUP BY bucket
ORDER BY bucket;

Здесь условие написано по голой колонке created_at.

Если по created_at есть индекс, планировщик может его использовать для отбора нужного диапазона.

Плохо:

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS orders_count
FROM orders
WHERE DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') >= TIMESTAMP '2024-06-01 00:00:00'
GROUP BY bucket
ORDER BY bucket;

Здесь функция применяется к каждой строке в WHERE. Такой фильтр хуже дружит с обычным индексом по created_at.

Хорошая привычка:

• в WHERE фильтруем по исходной колонке;
• в SELECT и GROUP BY применяем DATE_BIN уже к отобранным строкам.

DATE_BIN не заполняет пропуски сам

DATE_BIN группирует существующие строки.

Если в какой-то 15-минутке не было заказов, строки за эту корзину в результате не будет.

Например, если в 10:15 заказов не было, запрос вернёт:

10:00 | 5
10:30 | 8

А строки 10:15 | 0 не будет.

Если для графика нужны все интервалы, даже пустые, нужно отдельно сгенерировать календарь корзин через generate_series, а потом сделать LEFT JOIN.

WITH buckets AS (
    SELECT generate_series(
        TIMESTAMP '2024-06-01 00:00:00',
        TIMESTAMP '2024-06-01 23:45:00',
        INTERVAL '15 minutes'
    ) AS bucket
),
orders_by_bucket AS (
    SELECT
        DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
        COUNT(*) AS orders_count
    FROM orders
    WHERE created_at >= TIMESTAMP '2024-06-01 00:00:00'
      AND created_at < TIMESTAMP '2024-06-02 00:00:00'
    GROUP BY bucket
)
SELECT
    b.bucket,
    COALESCE(o.orders_count, 0) AS orders_count
FROM buckets AS b
LEFT JOIN orders_by_bucket AS o ON o.bucket = b.bucket
ORDER BY b.bucket;

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

TimescaleDB: time_bucket

В экосистеме PostgreSQL есть расширение TimescaleDB, которое часто используют для временных рядов.

В нём есть функция time_bucket.

По смыслу она похожа на DATE_BIN.

SELECT
    time_bucket(INTERVAL '15 minutes', created_at) AS bucket,
    SUM(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;

time_bucket появился раньше и предлагает больше возможностей для задач с временными рядами.

Например, в TimescaleDB есть удобные инструменты для месячных интервалов, смещений и работы с часовыми поясами.

Если у вас чистый PostgreSQL без расширений, используйте DATE_BIN.

Если вы работаете с TimescaleDB и временными рядами, часто удобнее использовать time_bucket.

MySQL: произвольные корзины вручную

В MySQL нет прямого аналога DATE_BIN.

Для 15-минутных корзин часто используют арифметику через Unix-время.

15 минут — это 900 секунд.

SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(created_at) / 900) * 900) AS bucket,
    COUNT(*) AS orders_count
FROM orders
GROUP BY bucket
ORDER BY bucket;

Работает, но читается тяжелее.

Если нужно добавить свою точку отсчёта, как origin в PostgreSQL, выражение становится ещё сложнее.

Именно поэтому DATE_BIN приятен: он прячет эту арифметику в понятную функцию.

ClickHouse: toStartOfInterval

В ClickHouse похожую задачу решают через toStartOfInterval.

Например, 15-минутные корзины:

SELECT
    toStartOfInterval(created_at, INTERVAL 15 MINUTE) AS bucket,
    sum(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;

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

Но при переносе запросов между PostgreSQL, MySQL и ClickHouse важно проверять детали:

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

На «середине дня» всё может выглядеть одинаково, а на границах корзин внезапно появятся расхождения.

Как проверять DATE_BIN на краевых случаях

Когда вы пишете запрос с временными корзинами, полезно проверить несколько точек руками.

Например, для интервала 15 минут:

SELECT DATE_BIN(
    INTERVAL '15 minutes',
    TIMESTAMP '2024-01-01 14:30:00',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-01-01 14:30:00

Если событие ровно на границе корзины, оно попадает в эту корзину.

Теперь точка внутри корзины:

SELECT DATE_BIN(
    INTERVAL '15 minutes',
    TIMESTAMP '2024-01-01 14:44:59',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-01-01 14:30:00

А вот следующая секунда уже попадёт в новую корзину:

SELECT DATE_BIN(
    INTERVAL '15 minutes',
    TIMESTAMP '2024-01-01 14:45:00',
    TIMESTAMP '2024-01-01'
);

Результат:

2024-01-01 14:45:00

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

Практический шаблон для метрик

Вот универсальный шаблон для отчёта по произвольным временным корзинам:

SELECT
    DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
    COUNT(*) AS events_count
FROM events
WHERE created_at >= TIMESTAMP '2024-06-01 00:00:00'
  AND created_at < TIMESTAMP '2024-06-02 00:00:00'
GROUP BY bucket
ORDER BY bucket;

Если нужно поменять шаг, меняете только первый аргумент:

INTERVAL '5 minutes'

или:

INTERVAL '1 hour'

или:

INTERVAL '6 hours'

А если нужно изменить фазу сетки, меняете origin.

Например, рабочие сутки с 09:00:

SELECT
    DATE_BIN(INTERVAL '24 hours', created_at, TIMESTAMP '2024-01-01 09:00:00') AS business_day,
    COUNT(*) AS orders_count
FROM orders
GROUP BY business_day
ORDER BY business_day;

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

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

Например, DATE_TRUNC хорош для часа, но не для 15 минут.

Вторая ошибка — забыть про origin.

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

Третья ошибка — использовать месяцы в DATE_BIN.

SELECT DATE_BIN(
    INTERVAL '1 month',
    TIMESTAMP '2024-03-20',
    TIMESTAMP '2024-01-01'
);

Так делать нельзя. Для месяцев используйте DATE_TRUNC.

Четвёртая ошибка — применять функцию в WHERE вместо фильтра по исходной колонке.

Лучше фильтровать так:

WHERE created_at >= TIMESTAMP '2024-06-01'
  AND created_at < TIMESTAMP '2024-07-01'

а не заворачивать created_at в функцию внутри условия.

Пятая ошибка — ожидать, что DATE_BIN сам покажет пустые интервалы. Не покажет. Для пустых корзин нужен generate_series и LEFT JOIN.

Главное

DATE_BIN группирует дату и время по корзинам произвольной фиксированной ширины.

Функция появилась в PostgreSQL 14 и принимает три аргумента: ширину корзины, исходную метку времени и точку отсчёта.

DATE_BIN возвращает начало корзины, в которую попало значение.

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

Для нестандартного шага вроде 15 минут, 6 часов или 10 дней удобнее использовать DATE_BIN.

origin задаёт фазу сетки. Если использовать разные значения origin, корзины в отчётах могут не совпасть.

Месяцы и годы в DATE_BIN использовать нельзя, потому что это интервалы непостоянной длины.

С часовыми поясами нужно быть аккуратным: для локальных отчётов иногда нужно сначала привести время к нужному часовому поясу.

Для производительности фильтруйте диапазон по исходной колонке created_at, а DATE_BIN применяйте уже в SELECT и GROUP BY.

DATE_BIN не создаёт пустые интервалы сам. Если нужны нули на графике, генерируйте ряд корзин через generate_series и соединяйте его с агрегированными данными.

Главная мысль простая: DATE_TRUNC хорош для привычного календаря, а DATE_BIN — для собственной линейки времени. Когда метрику нужно считать каждые 5, 15 или 30 минут, DATE_BIN превращает сложную временную арифметику в один понятный вызов.