PostgreSQL组按中午到中午24小时的顺序进行 [英] PostgreSQL group by 24 hour period noon to noon

问题描述

使用PostgreSQL，我需要能够在24小时内（从中午到中午）汇总数据（如标题所示）。在几周的时间内每5分钟收集一次数据，我希望对从第一天中午12:00至第二天中午12:00直到数据结束的24小时内的读数进行平均。 p>

对于更标准的午夜到午夜分组，我可以使用下面的示例，该示例工作得很好，但是我看不出如何使它适应中午。我需要的时间跨度。

 按date_trunc（'day'，< DateCol>）
  

PostgreSQL对我来说相对较新，如果我要在SQL Server中这样做，我会使用：

  dateadd（hour，（datepart（hour，< DateCol>）/ 12）* 12，dateadd（day，0，datediff（day，0） ，< DateCol>）））
  

据我从阅读 http://www.postgresql.org 没有与添加日期。有什么方法可以执行PostgreSQL中所需的操作？

解决方案

尝试按日期-12小时按天分组：

 按date_trunc（'day'，< DateCol>-INTERVAL '12 hours'）分组
  

Using PostgreSQL I need to be able to aggregate data, as the title suggests, in 24 hour periods but from noon to noon. The data is collected every 5 minutes over a period of several weeks and I want to average the readings over 24 hour periods from 12:00 noon of first day to 12:00 noon of the next day until the end of the data.

For a more standard midnight to midnight grouping I can use the example below, which works just fine, but I can't see work out how to adapt this to the noon-noon time-span I need.

group by date_trunc('day', <DateCol>)

PostgreSQL is relatively new to me, and if I was going to do this in SQL Server I'd use:

dateadd(hour, (datepart(hour, <DateCol>)/12)*12, dateadd(day, 0, datediff(day, 0, <DateCol>)))

As far as I can tell from reading the documentation at http://www.postgresql.org there is no equivalent to date add. Is there a way to do what is required within PostgreSQL?

解决方案

Try grouping by day on date - 12hours:

group by date_trunc('day', <DateCol> - INTERVAL '12 hours') 

这篇关于PostgreSQL组按中午到中午24小时的顺序进行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！