How to average hourly values over multiple days with SQL-CodePudding

I have a SQL table (postgreSQL/TimescaleDB) with hourly values, eg:

Timestamp               Value
...
2021-02-17 13:00:00     2
2021-02-17 14:00:00     4
...
2021-02-18 13:00:00     3
2021-02-18 14:00:00     3
...

I want to get the average values for each hour mapped to today's date in a specific timespan, so something like that:

select avg(value)
from table
where Timestamp between '2021-02-10' and '2021-02-20'
group by *hourpart of timestamp*

result today (2021-10-08) should be:

...
Timestamp               Value
2021-10-08 13:00:00     2.5
2021-10-08 14:00:00     3.5
...

If I do the same select tomorrow (2021-10-09) result should change to:

...
Timestamp               Value
2021-10-09 13:00:00     2.5
2021-10-09 14:00:00     3.5
...

CodePudding user response：

You have to write your query like this:

select avg(value) from table where Timestamp between '2021-02-10' and '2021-02-20' group by substring(TimeStamp,1,10), substring(TimeStamp,11,9)

CodePudding user response：

I resolved the problem by myself: Solution:

SELECT EXTRACT(HOUR FROM table."Timestamp") as hour,
avg(table."Value") as average
from table
where Timestamp between '2021-02-10' and '2021-02-20'
group by hour
order by hour;