garystafford

"ts_utc","ts_local","wirelessdeviceid","battery","board_temp","rh","env_temp","voc","iaq","eco2","rssi","snr","sensor_location"
"2021-04-12T18:38:52.000Z","2021-04-12T14:38:52.000-04:00","3a3c05d6-a3a3-434f-c5f9-47aa04d41117","3.6","69.8","53","68.0","1","82","730","-73","9.25","Second floor office"
"2021-04-12T18:39:21.000Z","2021-04-12T14:39:21.000-04:00","1ec1fe6d-9db0-2334-dee3-48a58d821704","3.6","68.0","57","66.2","1","93","1088","-78","10.0","First floor living room"
"2021-04-12T18:43:52.000Z","2021-04-12T14:43:52.000-04:00","3a3c05d6-a3a3-434f-c5f9-47aa04d41117","3.6","69.8","52","68.0","1","81","715","-73","10.0","Second floor office"
"2021-04-12T18:44:21.000Z","2021-04-12T14:44:21.000-04:00","1ec1fe6d-9db0-2334-dee3-48a58d821704","3.6","68.0","56","66.2","1","95","1104","-76","7.0","First floor living room"
"2021-04-12T18:48:52.000Z","2021-04-12T14:48:52.000-04:00","3a3c05d6-a3a3-434f-c5f9-47aa04d41117","3.6","69.8","52","68.0","1","93","814","-75","9.0","Second floor office"
"2021-04-12T18:49:21.00

garystafford

def dict_from_payload(base64_input: str, fport: int = None):
    """ Healthy Home Sensor IAQ (TBHV110) binary payload decoder """
    
    decoded = base64.b64decode(base64_input)

    # Byte 0, bit 0
    status = decoded[0] & 0b00000001  # (1 << 1) - 1

    # Byte 1, bits 3:0
    battery = decoded[1] & 0b00001111  # (1 << 4) - 1

garystafford

# base64 encoded binary message
AAs0LNsCAQB/ADM=

# base64 decoded binary message
b'\x00\x0b4,\xdb\x02\x01\x00\x7f\x003'

bytes    hex    dec     binary
--------------------------------
  1      00      0      00000000
  2      0b     11      00001011

garystafford

def dict_from_payload(base64_input: str, fport: int = None):
    """ Healthy Home Sensor IAQ (TBHV110) binary payload decoder """
    
    decoded = base64.b64decode(base64_input)

    # Byte 1
    status = decoded[0] & 0b00000001

    # Byte 2
    battery = decoded[1] & 0b00001111

garystafford

{
    "WirelessDeviceId": "3a3c05d6-a3a3-434f-c5f9-47aa04d41117",
    "PayloadData": "AAs0LNsCAQB/ADM=",
    "WirelessMetadata": {
        "LoRaWAN": {
            "DataRate": "3",
            "DevEui": "40bb42d1c49fa3c1",
            "FCnt": 1426,
            "FPort": 103,
            "Frequency": "904500000",

garystafford

{
    "WirelessDeviceId": "3a3c05d6-a3a3-434f-c5f9-47aa04d41117",
    "PayloadData": {
        "Status": 0,
        "Battery": 3.6,
        "BoardTemp": 20,
        "RH": 44,
        "ECO2": 731,
        "VOC": 1,
        "IAQ": 127,

garystafford

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,

garystafford

SELECT WirelessDeviceId, WirelessMetadata, "tbhv110_915" as PayloadDecoderName,
       aws_lambda("arn:aws:lambda:us-east-1:111222333444:function:lorawan-iot-core-TransformLoRaWANBinaryPayloadFunc-RQOC3C6CKGWZ",
                  {"PayloadDecoderName": "tbhv110_915",
                   "PayloadData": PayloadData,
                   "WirelessDeviceId": WirelessDeviceId,
                   "WirelessMetadata": WirelessMetadata}) as PayloadData

garystafford

{
    "data": {
        "co": 0.0031827073092533685,
        "humidity": 51.099998474121094,
        "light": true,
        "lpg": 0.005553622262501496,
        "motion": false,
        "smoke": 0.01449612738171321,
        "temperature": 19.100000381469727
    },

garystafford

-- create policies that automatically refreshes continuous aggregates
SELECT add_continuous_aggregate_policy('air_quality_summary_minute',
    start_offset => INTERVAL '1 week',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

SELECT add_continuous_aggregate_policy('light_summary_minute',
    start_offset => INTERVAL '1 week',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');