diff options
| -rw-r--r-- | populate_cache.sql | 13 | ||||
| -rw-r--r-- | refresh_cache.sql | 1 |
2 files changed, 8 insertions, 6 deletions
diff --git a/populate_cache.sql b/populate_cache.sql index 0af4b36..0c30a18 100644 --- a/populate_cache.sql +++ b/populate_cache.sql @@ -1,18 +1,19 @@ drop table value_cache cascade; CREATE TABLE value_cache ( -value_int integer, +value_real integer, timestamp timestamp, sensor_id integer ); create or replace view vi_value_cache as - SELECT timestamp, sensor_id, value_int, 'CACHE' + SELECT timestamp, sensor_id, value_real, 'CACHE' FROM value_cache UNION - SELECT date_trunc('hour', timestamp), sensor_id, avg(value_int), 'LIVE' + SELECT date_trunc('hour', timestamp), sensor_id, COALESCE(avg(value_int),avg(value_real)), 'LIVE' FROM value - WHERE timestamp > coalesce( (select max(timestamp) from value_cache), now() - interval '2 days') + WHERE date_trunc('hour', timestamp) > coalesce((select max(timestamp) from value_cache), + now() - interval '2 days') GROUP BY date_trunc('hour', timestamp), sensor_id; ; @@ -34,9 +35,9 @@ BEGIN delete from value_cache where timestamp >= last_update; insert into value_cache - select avg(value_int), date_trunc('hour', timestamp), sensor_id + select COALESCE(avg(value_real),avg(value_int)), date_trunc('hour', timestamp), sensor_id from value - where date_trunc('hour', timestamp) >= last_update + where date_trunc('hour', timestamp) > last_update group by date_trunc('hour', timestamp), sensor_id; RETURN last_update; diff --git a/refresh_cache.sql b/refresh_cache.sql new file mode 100644 index 0000000..8734348 --- /dev/null +++ b/refresh_cache.sql @@ -0,0 +1 @@ +select value_cache_aktualisieren(); |