Postgres按时间戳分组到6小时时段 [英] Postgres group by timestamp into 6 hourly buckets

问题描述

我有以下简单表格：

ID      TIMESTAMP               VALUE
4   2011-05-27 15:50:04 1253
5   2011-05-27 15:55:02 1304
6   2011-05-27 16:00:02 1322
7   2011-05-27 16:05:01 1364

我想平均 VALUES ，以及每天 TIMESTAMP 的 GROUP 放入6小时时段。例如00:00至06：00、06：00至12：00、12：00至18:00及18:00至00:00。

I would like to average the VALUES, and GROUP each TIMESTAMP day into 6 hourly buckets. e.g 00:00 to 06:00, 06:00 to 12:00, 12:00 to 18:00 & 18:00 to 00:00.

我能够按年，月，日&小时，请使用以下查询：

I am able to group by year, month, day & hour using the following query:

select avg(VALUE),
  EXTRACT(year from TIMESTAMP) AS year,
  EXTRACT(month from TIMESTAMP) AS month,
  EXTRACT(day from TIMESTAMP) as day
    from TABLE
      group by year,month,day

但是我无法将每天分为上述4个时段，非常欢迎任何帮助。

But I am unable to group each day into 4 periods as defined above, any help is most welcome.

推荐答案

我认为将（商号的小时数/ 6）的商的整数值分组应该会有所帮助。试试看，看看是否有帮助。
您的group by应该是

I think grouping the integer value of the quotient of the (Hour of your timestamp / 6) should help. Try it and see if it helps. Your group by should be something like

group by year, month, day, trunc(EXTRACT(hour from TIMESTAMP) / 6)

其背后的逻辑是将日期的小时部分除以6 ，则int值只能是

The logic behind this is that when the hour part of the date is divided by 6, the int values can only be

    0 - 0:00 - 5:59:59
    1 - 6:00 - 11:59:59
    2 - 12:00 - 17:59:59
    3 - 18:00 - 23:59:59

使用此分组可以将您的数据每天分为4组，这是您所需要的。

Grouping using this should put your data into 4 groups per day, which is what you need.

这篇关于Postgres按时间戳分组到6小时时段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！