1 files changed, 29 insertions, 2 deletions
diff --git a/src/DillerDao.js b/src/DillerDao.js
index ed6fcf0..e0c69d3 100644
--- a/src/DillerDao.js
+++ b/src/DillerDao.js
@@ -28,7 +28,32 @@ function DillerDao(client) {
   }
 
   function insertValue(propertyId, value) {
-    return client.none('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2)', [propertyId, value]);
+    return client.one('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2) RETURNING timestamp', [propertyId, value]);
+  }
+
+  function updateHourAggregatesForProperty(propertyId, timestamp) {
+    return client.none('DELETE FROM value_by_hour WHERE property=$1 AND timestamp=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ)', [propertyId, timestamp])
+      .then(function() {
+        return client.one('INSERT INTO value_by_hour(property, timestamp, count, max, min, avg) ' +
+          'SELECT property, DATE_TRUNC(\'hour\', timestamp) AS timestamp, COUNT(value) AS count, MAX(value::NUMERIC) AS max, MIN(value::NUMERIC) AS min, AVG(value::NUMERIC) AS avg ' +
+          'FROM value WHERE property=$1 AND DATE_TRUNC(\'hour\', timestamp)=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ) ' +
+          'GROUP BY property, DATE_TRUNC(\'hour\', timestamp) ' +
+          'ORDER BY 2, 1 ' +
+          'RETURNING *;', [propertyId, timestamp]);
+      });
+  }
+
+  function updateMinuteAggregatesForProperty(propertyId, timestamp) {
+    return client.none('DELETE FROM value_by_minute WHERE property=$1 AND timestamp=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ)', [propertyId, timestamp])
+      .then(function() {
+        return client.one('INSERT INTO value_by_minute(property, timestamp, count, max, min, avg) ' +
+          'SELECT property, DATE_TRUNC(\'minute\', timestamp) AS timestamp, COUNT(value) AS count, MAX(value::NUMERIC) AS max, MIN(value::NUMERIC) AS min, AVG(value::NUMERIC) AS avg ' +
+          'FROM value ' +
+          'WHERE property=$1 AND DATE_TRUNC(\'minute\', timestamp)=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ) ' +
+          'GROUP BY property, DATE_TRUNC(\'minute\', timestamp) ' +
+          'ORDER BY 2, 1 ' +
+          'RETURNING *;', [propertyId, timestamp]);
+      });
   }
 
   return {
@@ -40,7 +65,9 @@ function DillerDao(client) {
     updatePropertyName: updatePropertyName,
     updatePropertyDescription: updatePropertyDescription,
 
-    insertValue: insertValue
+    insertValue: insertValue,
+    updateHourAggregatesForProperty: updateHourAggregatesForProperty,
+    updateMinuteAggregatesForProperty: updateMinuteAggregatesForProperty
   }
 }