top of page
  • Writer's pictureAtchareeya Jattuporn

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

link to SQLite file


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

4 views

Recent Posts

See All
bottom of page