From: gregor herrmann <gregor@toastfreeware.priv.at>
Date: Thu, 30 Aug 2018 16:45:39 +0000 (+0200)
Subject: for datetime intervals, don't return lowest value (min()) but avg()
X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/commitdiff_plain/c078b6b5f231a2e7da614bcc6efcd0bb03c2fbeb?ds=sidebyside

for datetime intervals, don't return lowest value (min()) but avg()

needs a conversion to/from unix timestamp

cf. https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
and https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
---

diff --git a/web/seepark_web.py b/web/seepark_web.py
index 0870a12..bd22d31 100644
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -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,7 +130,7 @@ 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)