week 5 Node Server
Code Part
Graphing Data from the Database
Temperature from device 14
Messages per day from all device
Messages per device
Processing MQTT data with Node.js
SQL Query with InfluxDB and TimescaleDB
InfluxDB
When did the outside sensor break and stop sending data?
select last(value) from /.*/ where location = 'outside' group by location
I got the solution from this post in StackOverflow
What was the lowest temperature recorded in 2018? Which sensor recorded this data?
select min(value) from temperature where time >= '2018-01-01' and time < '2018-12-31' group by location
The lowest temperature in 2018 is -7.42 which came from the outside sensor.
InfluxDB vs TimescaleDB
Use InfluxDB and the farm database. Hint: use group by time(interval)
select min(value), max(value) from temperature where time >= '2018-10-01' and time <= '2018-12-31' and location = 'rootcellar' group by location, time(1w) tz('America/New_York')
Use TimescaleDB and the tsfarm database. Hint: use the time_bucket function
SELECT time_bucket('1 week', recorded_at) AS one_week, device, min(reading), max(reading)
FROM sensor_data
WHERE recorded_at BETWEEN '2018-10-1' and '2018-12-31'
AND device = 'rootcellar'
AND measurement = 'temperature'
GROUP BY one_week, device
ORDER BY one_week;
Explain the differences between the InfluxDB and TimescaleDB and query results.
I noticed that even I specify the time week in both databases. They had distinct ways to categorized the time. For example, in InfluxDB, the start date is 2018/09/27 but in TimeScaleDB, the start date is 2018/10/01. As a result, the results are mostly different. Personally, I think TimeScaleDB is more accurate since the data on week 5 in the InfluxDB result is disappeared.
InfluxDB with ITP table
Select the oldest data sending from device_14
select first(value) from /.*/ where device = 'device_14' group by device
Select the lowest, highest and average data sending from device_14
select min(value), max(value), mean(value) from /.*/ where device = 'device_14' group by device
