These in particular can be a real drag on performance.
Much more efficient to let InfluxDB do that for you.
Here are some examples:
# Limit the domotica db to 24hrs data by default (.8*60*60*24=69,120 69,120*11*9=6,842,880
CREATE RETENTION POLICY twenty_four_hours ON domotica DURATION 24h REPLICATION 1 DEFAULT
# Create 2nd Limit for the env_daily table for 5yrs (24*365*5=43,800 ). 5y = 5*365=1,825 d
CREATE RETENTION POLICY one_week ON domotica DURATION 1825d REPLICATION 1
# Aggregate details to 60minute data
CREATE CONTINUOUS QUERY cq_60min_environment ON test BEGIN
SELECT mean(value) AS value, max(value), min(value), max(value) - min(value) AS range
INTO test.five_years.environment_hourly
FROM test.one_week.environment
GROUP BY location, type, time(1h)
END
CREATE CONTINUOUS QUERY cq_60min ON domotica BEGIN SELECT mean("value") as value,max("value"),min("value"),max("value")-min("value") as range INTO domotica.one_week.env_daily FROM environment GROUP BY location,type,time(60m) END
SELECT mean("value") as value,max("value"),min("value"),max("value")-min("value") as range INTO domotica.one_week.env_daily FROM environment WHERE time >= 0 GROUP BY location,type,time(60m)
# SELECT mean("value") as value,max("value"),min("value"),max("value")-min("value") as range FROM environment WHERE time >= now() - 1d GROUP BY location,type,time(60m)
# SELECT * FROM environment WHERE location = 'HOME/IN/00/HAL' AND type = 'DewPoint' AND time >= '2016-04-28 15:00:00' AND time <= '2016-04-28 16:00:00'
DELETE FROM environment WHERE time < '2016-04-26'
select count("value") from domotica.'default'.environment
I think there is a post in the forum somewhere from me where I give a much better and more up-to-date example.
Here we go, this is better:
Need more detailed information on influxdb - General - Node-RED Forum (nodered.org)