Downsampling smart meter data with InfluxDB

This article is based on the official InfluxDB documentation on Downsampling and data retention.

I’m using a P1 (smart energy meter) database for this example.

First, change your default retention policy and create at least one additional retention policy:

CREATE RETENTION POLICY "168_hours" ON "P1_External" DURATION 168h REPLICATION 1 DEFAULT
CREATE RETENTION POLICY "2yr" ON "P1_External" DURATION 104w REPLICATION 1 

Create a test query that summarizes the data that needs to be stored in the downsampled data:

SELECT mean("current_delivery") as "current_delivery", mean("current_usage") as "current_usage", last("total_usage_gas") as "total_usage_gas", last("total_usage_t1") as "total_usage_t1", last("total_usage_t2") as "total_usage_t2", last("total_delivery_t1") as "total_delivery_t1", last("total_delivery_t2") as "total_delivery_t2" FROM energy_p1_actual GROUP BY "name", time(1h) ORDER BY time DESC LIMIT 10

Then, define a continuous query from this:

CREATE CONTINUOUS QUERY "cq_60m" on "P1_External" BEGIN SELECT mean("current_delivery") as "current_delivery", mean("current_usage") as "current_usage", last("total_usage_gas") as "total_usage_gas", last("total_usage_t1") as "total_usage_t1", last("total_usage_t2") as "total_usage_t2", last("total_delivery_t1") as "total_delivery_t1", last("total_delivery_t2") as "total_delivery_t2" INTO "2yr"."energy_p1_history" FROM energy_p1_actual GROUP BY "name", time(1h) END

As our retention policy is set to 2 hours the continuous query will run every two hours to summarize the data.