X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/blobdiff_plain/4e2a6e018609488515cc81942a46254e33f06a7c..69dcdf06c711cabe6f4688f046c406131d475271:/web/seepark_web.py?ds=sidebyside

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 42bbf30..59e0c8b 100644
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -59,19 +59,6 @@ class OpenWeatherMap(db.Model):
     __tablename__ = 'openweathermap'
 
 
-def select_sensordata(sensor_id, sensor_type, begin, end):
-    query = Sensors.query
-    if sensor_id is not None:
-        query = query.filter(Sensors.sensor_id == sensor_id)
-    if sensor_type is not None:
-        query = query.filter(Sensors.value_type == sensor_type)
-    if begin is not None:
-        query = query.filter(Sensors.timestamp >= begin)
-    if end is not None:
-        query = query.filter(Sensors.timestamp <= end)
-    return query.all()
-
-
 def calc_grouping_resolution(begin, end):
     """How many data points should be between the timestamps begin and end?"""
     # copied from munin/master/_bin/munin-cgi-graph.in
@@ -94,6 +81,19 @@ def calc_grouping_resolution(begin, end):
     return resolution
 
 
+def select_sensordata(sensor_id, sensor_type, begin, end):
+    query = Sensors.query
+    if sensor_id is not None:
+        query = query.filter(Sensors.sensor_id == sensor_id)
+    if sensor_type is not None:
+        query = query.filter(Sensors.value_type == sensor_type)
+    if begin is not None:
+        query = query.filter(Sensors.timestamp >= begin)
+    if end is not None:
+        query = query.filter(Sensors.timestamp <= end)
+    return query.all()
+
+
 def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
     # determine resolution (interval in seconds for data points)
     resolution = calc_grouping_resolution(begin, end)
@@ -101,7 +101,7 @@ def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
     # Let the database do the grouping. Example in SQL (MySQL):
     # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
     query = db.session.query(func.to_seconds(Sensors.timestamp).op('div')(resolution).label('g'),
-            func.min(Sensors.timestamp).label('timestamp'),
+            func.from_unixtime(func.avg(func.unix_timestamp(Sensors.timestamp))).label('timestamp'),
             func.avg(Sensors.value).label('value'),
             Sensors.sensor_id, Sensors.value_type, Sensors.sensor_name)
     if sensor_id is not None:
@@ -130,12 +130,12 @@ def select_openweatherdata_grouped(cityid, begin, end):
     # Let the database do the grouping. Example in SQL (MySQL):
     # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
     query = db.session.query(func.to_seconds(OpenWeatherMap.datetime).op('div')(resolution).label('g'),
-            func.min(OpenWeatherMap.datetime).label('datetime'),
+            func.from_unixtime(func.avg(func.unix_timestamp(OpenWeatherMap.datetime))).label('datetime'),
             func.avg(OpenWeatherMap.temp).label('temp'),
             OpenWeatherMap.cityid)
     OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
-    query = query.filter(Sensors.timestamp >= begin)
-    query = query.filter(Sensors.timestamp <= end)
+    query = query.filter(OpenWeatherMap.datetime >= begin)
+    query = query.filter(OpenWeatherMap.datetime <= end)
     query = query.group_by('g', OpenWeatherMap.cityid)
     return query.all()