o Splitting the value field into value_numeric and value_text.

2 files changed, 11 insertions, 10 deletions

diff --git a/src/Diller.js b/src/Diller.js
index fa2e308..e73bf46 100644
--- a/src/Diller.js
+++ b/src/Diller.js
@@ -12,7 +12,7 @@ function Diller(config, db) {
 
     return dao.insertValue(property.id, value)
       .then(function (res) {
-        log.info('typeof', typeof res.timestamp, res.timestamp, res.timestamp.getTime());
+        // ignore the result
         updateAggregates(property.id, res.timestamp);
         return res;
       });
diff --git a/src/DillerDao.js b/src/DillerDao.js
index b46e734..98b6e6e 100644
--- a/src/DillerDao.js
+++ b/src/DillerDao.js
@@ -2,7 +2,7 @@ function DillerDao(tx) {
 
   var deviceColumns = 'id, key, created_timestamp';
   var propertyColumns = 'id, device, key, created_timestamp';
-  var valueColumns = 'property, timestamp, value';
+  var valueColumns = 'property, timestamp, value_text, value_numeric';
 
   // -------------------------------------------------------------------------------------------------------------------
   // Device
@@ -58,21 +58,23 @@ function DillerDao(tx) {
 
   function valuesByPropertyId(propertyId, limit) {
     limit = limit || 10;
-    return tx.many('SELECT timestamp, value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]);
+    return tx.many('SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]);
   }
 
   function insertValue(propertyId, value) {
-    return tx.one('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2) RETURNING timestamp', [propertyId, value]);
+    var value_numeric = parseFloat(value) || undefined,
+      value_text = value_numeric ? null : value;
+
+    return tx.one('INSERT INTO value(property, timestamp, value_text, value_numeric) VALUES($1, CURRENT_TIMESTAMP, $2, $3::NUMERIC) RETURNING timestamp', [propertyId, value_text, value_numeric]);
   }
 
   function updateHourAggregatesForProperty(propertyId, timestamp) {
     return tx.none('DELETE FROM value_by_hour WHERE property=$1 AND timestamp=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ)', [propertyId, timestamp])
       .then(function() {
         return tx.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) ' +
+          'SELECT property, DATE_TRUNC(\'hour\', timestamp) AS timestamp, COUNT(value_numeric) 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) AND value_numeric IS NOT NULL '  +
           'GROUP BY property, DATE_TRUNC(\'hour\', timestamp) ' +
-          'ORDER BY 2, 1 ' +
           'RETURNING *;', [propertyId, timestamp]);
       });
   }
@@ -81,11 +83,10 @@ function DillerDao(tx) {
     return tx.none('DELETE FROM value_by_minute WHERE property=$1 AND timestamp=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ)', [propertyId, timestamp])
       .then(function() {
         return tx.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 ' +
+          'SELECT property, DATE_TRUNC(\'minute\', timestamp) AS timestamp, COUNT(value_numeric) 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) ' +
+          'WHERE property=$1 AND DATE_TRUNC(\'minute\', timestamp)=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ) AND value_numeric IS NOT NULL ' +
           'GROUP BY property, DATE_TRUNC(\'minute\', timestamp) ' +
-          'ORDER BY 2, 1 ' +
           'RETURNING *;', [propertyId, timestamp]);
       });
   }