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 0870a12061715662f61501eb31b935e22a50ff5c..bd22d31a693b46bee0dc80b7aa503ac0d0bffb69 100644 (file)
--- 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'),
     # 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:
             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'),
     # 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)
             func.avg(OpenWeatherMap.temp).label('temp'),
             OpenWeatherMap.cityid)
     OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)