2019-03-04

    D2D: Time Series Databases

    This is week 3 assignment for Device to Database


    1. When did the outside sensor break and stop sending data?

    select * 
      from /.*/ 
      where location = 'outside' 
      order by time DESC 
      limit 1
    
    influx-1

    2. 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 < '2019-01-01'
    
    influx-2

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

    • InfluxDB
    select min(value), max(value) 
      from temperature 
      where 
        time > '2018-10-07T00:00:00Z' 
        and time < '2018-12-31' 
        and location = 'rootcellar' 
      group by time(1w, 4d)
    
    influx-3
    group-time-offset-issue
    • Timescale
    select 
      time_bucket('1 week', recorded_at::timestamp) as week, 
      min(reading) as min_read,
      max(reading) as max_read
    from sensor_data
    where recorded_at between '2018-10-01' and '2018-12-31'
    and device = 'rootcellar' 
      and measurement = 'temperature'
    group by week;
    
    timescale-3