All sensors report "NULL" now....
Closed this issue · 5 comments
`| 274063 | 1 | 1 | 2017-12-10 08:20:00 | NULL | NULL | NULL | 0 |
| 274064 | 1 | 2 | 2017-12-10 08:20:00 | NULL | NULL | NULL | 0 |
| 274065 | 1 | 3 | 2017-12-10 08:20:00 | NULL | NULL | NULL | 0 |
| 274066 | 1 | 4 | 2017-12-10 08:20:00 | NULL | NULL | NULL | 0 |
| 274067 | 1 | 5 | 2017-12-10 08:25:00 | NULL | NULL | NULL | 0 |
| 274068 | 1 | 1 | 2017-12-10 08:25:00 | NULL | NULL | NULL | 0 |
| 274069 | 1 | 2 | 2017-12-10 08:25:00 | NULL | NULL | NULL | 0 |
| 274070 | 1 | 3 | 2017-12-10 08:25:00 | NULL | NULL | NULL | 0 |
| 274071 | 1 | 4 | 2017-12-10 08:25:00 | NULL | NULL | NULL | 0 |
| 274072 | 1 | 5 | 2017-12-10 08:30:00 | NULL | NULL | NULL | 0 |
| 274073 | 1 | 1 | 2017-12-10 08:30:00 | NULL | NULL | NULL | 0 |
| 274074 | 1 | 2 | 2017-12-10 08:30:00 | NULL | NULL | NULL | 0 |
| 274075 | 1 | 3 | 2017-12-10 08:30:00 | NULL | NULL | NULL | 0 |
| 274076 | 1 | 4 | 2017-12-10 08:30:00 | NULL | NULL | NULL | 0 |
| 274077 | 1 | 5 | 2017-12-10 08:35:00 | NULL | NULL | NULL | 0 |
| 274078 | 1 | 1 | 2017-12-10 08:35:00 | NULL | NULL | NULL | 0 |
| 274079 | 1 | 2 | 2017-12-10 08:35:00 | NULL | NULL | NULL | 0 |
| 274080 | 1 | 3 | 2017-12-10 08:35:00 | NULL | NULL | NULL | 0 |
| 274081 | 1 | 4 | 2017-12-10 08:35:00 | NULL | NULL | NULL | 0 |
| 274082 | 1 | 5 | 2017-12-10 08:40:00 | NULL | NULL | NULL | 0 |
| 274083 | 1 | 1 | 2017-12-10 08:40:00 | NULL | NULL | NULL | 0 |
| 274084 | 1 | 2 | 2017-12-10 08:40:00 | NULL | NULL | NULL | 0 |
| 274085 | 1 | 3 | 2017-12-10 08:40:00 | NULL | NULL | NULL | 0 |
| 274086 | 1 | 4 | 2017-12-10 08:40:00 | NULL | NULL | NULL | 0 |
| 274087 | 1 | 5 | 2017-12-10 08:45:00 | NULL | NULL | NULL | 0 |
| 274088 | 1 | 1 | 2017-12-10 08:45:00 | NULL | NULL | NULL | 0 |
| 274089 | 1 | 2 | 2017-12-10 08:45:00 | NULL | NULL | NULL | 0 |
| 274090 | 1 | 3 | 2017-12-10 08:45:00 | NULL | NULL | NULL | 0 |
| 274091 | 1 | 4 | 2017-12-10 08:45:00 | NULL | NULL | NULL | 0 |
| 274092 | 1 | 5 | 2017-12-10 08:50:00 | NULL | NULL | NULL | 0 |
| 274093 | 1 | 1 | 2017-12-10 08:50:00 | NULL | NULL | NULL | 0 |
| 274094 | 1 | 2 | 2017-12-10 08:50:00 | NULL | NULL | NULL | 0 |
| 274095 | 1 | 3 | 2017-12-10 08:50:00 | NULL | NULL | NULL | 0 |
| 274096 | 1 | 4 | 2017-12-10 08:50:00 | NULL | NULL | NULL | 0 |
| 274097 | 1 | 5 | 2017-12-10 08:55:00 | NULL | NULL | NULL | 0 |
| 274098 | 1 | 1 | 2017-12-10 08:55:00 | NULL | NULL | NULL | 0 |
| 274099 | 1 | 2 | 2017-12-10 08:55:00 | NULL | NULL | NULL | 0 |
| 274100 | 1 | 3 | 2017-12-10 08:55:00 | NULL | NULL | NULL | 0 |
| 274101 | 1 | 4 | 2017-12-10 08:55:00 | NULL | NULL | NULL | 0 |
| 274102 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274103 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274104 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274105 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274106 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
``
dropped the database completely and started fresh with a fresh pull this AM... all sensors now show null.
However, historical data is still there though, so it's pulling it.... I'm running my cron ever half hour.
Okay, apparently it just doesn't populate to the API for an hour or so... maybe some cleanup code to nuke the NULL NULL NULL's daily if there is some more timestamps with actual data?
oh... and now it seems to just be pulling it over and over again.... I've been refreshing a query once every so often.....
Query:
mysql> select * from runtime_report_sensor where timestamp LIKE '2017-12-10 09:00:00';
First time...
| runtime_report_sensor_id | thermostat_id | sensor_id | timestamp | temperature | humidity | occupancy | deleted |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
| 274102 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274103 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274104 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274105 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274106 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274592 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274593 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274594 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274595 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274596 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275082 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275083 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275084 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275085 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275086 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
15 rows in set (0.23 sec)
Second
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
| runtime_report_sensor_id | thermostat_id | sensor_id | timestamp | temperature | humidity | occupancy | deleted |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
| 274102 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274103 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274104 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274105 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274106 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274592 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274593 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274594 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274595 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274596 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275082 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275083 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275084 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275085 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275086 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275572 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275573 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275574 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275575 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275576 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
20 rows in set (0.00 sec)
Third...
`
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
| runtime_report_sensor_id | thermostat_id | sensor_id | timestamp | temperature | humidity | occupancy | deleted |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
| 274102 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274103 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274104 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274105 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274106 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274592 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274593 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274594 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274595 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 274596 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275082 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275083 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275084 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275085 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275086 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275572 | 1 | 5 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275573 | 1 | 1 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275574 | 1 | 2 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275575 | 1 | 3 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 275576 | 1 | 4 | 2017-12-10 09:00:00 | NULL | NULL | NULL | 0 |
| 276062 | 1 | 5 | 2017-12-10 09:00:00 | 65.2 | 35 | 0 | 0 |
| 276063 | 1 | 1 | 2017-12-10 09:00:00 | 65.9 | NULL | 0 | 0 |
| 276064 | 1 | 2 | 2017-12-10 09:00:00 | 62.6 | NULL | 0 | 0 |
| 276065 | 1 | 3 | 2017-12-10 09:00:00 | 67.2 | NULL | 0 | 0 |
| 276066 | 1 | 4 | 2017-12-10 09:00:00 | 62.1 | NULL | 0 | 0 |
| 276552 | 1 | 5 | 2017-12-10 09:00:00 | 65.2 | 35 | 0 | 0 |
| 276553 | 1 | 1 | 2017-12-10 09:00:00 | 65.9 | NULL | 0 | 0 |
| 276554 | 1 | 2 | 2017-12-10 09:00:00 | 62.6 | NULL | 0 | 0 |
| 276555 | 1 | 3 | 2017-12-10 09:00:00 | 67.2 | NULL | 0 | 0 |
| 276556 | 1 | 4 | 2017-12-10 09:00:00 | 62.1 | NULL | 0 | 0 |
+--------------------------+---------------+-----------+---------------------+-------------+----------+-----------+---------+
30 rows in set (0.25 sec)
Hi @starblazr! Sorry about the issue...I forgot to include a unique key constraint in the version of the code I pushed. I'll push a fix soon, but for now you can do the following:
truncate runtime_report_thermostat;
truncate runtime_report_sensor;
alter table `runtime_report_sensor` add unique `sensor_id_timestamp` (`sensor_id`, `timestamp`), drop index `sensor_id`;
This will delete all contents from the runtime report tables and add the correct unique key to the database. The next time your cron job runs it will get all history again.
As for the null values you're seeing: that's normal (edit: not the duplicates; you should only have one unique row per sensor per timestamp). The ecobee API returns data for all time up to the present, but your ecobee at home only transmits every 15 minutes or so. The end result is that you get several rows in the runtime report tables with a bunch of nulls. Those rows will be updated as the data arrives. I opted to include them in the database even though they are basically worthless because sqlbee is designed as a direct sync of what ecobee without any interpretation.