2019-02-24

    D2D: MKR 1010, MQTT and PostgreSQL

    👉 Source code is here


    psql practice

    1. When did the outside sensor break and stop sending data?
    select *
      from sensor_data
      where device = 'outside' order by recorded_at desc
      limit 1
    ;
    
    when did outside sensor break

    1. Show the min and max temperature in the root cellar by year
    select min(reading) as min_read,
      max(reading) as max_read,
      date_part('year', recorded_at) as year
      from sensor_data
      where device = 'rootcellar'
      and measurement = 'temperature'
      group by year
    ;
    
    min and max temp in root cellar by year

    1. What was the lowest temperature recorded 2018?
    select 
      min(reading) as temperature,
      date_part('year', recorded_at) as year
      from sensor_data
      where date_part('year', recorded_at) = '2018'
      and measurement = 'temperature'
      group by year
    ;
    
    lowest temperature recorded 2018

    1. Which sensor recorded the lowest temperature 2018 and when?
    select 
      min(reading) as min_read,
      device,
      recorded_at
      from sensor_data
      where measurement = 'temperature'
      and date_part('year', recorded_at) = 2018
      and reading = (
      select min(reading)
        from sensor_data
        where measurement = 'temperature'
        and date_part('year', recorded_at) = 2018
      ) 
      group by device, recorded_at
    ;
    
    sensor recorded the lowest temperature 2018 and when

    1. Write two queries that use data from your sensor
    select * from mqtt_message where topic like '%ms%'*
    
    select payload from mqtt_message where topic like '%ms/temperature'*
    
    own device reading