cache auf int->real cast

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();