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.

  "time" AS "time",
  metric AS metric,
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND 
  m_tags IS NOT NULL
ORDER BY time desc limit 1000
  • 1
    Hi, and welcome to the forum! A word of advice - don't use SQL keywords (i.e. time & hour) as column or field names... it's potentially confusing and makes debugging difficult...
    – Vérace
    Commented May 10, 2021 at 19:14

1 Answer 1


You can use time_bucket function to make it.


  time_bucket('1 hour', "time") AS hour,
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.