Усекать временную метку на произвольные интервалы

Я хочу построить my_values с некоторым меньшим разрешением по оси времени, чем в базе данных. Я использую что-то вроде следующего SQL для получения средних значений за промежуток времени (например, час):

SELECT DATE_TRUNC('hour', my_timestamps) AS my_time_lowres
 , AVG(my_values) 
FROM my_table 
GROUP BY my_time_lowres

Используя date_trunc(), можно уменьшить разрешение временной метки до определенной степени (согласно docs):

select date_trunc('hour', timestamp '2001-02-16 20:38:40') 
-- the output is: 2001-02-16 20:00:00

Таким образом, я могу сделать это для следующих размеров интервалов (и некоторых более крупных/меньших размеров):

...
second
minute
hour
day
week
...

Есть ли способ достичь этого и для других временных интервалов, например, 3 часа, 6 часов?

2 ответа

Рассмотрим эту демонстрацию, чтобы принести временные метки с разрешением до 15 минут и суммировать получающиеся обманы:

WITH tbl(id, ts) AS ( VALUES
 (1::int, '2012-10-04 00:00:00'::timestamp)
 ,(2, '2012-10-04 18:23:01')
 ,(3, '2012-10-04 18:30:00')
 ,(4, '2012-10-04 18:52:33')
 ,(5, '2012-10-04 18:55:01')
 ,(6, '2012-10-04 18:59:59')
 ,(7, '2012-10-05 11:01:01')
 )
SELECT to_timestamp((extract(epoch FROM ts)::bigint / 900)*900)::timestamp
 AS lower_bound
 , to_timestamp(avg(extract(epoch FROM ts)))::timestamp AS avg_ts
 , count(*) AS ct
FROM tbl
GROUP BY 1
ORDER BY 1;

Результат:

lower_bound | avg_ts | ct
---------------------+---------------------+----
 2012-10-04 00:00:00 | 2012-10-04 00:00:00 | 1
 2012-10-04 18:15:00 | 2012-10-04 18:23:01 | 1
 2012-10-04 18:30:00 | 2012-10-04 18:30:00 | 1
 2012-10-04 18:45:00 | 2012-10-04 18:55:51 | 3
 2012-10-05 11:00:00 | 2012-10-05 11:01:01 | 1

Трюк заключается в том, чтобы извлечь эпоху unix, как @Michael, уже опубликованный. Целочисленное деление объединяет их в ведра выбранного разрешения, поскольку дробные цифры усекаются.

Я деляю на 900, потому что 15 минут = 900 секунд.

Умножьте на тот же номер, чтобы получить полученный lower_bound. Преобразуйте эпоху unix обратно в метку времени с to_timestamp().

Это отлично работает для интервалов, которые могут быть представлены без дробных цифр в десятичной системе. Для еще большей универсальности используйте часто забытую функцию width_bucket(), как я демонстрирую в этом недавний, тесно связанный ответ. Больше объяснений, ссылок и демонстрационной версии sqlfiddle.


Вы можете преобразовать число секунд с эпохи и перейти на целочисленное деление на 3 * 3600, 6 * 3600 и т.д.

licensed under cc by-sa 3.0 with attribution.