postgres read query resample time series data for hourly averages 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
  &quot;time&quot; AS &quot;time&quot;,
  metric AS metric,
  value,
  kv_tags,
  m_tags
FROM building_data
WHERE kv_tags-&gt;'equip_name' = '[&quot;35201&quot;]' AND 
  m_tags IS NOT NULL
ORDER BY time desc limit 1000
You can use time_bucket function to make it.
Example:
SELECT
  time_bucket('1 hour', &quot;time&quot;) AS hour,
  metric,
  avg(value)
FROM building_data
WHERE kv_tags-&gt;'equip_name' = '[&quot;35201&quot;]' 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.