How to get the average in different intervals in posgresql?

Question

PostgreSQL

Good afternoon.

There is the following table

id | created             | value
1  | 2021-01-01 10:00:00 | 1
2  | 2021-01-01 10:01:00 | 10
3  | 2021-01-01 10:02:00 | 33
4  | 2021-01-01 10:03:00 | 3 
....
N  | 2021-09-01 13:13:00 | 40

where for several years the number per minute is indicated. (Data is skipped here and there for various reasons).

I want to get an average value in different intervals - from 10 minutes to months and years.
So far I have written the following function:

select to_created(CONCAT( 
  extract('year' from e."created"), '-',
  extract('month' from e."created"), '-', 
  extract('day' from e."created"), '-', ' ',
  extract('hour' from e."created"), ':',
  FLOOR(extract('minute' from e."created") / 3) * 3, ':',
  extract('second' from e."created")
 ), 'YYYY-MM-DD hh24:mi:ss') as tmp, AVG(e.value) avg_v from table e group by tmp order by tmp asc;

Here for example I receive on intervals in 3 minutes. Is there a way to avoid such a cumbersome construction? And to make request optimized? Search results did not give clear answers.

Answer 1

At intervals of 1 year/month/day/hour/minute:

select date_trunc('hour', e.created), avg(e.value) from table e group by 1 order by 1

(instead of hour - year/month/day ...)
But in general, your task is not quite clearly defined: does the interval start at the beginning of the corresponding calendar period or is it optional? Those. if we group by days, the days go from 0:00:00 to 23:59:59 or, say, take an arbitrary starting moment (let's say 2019-08-03 14:25:44) and count from it (i.e. days 14:25:44 to 14:25:43 the next day)?
This is even more significant for the case of periods like 7 hours, or 23 minutes. For example, counting from the beginning of a day, periods of 7 hours can go like this:

[00:00-07:00)
[07:00-14:00)
[14:00-21:00)
[21:00-00:00) - следующий день
[00:00-07:00) - следующий день

or like this:

[00:00-07:00)
[07:00-14:00)
[14:00-21:00)
[21:00-03:00) - следующий день
[03:00-10:00) - следующий день
...

for the first case something like this:

select date_trunc('day', e.created) + (trunc(extract('hour' from e.created) / 7) * 7 || ' hour')::interval, avg(e.value) from table e group by 1 order by 1

not much simpler than yours, as you can see