agrupar por intervalos de timestamp

[marranxo]

Hola,

Vengo a plantearos la siguiente cuestión que me tiene desquiciado. Estoy realizando un seguimiento de valores en el tiempo y necesito realizar una consulta que me permita agrupar los datos en intervalos de N tiempo (horas + minutos).

Tabla: events
- value : bigint
- time : timestamp without timezone


select avg(value) as value,date_trunc('minute', time) as date
from events
where ....
group by input,date_trunc('minute', time)
order by date_trunc('minute', time)

Un saludo y gracias de antemano.

[huesos52]

Podrías explicar con un ejemplo concreto los resultados que buscarías basado en unos datos existentes?

saludos

[marranxo]

Imaginemos que queremos agrupar por un intervalo de 45minutos los siguientes registros:

time value
2011-03-01 20:04:00 8.3
2011-03-01 20:14:00 7.1
2011-03-01 20:28:00 6.3
2011-03-01 20:44:00 5.8
2011-03-01 20:46:00 9.1
2011-03-01 20:53:00 13.5
2011-03-01 21:28:00 8.6
2011-03-01 21:32:00 6.7
2011-03-01 21:39:00 6.2
2011-03-01 21:48:00 5.1
2011-03-01 21:59:00 3.0
2011-03-01 22:14:00 3.6
2011-03-01 22:16:00 4.3

el resultado podría ser el siguiente:

2011-03-01 20:00:00 avg(8.3, 7.1, 6.3, 5.8)
2011-03-01 20:45:00 avg(9.1, 13.5, 8.6)
2011-03-01 21:30:00 avg(6.7, 6.2, 5.1, 3.0, 3.6)
2011-03-01 22:15:00 avg(4.3)

Gracias por tu interés.

Un saludo,

J.Kastle

[marranxo]

Al final encontré mi propia respuesta:


select avg(value) as value, TIMESTAMP WITHOUT TIME ZONE 'epoch' + date_part('epoch', time)::bigint/K0*K0 * INTERVAL '1 second' as time
from events
where ...
group by date_part('epoch', time)::bigint/K0*K0
order by date_part('epoch', time)::bigint/K0*K0 asc

Al extraer la "época" la fecha se convierte en el número de segundos pasados desde ¿1 de enero de 1970 creo? Indistintamente, al aplicar una división sobre el entero nos comemos la parte de la división conocida como resto y al volver a multiplicar con el mismo valor volvemos al valor inicial sin este resto. La solución consiste en asignar como K0 el intervalo en segundos, así un K0=300 nos agruparía la consulta a apartados de 5 minutos empezando en HH:00.

Un saludo,

J.Kastle