All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can use time_bucket function to make it.
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
m_tags you’ll need to also use aggregation functions or add them to the group by clause.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂