Aggregating database data by timestamp

Author: Mateusz Koteja

Problem context

In PostgreSQL we have entries (readings of produced and used up energy), that we want to aggregate based on their timestamp. While readings should be in 15-minute intervals (12:00, 12:15, 12:30) this is not ensured.

We should be able to aggregate readings by timestamp to hour, day, week (monday-sunday) and month periods. Also, we should support timezone offsets (if timestamp is in UTC, a day means something different in UTC time and Australian time): end user should see readings relative to their timezone.

In other project we used application logic for that: we got readings from database, and process them in the code. However the code was complex, and hard to grasp (especially the timezone part). Also it was not performant.

Whatever we use it must work with PostgreSQL as this is our database of choice.