Тази статия в момента е на руски — английският превод е в процес на изготвяне.
DATE_BIN появился в PostgreSQL 14 и решает то, чего не умеет DATE_TRUNC: он привязывает метку времени к началу корзины любой ширины — 15 минут, 6 часов, 10 дней — отсчитывая её от заданной точки. Это незаменимый инструмент для метрик с нестандартным шагом.
До PostgreSQL 14 корзину в 5, 10 или 15 минут приходилось собирать вручную: вытаскивать EXTRACT(EPOCH ...), делить на ширину корзины, округлять вниз и собирать метку времени обратно. DATE_BIN сворачивает всю эту арифметику в один вызов и делает её детерминированной: каждое событие привязывается к одной и той же сетке корзин от заданного origin. Именно поэтому функция так удобна для продуктовых метрик, мониторинга, биллинга и событийных витрин с нестандартным шагом — там, где DATE_TRUNC даёт только час, день или месяц.
Синтаксис и интуиция
Сигнатура простая: DATE_BIN(stride, source, origin). Первый аргумент — interval (ширина корзины), второй — метка времени, которую раскладываем по корзинам, третий — origin, точка, от которой нарезаются интервалы.
SELECT DATE_BIN(
INTERVAL '15 minutes',
TIMESTAMP '2024-01-01 14:37:09',
TIMESTAMP '2024-01-01'
);
Идея: представьте бесконечную линейку из корзин шириной stride, начинающуюся в origin. DATE_BIN находит корзину, в которую попадает source, и возвращает её левую границу.
- Округление всегда вниз, как и у
DATE_TRUNC.
- Результат «прилипает» к сетке
origin, а не к началу часа или суток.
stride должен быть целым числом фиксированных единиц времени (секунды, минуты, часы, дни, недели).
Где DATE_TRUNC бессилен
DATE_TRUNC знает только фиксированный набор полей: час, день, месяц. Корзину по 15 минут или по 6 часов им не построить. С DATE_BIN это одна строка:
SELECT
DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;
Так же легко считать активность по корзинам в 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;
Меняете только stride — и получаете любой шаг без подзапросов и арифметики по EXTRACT.
Зачем нужен origin
origin задаёт фазу сетки. Для 15 минут или часа это почти всегда безразлично, но как только stride не делит сутки нацело, точка отсчёта становится критичной.
SELECT DATE_BIN(
INTERVAL '7 days',
TIMESTAMP '2024-03-20 10:00:00',
TIMESTAMP '2024-01-01'
);
Здесь недели нарезаются от 1 января, а не от понедельника по ISO. Сдвинув origin на нужный день недели, вы получите «недели», начинающиеся именно тогда, когда нужно бизнесу.
- Один и тот же
origin для всех запросов — корзины будут совпадать между отчётами.
- Для смещённых суток (рабочий день с 9 утра) задайте
origin вроде TIMESTAMP '2024-01-01 09:00:00'.
Ловушки: типы и часовые пояса
Главная засада — несогласованность типов. Если source имеет тип timestamptz, то и origin должен быть timestamptz, иначе будет ошибка. А ещё для timestamptz бины считаются в UTC, поэтому корзины «6 часов» поедут относительно местного времени.
SELECT
DATE_BIN(
INTERVAL '6 hours',
created_at AT TIME ZONE 'Europe/Moscow',
TIMESTAMP '2024-01-01'
) AS shift_msk,
COUNT(*)
FROM orders
GROUP BY shift_msk;
Ещё пара граблей:
- Месяцы и годы запрещены:
INTERVAL '1 month' вызовет ошибку, ведь его длина непостоянна. Для месяцев используйте DATE_TRUNC.
DATE_BIN появился только в PostgreSQL 14 — на 13 и ниже его нет.
Аналоги: TimescaleDB и ClickHouse
DATE_BIN хорош, но в экосистеме есть и более гибкие варианты.
- TimescaleDB даёт
time_bucket, прародителя DATE_BIN. У него совместимый смысл, но богаче возможности: поддержка месячных интервалов, смещения offset и часовых поясов прямо в аргументах.
SELECT
time_bucket(INTERVAL '15 minutes', created_at) AS bucket,
SUM(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;
- ClickHouse решает ту же задачу через
toStartOfInterval:
SELECT
toStartOfInterval(created_at, INTERVAL 15 MINUTE) AS bucket,
sum(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;
В MySQL прямого аналога DATE_BIN нет вплоть до 8.x: корзину произвольной ширины собирают вручную через FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(created_at) / 900) * 900), где 900 — это 15 минут в секундах, а смещение фазы под origin приходится добавлять руками. Расхождения между движками почти всегда сидят именно в краевых случаях: origin и фаза сетки, часовой пояс и UTC у timestamptz, запрет месячных интервалов. Перенося группировку между PostgreSQL, MySQL и ClickHouse, сверяйте результат на пограничных метках — полночь, граница корзины, переход на летнее время — а не только на «удобных» серединах интервалов.
И помните про производительность: DATE_BIN(...) над колонкой created_at в GROUP BY вычисляется построчно и не использует индекс по created_at для самой группировки. Если запрос ещё и фильтрует по диапазону дат, держите условие в WHERE created_at >= ... AND created_at < ... на голой колонке, чтобы планировщик мог взять индекс, а DATE_BIN применяйте только к уже отобранным строкам.
Итог: DATE_TRUNC — для стандартных единиц, DATE_BIN — для произвольного шага на чистом PostgreSQL, а time_bucket — когда нужны месяцы, смещения и пояса из коробки.
DATE_BINпоявился в PostgreSQL 14 и решает то, чего не умеетDATE_TRUNC: он привязывает метку времени к началу корзины любой ширины — 15 минут, 6 часов, 10 дней — отсчитывая её от заданной точки. Это незаменимый инструмент для метрик с нестандартным шагом.До PostgreSQL 14 корзину в 5, 10 или 15 минут приходилось собирать вручную: вытаскивать
EXTRACT(EPOCH ...), делить на ширину корзины, округлять вниз и собирать метку времени обратно.DATE_BINсворачивает всю эту арифметику в один вызов и делает её детерминированной: каждое событие привязывается к одной и той же сетке корзин от заданногоorigin. Именно поэтому функция так удобна для продуктовых метрик, мониторинга, биллинга и событийных витрин с нестандартным шагом — там, гдеDATE_TRUNCдаёт только час, день или месяц.Синтаксис и интуиция
Сигнатура простая:
DATE_BIN(stride, source, origin). Первый аргумент —interval(ширина корзины), второй — метка времени, которую раскладываем по корзинам, третий —origin, точка, от которой нарезаются интервалы.SELECT DATE_BIN( INTERVAL '15 minutes', TIMESTAMP '2024-01-01 14:37:09', TIMESTAMP '2024-01-01' ); -- 2024-01-01 14:30:00Идея: представьте бесконечную линейку из корзин шириной
stride, начинающуюся вorigin.DATE_BINнаходит корзину, в которую попадаетsource, и возвращает её левую границу.DATE_TRUNC.origin, а не к началу часа или суток.strideдолжен быть целым числом фиксированных единиц времени (секунды, минуты, часы, дни, недели).Где DATE_TRUNC бессилен
DATE_TRUNCзнает только фиксированный набор полей: час, день, месяц. Корзину по 15 минут или по 6 часов им не построить. СDATE_BINэто одна строка:SELECT DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket, COUNT(*) AS orders, SUM(amount) AS revenue FROM orders WHERE status = 'paid' GROUP BY bucket ORDER BY bucket;Так же легко считать активность по корзинам в 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;Меняете только
stride— и получаете любой шаг без подзапросов и арифметики поEXTRACT.Зачем нужен origin
originзадаёт фазу сетки. Для 15 минут или часа это почти всегда безразлично, но как толькоstrideне делит сутки нацело, точка отсчёта становится критичной.SELECT DATE_BIN( INTERVAL '7 days', TIMESTAMP '2024-03-20 10:00:00', TIMESTAMP '2024-01-01' ); -- 2024-03-18 00:00:00Здесь недели нарезаются от 1 января, а не от понедельника по ISO. Сдвинув
originна нужный день недели, вы получите «недели», начинающиеся именно тогда, когда нужно бизнесу.originдля всех запросов — корзины будут совпадать между отчётами.originвродеTIMESTAMP '2024-01-01 09:00:00'.Ловушки: типы и часовые пояса
Главная засада — несогласованность типов. Если
sourceимеет типtimestamptz, то иoriginдолжен бытьtimestamptz, иначе будет ошибка. А ещё дляtimestamptzбины считаются в UTC, поэтому корзины «6 часов» поедут относительно местного времени.SELECT DATE_BIN( INTERVAL '6 hours', created_at AT TIME ZONE 'Europe/Moscow', TIMESTAMP '2024-01-01' ) AS shift_msk, COUNT(*) FROM orders GROUP BY shift_msk;Ещё пара граблей:
INTERVAL '1 month'вызовет ошибку, ведь его длина непостоянна. Для месяцев используйтеDATE_TRUNC.DATE_BINпоявился только в PostgreSQL 14 — на 13 и ниже его нет.Аналоги: TimescaleDB и ClickHouse
DATE_BINхорош, но в экосистеме есть и более гибкие варианты.time_bucket, прародителяDATE_BIN. У него совместимый смысл, но богаче возможности: поддержка месячных интервалов, смещенияoffsetи часовых поясов прямо в аргументах.SELECT time_bucket(INTERVAL '15 minutes', created_at) AS bucket, SUM(amount) AS revenue FROM orders GROUP BY bucket ORDER BY bucket;toStartOfInterval:SELECT toStartOfInterval(created_at, INTERVAL 15 MINUTE) AS bucket, sum(amount) AS revenue FROM orders GROUP BY bucket ORDER BY bucket;В MySQL прямого аналога
DATE_BINнет вплоть до 8.x: корзину произвольной ширины собирают вручную черезFROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(created_at) / 900) * 900), где 900 — это 15 минут в секундах, а смещение фазы подoriginприходится добавлять руками. Расхождения между движками почти всегда сидят именно в краевых случаях:originи фаза сетки, часовой пояс и UTC уtimestamptz, запрет месячных интервалов. Перенося группировку между PostgreSQL, MySQL и ClickHouse, сверяйте результат на пограничных метках — полночь, граница корзины, переход на летнее время — а не только на «удобных» серединах интервалов.И помните про производительность:
DATE_BIN(...)над колонкойcreated_atвGROUP BYвычисляется построчно и не использует индекс поcreated_atдля самой группировки. Если запрос ещё и фильтрует по диапазону дат, держите условие вWHERE created_at >= ... AND created_at < ...на голой колонке, чтобы планировщик мог взять индекс, аDATE_BINприменяйте только к уже отобранным строкам.Итог:
DATE_TRUNC— для стандартных единиц,DATE_BIN— для произвольного шага на чистом PostgreSQL, аtime_bucket— когда нужны месяцы, смещения и пояса из коробки.