iot_analytics_query.sql

SELECT to_iso8601(date_parse(wirelessmetadata.lorawan."timestamp", 
          '%Y-%m-%dT%H:%i:%SZ') AT TIME ZONE 'UTC') AS ts_utc,
       to_iso8601(date_parse(wirelessmetadata.lorawan."timestamp", 
          '%Y-%m-%dT%H:%i:%SZ') AT TIME ZONE 'America/New_York') AS ts_local,
       wirelessdeviceid,
       payloaddata.battery,
       round(((payloaddata.boardtemp * 1.8) + 32), 2) AS board_temp,
       payloaddata.rh,
       round(((payloaddata.envtemp * 1.8) + 32), 2) AS env_temp,
       payloaddata.voc,
       payloaddata.iaq,
       payloaddata.eco2,
       wirelessmetadata.lorawan.gateways[1].rssi AS rssi,
       wirelessmetadata.lorawan.gateways[1].snr AS snr
FROM lorawan_iot_core_analytics_data_store
WHERE payloaddecodername = 'tbhv110_915'
  AND payloaddata IS NOT NULL
ORDER BY ts_local