interval - timestamp postgresql ejemplo

¿Cuál es la forma más rápida de truncar marcas de tiempo a 5 minutos en Postgres?

Question

Postgres puede redondear (truncar) las marcas de tiempo usando la función date_trunc, como esto:

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

Estoy buscando una manera de truncar una marca de tiempo al límite de 5 minutos más cercano, por ejemplo, 14:26:57 se convierte en 14:25:00. La forma más sencilla de hacerlo es así:

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

Dado que esta es una parte crítica de la consulta, me pregunto si esta es la solución más rápida o si hay algún método abreviado (compatible con Postgres 8.1+) que he pasado por alto.

Answer 1

Consulta completa para aquellos que se preguntan (basado en la pregunta @DNS):

Suponiendo que tiene pedidos y desea contarlos por partes de 5min y 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

Answer 2

Me preguntaba lo mismo. Encontré dos formas alternativas de hacer esto, pero la que sugeriste fue más rápida.

Realicé pruebas informales comparativamente con una de nuestras mesas más grandes. Limité la consulta a los primeros 4 millones de filas. Alterné entre las dos consultas para evitar darle una ventaja injusta debido al almacenamiento en caché de la base de datos.

Pasando por época / tiempo unix

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

(Tenga en cuenta que esto produce timestamptz incluso si usó un tipo de datos no consciente de la zona horaria)

Resultados

• Ejecutar 1 : 39.368 segundos
• Ejecutar 3 : 39.526 segundos
• Ejecutar 5 : 39.883 segundos

Usando date_trunc y 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

Resultados

• Ejecutar 2 : 34.189 segundos
• Ejecuta 4 : 37.028 segundos
• Ejecutar 6 : 32.397 segundos

Sistema

• Versión DB: PostgreSQL 9.6.2 en x86_64-pc-linux-gnu, compilado por gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, de 64 bits
• Núcleos: Intel® Xeon®, E5-1650v2, Hexa-Core
• RAM: 64 GB, DDR3 ECC RAM

Conclusión

Tu versión parece ser más rápida. Pero no lo suficientemente rápido para mi caso de uso específico. La ventaja de no tener que especificar la hora hace que la versión de época sea más versátil y produzca una parametrización más sencilla en el código del lado del cliente. Maneja intervalos de 2 hour tan bien como intervalos de 5 minute sin tener que date_trunc argumento de unidad de tiempo date_trunc . En una nota final, deseo que este argumento de unidad de tiempo se haya cambiado a un argumento de intervalo de tiempo en su lugar.

Answer 3

No creo que haya ningún método más rápido.

Y no creo que debas preocuparte por el rendimiento de la expresión.

Todo lo demás relacionado con la ejecución de su sentencia (SELECT, UPDATE, ...) es probablemente mucho más costoso (por ejemplo, la E / S para recuperar filas) que el cálculo de fecha / hora.