Device to Database: Trying out a few time series databases

2. Use the farm database on InfluxDB. Write queries to answer the following questions:

• When did the outside sensor break and stop sending data?

select * from /.*/ where location = 'outside' ORDER BY time DESC limit 1

• What was the lowest temperature recorded in 2018? Which sensor recorded this data?

SELECT min(value), location from temperature where time >= '2018-01-01' and time < '2018-12-31' tz('America/New_York')

3. Find the min and max temperatures by week for the root cellar for the last 3 months of 2018.

• 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 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::timestamp) AS one_week,
        min(reading), max(reading)
    FROM sensor_data
    WHERE recorded_at BETWEEN '2018-10-01' and '2018-12-31'
    AND device = 'rootcellar'
    AND measurement = 'temperature'
    GROUP BY one_week;

Explain the differences between the InfluxDB and TimescaleDB and query results.

InfluxDB and TimescaleDB has differing defaults for their weekly intervals. For example, in InfluxDB, the default start of the week is Monday (you can see the dates for the weekly results begins 9/27 rather than 10/1). This results differences in each of their reported min and max values.

Challenge: Run the same query in MongoDB using the farm database

db.sensorData.aggregate([{
$match: { "measurement": "temperature", "device": "rootcellar", "recorded": {
             $gte: new ISODate("2018-10-01T00:00:00-05:00"),
             $lte: new ISODate("2018-12-31T00:00:00-05:00")
         }
     }
 }, {
     $group: {
			_id: {
             week: {
                 $week: {
                     date: "$recorded",
                     timezone: "America/New_York"
                 }
             }
         },
         min: { $min: "$reading" },
         max: { $max: "$reading" },
     }
 }])

Write two queries that use data from your sensor data from InfluxDB.

A fun way to discover that my sensor had disconnected from my WiFi network:

select * from light where device = 'device_rk' order by time DESC limit 10

I also checked out the min and max temperatures from my sensor. The readings seemed a little high to me, though qualitatively, the living room is the warmest part of my apartment (and that is where my sensor is presently located).