Aggregating postgresql rows by date

Question

I have records with values like: date, hour, value. Aggregating whole day is easy, i just group by date. My problem is that i need to aggregate records from whole days with specific start/end time. In example when start time is 9 a.m. then i have records: - from monday 9 a.m. to tuesday 8 a.m (grouped in one record) - from tuesday 9 a.m. to wednesday 8 a.m. (grouped in one record) and so on. Thanks, for any ideas.

MatBailie · Accepted Answer · 2011-04-07 11:09:14Z

2

I'm going to assume your hour field is an INT, but regardless you should be able to adapt this...

GROUP BY
  CASE WHEN hour >= 9 THEN date ELSE date - 1 END

This essentially treats any hour before 9am as being part of the previous day.

answered Apr 7, 2011 at 11:09

MatBailie

87.6k19 gold badges113 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Krelin - hacker · Accepted Answer · 2011-04-07 11:08:47Z

0

GROUP BY date_trunc('day',date+(hour-9)*interval '1h') or something like that?

answered Apr 7, 2011 at 11:08

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Collectives™ on Stack Overflow

Aggregating postgresql rows by date

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related