From ba14c390b1f377bed6b46a83073bc35e0438b88e Mon Sep 17 00:00:00 2001 From: yvesf Date: Sun, 17 Jul 2011 15:22:43 +0200 Subject: cache auf int->real cast --- populate_cache.sql | 13 +++++++------ 1 file changed, 7 insertions(+), 6 deletions(-) (limited to 'populate_cache.sql') 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; -- cgit v1.2.1