Какой самый быстрый способ урезать метки времени до 5 минут в Postgres?

Postgres может округлять (усекать) временные метки с помощью функции date_trunc, например:

date_trunc('hour', val)
date_trunc('minute', val)

Я ищу способ обрезать временную метку до ближайшей 5-минутной границы, чтобы, например, 14:26:57 стало 14:25:00. Простой способ сделать это так:

date_trunc('hour', val) + date_part('minute', val)::int / 5 * interval '5 min'

Так как это критичная для производительности часть запроса, мне интересно, является ли это самым быстрым решением или есть какой-то ярлык (совместимый с Postgres 8.1+), который я пропустил.

Источник

4 ответа

Решение

Я не думаю, что есть более быстрый метод.

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

Все остальное, что задействовано в выполнении вашего оператора (SELECT, UPDATE, ...), скорее всего, намного дороже (например, ввод-вывод для получения строк), чем вычисление даты / времени.

Источник

Я думал о том же самом. Я нашел два альтернативных способа сделать это, но тот, который вы предложили, был быстрее.

Я неофициально сравнил один из наших больших столов. Я ограничил запрос первыми 4 миллионами строк. Я чередовал два запроса, чтобы избежать несправедливого преимущества из-за кеширования БД.

Проходя через эпоху / юникс

SELECT to_timestamp(
    (EXTRACT(epoch FROM ht.time) / EXTRACT(epoch FROM interval '5 min'))::int 
    * EXTRACT(epoch FROM interval '5 min')
) FROM huge_table AS ht LIMIT 4000000

(Обратите внимание, что это производит timestamptzдаже если вы использовали часовой пояс, не зная тип данных)

Результаты

  • Прогон 1: 39,368 секунды
  • Прогон 3: 39,526 секунды
  • Прогон 5: 39,883 секунды

Использование date_trunc и date_part

SELECT 
    date_trunc('hour', ht.time) 
    + date_part('minute', ht.time)::int / 5 * interval '5 min'
FROM huge_table AS ht LIMIT 4000000

Результаты

  • Прогон 2: 34,189 секунды
  • Прогон 4: 37,028 секунды
  • Прогон 6: 32,397 секунды

система

  • Версия БД: PostgreSQL 9.6.2 на x86_64-pc-linux-gnu, скомпилированная gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-битная
  • Ядра: Intel® Xeon®, E5-1650v2, Hexa-Core
  • Оперативная память: 64 ГБ, оперативная память DDR3 ECC

Заключение

Ваша версия кажется быстрее. Но не достаточно быстро для моего конкретного случая использования. Преимущество отсутствия необходимости указывать час делает версию эпохи более универсальной и упрощает параметризацию в коде на стороне клиента. Это обрабатывает 2 hour интервалы так же, как 5 minute интервалы без необходимости сталкиваться date_trunc аргумент единицы времени вверх. В заключение я бы хотел, чтобы вместо этого аргумент единицы времени был изменен на аргумент временного интервала.

Источник

Начиная с Постгреса 14,самый простой и быстрый :

      date_bin('5 min', val, '2000-1-1')

date_bin()Руководство:

Функция «складывает» входную метку времени в указанный интервал (шаг), выровненный с указанным источником.

date_bin(, ,origin)

является выражением значения типа или . (Значения типа автоматически преобразуются в .) strideявляется выражением значения интервального типа. Возвращаемое значение также имеет тип илиtimestamp with time zone, и он отмечает начало корзины, в которую sourceразмещен.

Укажите «источник» соответствующего типа данных, чтобы избежать неожиданных результатов из-за того, что приведение игнорирует часовые пояса или предполагает неправильный.

Мой пример выглядит какdateбуквальный, но служит действительнымtimestampбуквально тоже. Если компонент времени отсутствует, предполагается «00:00».

Связанный:

Источник

Полный запрос для тех, кто интересуется (на основе вопроса @DNS):

Предполагая, что у вас есть заказы, и вы хотите посчитать их по кусочкам 5 минут и shop_id:

SELECT date_trunc('hour', created_at) + date_part('minute', created_at)::int / 5 * interval '5 min' AS minute
      , shop_id, count(id) as orders_count
FROM orders
GROUP BY 1, shop_id
ORDER BY 1 ASC
Источник
Другие вопросы по тегам