postgres read query resample time series data for hourly averages

Question

I am new to SQL trying to learn how to do read queries on time series data. Can someone give me a tip on how to resample interval time series data to hourly averages on the postgres read query?

My table is named building_data where there are a few columns named time, metric, value, kv_tags, m_tags

time is my date/time stamp column where I am trying to see if I can resample the data in the value column into hourly averages. The WHERE in the query below is to filter out for a specific device that I am interested in looking at the data. I apologize if that doesn't make sense.

For a first timer this sql query appears to work but its not incorporating some process to resample the data in hourly averages, any tips greatly appreciated.

SELECT
  "time" AS "time",
  metric AS metric,
  value,
  kv_tags,
  m_tags
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND 
  m_tags IS NOT NULL
ORDER BY time desc limit 1000

Answer 1

You can use time_bucket function to make it.

Example:

SELECT
  time_bucket('1 hour', "time") AS hour,
  metric,
  avg(value)
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND 
  m_tags IS NOT NULL
GROUP BY hour, metric
ORDER BY hour limit 1000

Note that I'm simplifying the query and if you want to include kv_tags and m_tags you'll need to also use aggregation functions or add them to the group by clause.