From: Philipp Spitzer <philipp@spitzer.priv.at>
Date: Wed, 18 Jul 2018 21:26:47 +0000 (+0200)
Subject: Prepare consolidation of data.
X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/commitdiff_plain/90b27bb7bd4231c93556537faa5ee6b9a6329291

Prepare consolidation of data.
---

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 18bedc9..c19e9b9 100644
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -71,29 +71,39 @@ def select_sensordata(sensor_id, sensor_type, begin, end, mode):
         query = query.filter(Sensors.timestamp >= begin)
     if end is not None:
         query = query.filter(Sensors.timestamp <= end)
-    if mode == 'consolidated':
-        if begin is None or end is None:
-            pass
+    if mode == 'consolidated' and begin is None and end is None:
+        # copied from munin/master/_bin/munin-cgi-graph.in
+        # interval in seconds for data points
+        resolutions = dict(
+            day   =   300,
+            week  =  1800,
+            month =  7200,
+            year  = 86400,
+        )
+        duration = (end - begin).total_seconds()
+        day = 60 * 60 * 24
+        if duration < day:
+            resolution = resolutions['day']
+        elif duration < 7 * day:
+            resolution = resolutions['week']
+        elif duration < 31 * day:
+            resolution = resolutions['month']
         else:
-            # copied from munin/master/_bin/munin-cgi-graph.in
-            resolutions = dict(
-                day   =   300,
-                week  =  1800,
-                month =  7200,
-                year  = 86400,
-            )
-            duration = (end - begin).total_seconds()
-            day = 60 * 60 * 24
-            if duration < day:
-                resolution = resolutions['day']
-            elif duration < 7 * day:
-                resolution = resolutions['week']
-            elif duration < 31 * day:
-                resolution = resolutions['month']
-            else:
-                resolution = resolutions['year']
-            # TODO: filter out samples from 'result'
-            # like loop over results and skip if timestamp(n+1)-timestamp(n)<resolution
+            resolution = resolutions['year']
+        # TODO: filter out samples from 'result'
+        # something like 
+        # select mean(temperature) from sensors where ... group by mod(timestamp, resolution)
+        # func.avg(...)
+        #
+        # from https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range
+        # SELECT
+        #    timestamp,  -- not sure about that
+        #        name,
+        #            count(b.name)
+        #            FROM time a, id 
+        #            WHERE …
+        #            GROUP BY 
+        #            UNIX_TIMESTAMP(timestamp) DIV 300, name
     return query.all()