Aggregating postgresql rows by date

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 d开发者_高级运维ays 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.

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.

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