Insert values from http request into SQLite

In my flow I have two http-request nodes that I use (every ten minutes) to get data from an API. I filter the request data through five functions and I end up with five values:

  1. temperature
  2. humidity
  3. pressure
  4. PM10
  5. PM2.5

Now I have to store these variables into a new sqlite db, calculate the mean for each one (every two hours) and save it in a different table of my sqlite db. Lastly, I have to plot the mean.

I spent the last two days trying to store the values in a db but I'm so stuck. Can you please help me?

My flow is:

[
    {
        "id": "9bd4d4a2aefc85ef",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "b8b922afb22bcc64",
        "type": "http request",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "method": "GET",
        "ret": "obj",
        "paytoqs": "ignore",
        "url": "data.sensor.community/airrohr/v1/sensor/56402/",
        "tls": "",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "x": 490,
        "y": 180,
        "wires": [
            [
                "a7c40c9260131891",
                "648ae758795c8323",
                "52f3e8cec5632c1d"
            ]
        ]
    },
    {
        "id": "972baebc079e5737",
        "type": "inject",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "600",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 130,
        "y": 180,
        "wires": [
            [
                "cab8ba5f69ac662f"
            ]
        ]
    },
    {
        "id": "cab8ba5f69ac662f",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "msg headers",
        "func": "\nmsg.headers = {};\nmsg.headers['content-type'] = 'application/json';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 310,
        "y": 180,
        "wires": [
            [
                "b8b922afb22bcc64",
                "4ce3f8e1f62d363c"
            ]
        ]
    },
    {
        "id": "a7c40c9260131891",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Temperature",
        "func": "var temp = {};\n\ntemp.payload = Number(msg.payload[0].sensordatavalues[0].value);\n\ntemp.topic = \"Temperature: \";\n\nreturn temp;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 690,
        "y": 120,
        "wires": [
            [
                "a93e0c9e08a7b2ce"
            ]
        ]
    },
    {
        "id": "648ae758795c8323",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Pressure",
        "func": "var press = {};\n\npress.payload = Number(msg.payload[0].sensordatavalues[1].value);\npress.topic = \"Pressure: \";\n\nreturn press;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 700,
        "y": 180,
        "wires": [
            [
                "80fda223a34e984b"
            ]
        ]
    },
    {
        "id": "52f3e8cec5632c1d",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Humidity",
        "func": "var hum = {};\n\nhum.payload = Number(msg.payload[0].sensordatavalues[2].value);\n\nhum.topic = \"Humidity: \";\n\nreturn hum;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 700,
        "y": 240,
        "wires": [
            [
                "905a3adce3845d77"
            ]
        ]
    },
    {
        "id": "a93e0c9e08a7b2ce",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "5ad727043a9a374f",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "Temperature",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "-10",
        "ymax": "40",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 890,
        "y": 120,
        "wires": [
            []
        ]
    },
    {
        "id": "80fda223a34e984b",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "eee15df9519e1362",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "Pressure",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 880,
        "y": 180,
        "wires": [
            []
        ]
    },
    {
        "id": "905a3adce3845d77",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "a13ed6592e65c37d",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "Humidity",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 880,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "4ce3f8e1f62d363c",
        "type": "http request",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "method": "GET",
        "ret": "obj",
        "paytoqs": "ignore",
        "url": "data.sensor.community/airrohr/v1/sensor/56401/",
        "tls": "",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "x": 490,
        "y": 380,
        "wires": [
            [
                "332f6ceab8a668af",
                "c9ba0952a84ce5d1"
            ]
        ]
    },
    {
        "id": "332f6ceab8a668af",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "PM10",
        "func": "var pm10 = {};\n\npm10.payload = Number(msg.payload[0].sensordatavalues[0].value);\n\npm10.topic = \"PM10: \";\n\nreturn pm10;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 690,
        "y": 340,
        "wires": [
            [
                "8355037b867a0a36"
            ]
        ]
    },
    {
        "id": "c9ba0952a84ce5d1",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "PM2.5",
        "func": "var pm25 = {};\n\npm25.payload = Number(msg.payload[0].sensordatavalues[1].value);\n\npm25.topic = \"PM25: \";\n\nreturn pm25;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 690,
        "y": 440,
        "wires": [
            [
                "f6aec9e2bde9e73e"
            ]
        ]
    },
    {
        "id": "8355037b867a0a36",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "bbd1bb4c3c1006dc",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "PM10",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 870,
        "y": 340,
        "wires": [
            []
        ]
    },
    {
        "id": "f6aec9e2bde9e73e",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "12ead6f68e7681c4",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "PM2.5",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 870,
        "y": 440,
        "wires": [
            []
        ]
    },
    {
        "id": "5ad727043a9a374f",
        "type": "ui_group",
        "name": "Temperature",
        "tab": "922b63a179737013",
        "order": 1,
        "disp": true,
        "width": "8",
        "collapse": false,
        "className": ""
    },
    {
        "id": "eee15df9519e1362",
        "type": "ui_group",
        "name": "Pressure",
        "tab": "922b63a179737013",
        "order": 2,
        "disp": true,
        "width": "8",
        "collapse": false,
        "className": ""
    },
    {
        "id": "a13ed6592e65c37d",
        "type": "ui_group",
        "name": "Humidity",
        "tab": "922b63a179737013",
        "order": 3,
        "disp": true,
        "width": "8",
        "collapse": false,
        "className": ""
    },
    {
        "id": "bbd1bb4c3c1006dc",
        "type": "ui_group",
        "name": "PM10",
        "tab": "f7ca8a44f3ea797f",
        "order": 4,
        "disp": true,
        "width": "10",
        "collapse": false,
        "className": ""
    },
    {
        "id": "12ead6f68e7681c4",
        "type": "ui_group",
        "name": "PM2.5",
        "tab": "f7ca8a44f3ea797f",
        "order": 5,
        "disp": true,
        "width": "10",
        "collapse": false,
        "className": ""
    },
    {
        "id": "922b63a179737013",
        "type": "ui_tab",
        "name": "Sensor 56402: Temperature, Pressure, Humidity",
        "icon": "dashboard",
        "order": 2,
        "disabled": false,
        "hidden": false
    },
    {
        "id": "f7ca8a44f3ea797f",
        "type": "ui_tab",
        "name": "Sensor 56401: PM2.5, PM10",
        "icon": "dashboard",
        "order": 1,
        "disabled": false,
        "hidden": false
    }
]

You flow doesn't show anything involved with inserting into a sqlite DB, all it shows is you sending data to the dashboard.

Show us the flows dealing with formatting and sending the data to the database.

That's the problem. I'm using the sqlite node but I can't get my task done. I'm almost ashamed to send what I did but I'll try again shortly and I'll post it here.

Make sure you carefully read the help text for the sqlite node, decide what query you need and work out what you need to pass to the node, then concentrate on constructing that data and feeding it into a debug node. Only when it looks right, connect it to the dB node.

Ok so I tried something and I managed to store the values in a database. The flow is:

[
    {
        "id": "b8b922afb22bcc64",
        "type": "http request",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "method": "GET",
        "ret": "obj",
        "paytoqs": "ignore",
        "url": "data.sensor.community/airrohr/v1/sensor/56402/",
        "tls": "",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "credentials": {},
        "x": 450,
        "y": 540,
        "wires": [
            [
                "a7c40c9260131891",
                "648ae758795c8323",
                "52f3e8cec5632c1d",
                "2e5959cfb6f251a1",
                "5a368daa726e13bf"
            ]
        ]
    },
    {
        "id": "972baebc079e5737",
        "type": "inject",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "600",
        "crontab": "",
        "once": true,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 90,
        "y": 540,
        "wires": [
            [
                "cab8ba5f69ac662f"
            ]
        ]
    },
    {
        "id": "cab8ba5f69ac662f",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "msg headers",
        "func": "\nmsg.headers = {};\nmsg.headers['content-type'] = 'application/json';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 270,
        "y": 540,
        "wires": [
            [
                "b8b922afb22bcc64",
                "4ce3f8e1f62d363c"
            ]
        ]
    },
    {
        "id": "a7c40c9260131891",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Temperature",
        "func": "var temp = 0;\n\nfor (let i = 0; i < msg.payload.length; i++) {\n    temp = temp + Number(msg.payload[i].sensordatavalues[0].value);\n}\n\nvar tempe = {};\ntempe.payload = temp/msg.payload.length;\ntempe.topic = \"Temperature: \";\n\nreturn tempe;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 690,
        "y": 540,
        "wires": [
            [
                "a93e0c9e08a7b2ce"
            ]
        ]
    },
    {
        "id": "648ae758795c8323",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Pressure",
        "func": "var press = 0;\n\nfor (let i = 0; i < msg.payload.length; i++) {\n    press = press + Number(msg.payload[i].sensordatavalues[1].value);\n}\n\nvar pressu = {};\npressu.payload = press / msg.payload.length;\npressu.topic = \"Pressure: \";\n\nreturn pressu;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 700,
        "y": 600,
        "wires": [
            [
                "80fda223a34e984b"
            ]
        ]
    },
    {
        "id": "52f3e8cec5632c1d",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Humidity",
        "func": "var hum = 0;\n\nfor (let i = 0; i < msg.payload.length; i++) {\n    hum = hum + Number(msg.payload[i].sensordatavalues[2].value);\n}\n\nvar humi = {};\nhumi.payload = hum / msg.payload.length;\nhumi.topic = \"Humidity: \";\n\nreturn humi;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 700,
        "y": 660,
        "wires": [
            [
                "905a3adce3845d77"
            ]
        ]
    },
    {
        "id": "a93e0c9e08a7b2ce",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "5ad727043a9a374f",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "Temperature",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "-10",
        "ymax": "40",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 890,
        "y": 540,
        "wires": [
            []
        ]
    },
    {
        "id": "80fda223a34e984b",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "eee15df9519e1362",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "Pressure",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 880,
        "y": 600,
        "wires": [
            []
        ]
    },
    {
        "id": "905a3adce3845d77",
        "type": "ui_chart",
        "z": "9bd4d4a2aefc85ef",
        "name": "",
        "group": "a13ed6592e65c37d",
        "order": 1,
        "width": 0,
        "height": 0,
        "label": "Humidity",
        "chartType": "line",
        "legend": "false",
        "xformat": "HH:mm:ss",
        "interpolate": "linear",
        "nodata": "",
        "dot": false,
        "ymin": "",
        "ymax": "",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#aec7e8",
            "#ff7f0e",
            "#2ca02c",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 880,
        "y": 660,
        "wires": [
            []
        ]
    },
    {
        "id": "b3fb7bc610ef2c8c",
        "type": "inject",
        "z": "9bd4d4a2aefc85ef",
        "name": "Create DB",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 140,
        "y": 40,
        "wires": [
            [
                "6c7b328f04d04678"
            ]
        ]
    },
    {
        "id": "6c7b328f04d04678",
        "type": "sqlite",
        "z": "9bd4d4a2aefc85ef",
        "mydb": "c03247d7a40f32e6",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "",
        "x": 550,
        "y": 40,
        "wires": [
            [
                "b06277d286ead2e8"
            ]
        ]
    },
    {
        "id": "b06277d286ead2e8",
        "type": "debug",
        "z": "9bd4d4a2aefc85ef",
        "name": "debug 1",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 760,
        "y": 40,
        "wires": []
    },
    {
        "id": "70db6777f07ec6b0",
        "type": "inject",
        "z": "9bd4d4a2aefc85ef",
        "name": "Create Table (temp etc)",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "CREATE TABLE TEMP( TEMPERATURE NUMERIC, PRESSURE NUMERIC, HUMIDITY NUMERIC)",
        "payload": "",
        "payloadType": "date",
        "x": 180,
        "y": 100,
        "wires": [
            [
                "f686c22b2a7d7ef2"
            ]
        ]
    },
    {
        "id": "f686c22b2a7d7ef2",
        "type": "sqlite",
        "z": "9bd4d4a2aefc85ef",
        "mydb": "c03247d7a40f32e6",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "",
        "x": 550,
        "y": 100,
        "wires": [
            [
                "71ff3c49529d2c8f"
            ]
        ]
    },
    {
        "id": "71ff3c49529d2c8f",
        "type": "debug",
        "z": "9bd4d4a2aefc85ef",
        "name": "debug 2",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "topic",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 760,
        "y": 100,
        "wires": []
    },
    {
        "id": "2e5959cfb6f251a1",
        "type": "function",
        "z": "9bd4d4a2aefc85ef",
        "name": "Add to table",
        "func": "//--------------------TEMPERATURE----------------------//\nvar temp = 0;\nfor (let i = 0; i < msg.payload.length; i++) {\n    temp = temp + Number(msg.payload[i].sensordatavalues[0].value);\n}\n\nvar tabletempe = {};\ntabletempe.payload = temp / msg.payload.length;\ntabletempe.topic = \"Temperature: \";\n\n//----------------------PRESSURE----------------------//\n\nvar press = 0;\nfor (let j = 0; j < msg.payload.length; j++) {\n    press = press + Number(msg.payload[j].sensordatavalues[1].value);\n}\n\nvar tablepressu = {};\ntablepressu.payload = press / msg.payload.length;\ntablepressu.topic = \"Pressure: \";\n\n//----------------------HUMIDITY---------------------//\n\nvar hum = 0;\nfor (let k = 0; k < msg.payload.length; k++) {\n    hum = hum + Number(msg.payload[k].sensordatavalues[2].value);\n}\n\nvar tablehumi = {};\ntablehumi.payload = hum / msg.payload.length;\ntablehumi.topic = \"Humidity: \";\n\n//----------------------MESSAGE---------------------//\n\nvar newMsg = {\n    \"topic\": \"INSERT INTO TEMP VALUES ( \" + tabletempe.payload + \", \" + tablepressu.payload + \", \" + tablehumi.payload + \")\"\n}\n\nreturn newMsg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 710,
        "y": 720,
        "wires": [
            [
                "048c143c6b197cc4"
            ]
        ]
    },
    {
        "id": "048c143c6b197cc4",
        "type": "sqlite",
        "z": "9bd4d4a2aefc85ef",
        "mydb": "c03247d7a40f32e6",
        "sqlquery": "msg.topic",
        "sql": "",
        "name": "",
        "x": 950,
        "y": 720,
        "wires": [
            [
                "00e7cfd146a07f16"
            ]
        ]
    },
    {
        "id": "5a368daa726e13bf",
        "type": "debug",
        "z": "9bd4d4a2aefc85ef",
        "name": "debug 17",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 680,
        "y": 480,
        "wires": []
    },
    {
        "id": "00e7cfd146a07f16",
        "type": "debug",
        "z": "9bd4d4a2aefc85ef",
        "name": "debug 18",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1180,
        "y": 720,
        "wires": []
    },
    {
        "id": "5ad727043a9a374f",
        "type": "ui_group",
        "name": "Temperature",
        "tab": "922b63a179737013",
        "order": 1,
        "disp": true,
        "width": "8",
        "collapse": false,
        "className": ""
    },
    {
        "id": "eee15df9519e1362",
        "type": "ui_group",
        "name": "Pressure",
        "tab": "922b63a179737013",
        "order": 2,
        "disp": true,
        "width": "8",
        "collapse": false,
        "className": ""
    },
    {
        "id": "a13ed6592e65c37d",
        "type": "ui_group",
        "name": "Humidity",
        "tab": "922b63a179737013",
        "order": 3,
        "disp": true,
        "width": "8",
        "collapse": false,
        "className": ""
    },
    {
        "id": "c03247d7a40f32e6",
        "type": "sqlitedb",
        "z": "9bd4d4a2aefc85ef",
        "db": "CRETE_NEWNEW",
        "mode": "RWC"
    },
    {
        "id": "922b63a179737013",
        "type": "ui_tab",
        "name": "Sensor 56402: Temperature, Pressure, Humidity",
        "icon": "dashboard",
        "order": 2,
        "disabled": false,
        "hidden": false
    }
]

The problem is that now I have to get the values back from the database and write a function that will calculate the mean every two hours.

So you need to use a SELECT statement. here is a set of flows showing different SQLite statements that might help you out.

[{"id":"b996e9de.cd54c8","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO hvac4(\n    ID,\n    UTC,\n    DateTime,\n    hp, \n    lp  \n    )\nVALUES\n    (1695,1583209921955,\"2020/03/02T20:32:01\",77.1,74.2),\n(1694,1583207096121,\"2020/03/02T19:44:56\",78.2,76.6),\n(1693,1583206453163,\"2020/03/02T19:34:13\",77.9,76.4),\n(1692,1583164232433,\"2020/03/02T07:50:32\",74,78.1),\n(1691,1583163932418,\"2020/03/02T07:45:32\",73.3,75.2),\n(1690,1583163632415,\"2020/03/02T07:40:32\",73.1,73.7),\n(1689,1583163332419,\"2020/03/02T07:35:32\",73.1,72),\n(1688,1583163032423,\"2020/03/02T07:30:32\",73,72.1),\n(1687,1583162732413,\"2020/03/02T07:25:32\",73.4,72.7),\n(1686,1583162432401,\"2020/03/02T07:20:32\",73.5,73.3),\n(1685,1583162132391,\"2020/03/02T07:15:32\",72.6,71),\n(1684,1583161832396,\"2020/03/02T07:10:32\",72.7,71.2),\n(1683,1583161532392,\"2020/03/02T07:05:32\",72.9,71.8),\n(1682,1583161232394,\"2020/03/02T07:00:32\",72.9,72.2),\n(1681,1583160932396,\"2020/03/02T06:55:32\",72.9,72),\n(1680,1583160632397,\"2020/03/02T06:50:32\",72.9,71.6),\n(1679,1583160332382,\"2020/03/02T06:45:32\",73.3,72.1),\n(1678,1583160032392,\"2020/03/02T06:40:32\",73.2,71.9),\n(1677,1583159732387,\"2020/03/02T06:35:32\",72.6,70.2),\n(1676,1583159432365,\"2020/03/02T06:30:32\",72.7,70.3),\n(1675,1583159132367,\"2020/03/02T06:25:32\",72.8,70.4),\n(1674,1583158832363,\"2020/03/02T06:20:32\",73,70.9),\n(1673,1583158532364,\"2020/03/02T06:15:32\",73.4,71.7),\n(1672,1583158232344,\"2020/03/02T06:10:32\",73.7,72.5),\n(1671,1583157932355,\"2020/03/02T06:05:32\",72.8,70.2),\n(1670,1583157632345,\"2020/03/02T06:00:32\",72.9,70.1),\n(1669,1583157332343,\"2020/03/02T05:55:32\",73,70.2),\n(1668,1583157032339,\"2020/03/02T05:50:32\",73.1,70.6),\n(1667,1583156732330,\"2020/03/02T05:45:32\",73.3,71.1),\n(1666,1583156432324,\"2020/03/02T05:40:32\",73.8,72),\n(1665,1583156132316,\"2020/03/02T05:35:32\",73.8,72.4),\n(1664,1583155832318,\"2020/03/02T05:30:32\",73,70.3),\n(1663,1583155532312,\"2020/03/02T05:25:32\",73.1,70.3),\n(1662,1583155232313,\"2020/03/02T05:20:32\",73.2,70.4),\n(1663,1582887743604,\"2020/02/28T03:02:23\",72.9,69.6)\n","output":"str","x":360,"y":100,"wires":[["25b23f04.053a1"]]}]
1 Like

Can you please re-paste the JSON? I think that something might be missing

Whoops, I had to edit the flow to reduce the number of characters and then just selected the template instead of the complete flow :face_with_open_eyes_and_hand_over_mouth:

[{"id":"b54cfc20.fecb38","type":"tab","label":"Flow 1","disabled":false,"info":"","_mcu":{"mcu":false}},{"id":"5d644bc7.ff4e5c","type":"sqlite","z":"b54cfc20.fecb38","mydb":"b48607bf.623f88","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":110,"y":140,"wires":[["f82e9731.4a16a"]]},{"id":"771a4816.0c4cd","type":"inject","z":"b54cfc20.fecb38","name":"Create Table","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":60,"wires":[["1f7f15b5.e2d6aa"]]},{"id":"f82e9731.4a16a","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":110,"y":180,"wires":[]},{"id":"1f7f15b5.e2d6aa","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE hvac4(\n    ID INTEGER PRIMARY KEY AUTOINCREMENT, \n    UTC INTEGER,\n    DateTime TEXT,\n    hp NUMERIC, \n    lp NUMERIC \n)","output":"str","x":120,"y":100,"wires":[["5d644bc7.ff4e5c"]]},{"id":"25b23f04.053a1","type":"sqlite","z":"b54cfc20.fecb38","mydb":"b48607bf.623f88","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":350,"y":140,"wires":[["16c71ea.b668961"]]},{"id":"56c189c6.5cab38","type":"inject","z":"b54cfc20.fecb38","name":"Insert multi rows","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":380,"y":60,"wires":[["b996e9de.cd54c8"]]},{"id":"16c71ea.b668961","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":350,"y":180,"wires":[]},{"id":"b996e9de.cd54c8","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO hvac4(\n    ID,\n    UTC,\n    DateTime,\n    hp, \n    lp  \n    )\nVALUES\n    (1695,1583209921955,\"2020/03/02T20:32:01\",77.1,74.2),\n(1694,1583207096121,\"2020/03/02T19:44:56\",78.2,76.6),\n(1693,1583206453163,\"2020/03/02T19:34:13\",77.9,76.4),\n(1692,1583164232433,\"2020/03/02T07:50:32\",74,78.1),\n(1691,1583163932418,\"2020/03/02T07:45:32\",73.3,75.2),\n(1690,1583163632415,\"2020/03/02T07:40:32\",73.1,73.7),\n(1689,1583163332419,\"2020/03/02T07:35:32\",73.1,72),\n(1688,1583163032423,\"2020/03/02T07:30:32\",73,72.1),\n(1687,1583162732413,\"2020/03/02T07:25:32\",73.4,72.7),\n(1686,1583162432401,\"2020/03/02T07:20:32\",73.5,73.3),\n(1685,1583162132391,\"2020/03/02T07:15:32\",72.6,71),\n(1684,1583161832396,\"2020/03/02T07:10:32\",72.7,71.2),\n(1683,1583161532392,\"2020/03/02T07:05:32\",72.9,71.8),\n(1682,1583161232394,\"2020/03/02T07:00:32\",72.9,72.2),\n(1681,1583160932396,\"2020/03/02T06:55:32\",72.9,72),\n(1680,1583160632397,\"2020/03/02T06:50:32\",72.9,71.6),\n(1679,1583160332382,\"2020/03/02T06:45:32\",73.3,72.1),\n(1678,1583160032392,\"2020/03/02T06:40:32\",73.2,71.9),\n(1677,1583159732387,\"2020/03/02T06:35:32\",72.6,70.2),\n(1676,1583159432365,\"2020/03/02T06:30:32\",72.7,70.3),\n(1675,1583159132367,\"2020/03/02T06:25:32\",72.8,70.4),\n(1674,1583158832363,\"2020/03/02T06:20:32\",73,70.9),\n(1673,1583158532364,\"2020/03/02T06:15:32\",73.4,71.7),\n(1672,1583158232344,\"2020/03/02T06:10:32\",73.7,72.5),\n(1671,1583157932355,\"2020/03/02T06:05:32\",72.8,70.2),\n(1672,1582887743604,\"2020/02/28T03:02:23\",72.9,69.6)\n","output":"str","x":360,"y":100,"wires":[["25b23f04.053a1"]]},{"id":"d58df386.29cda","type":"sqlite","z":"b54cfc20.fecb38","mydb":"b48607bf.623f88","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":630,"y":140,"wires":[["79c33077.edf418"]]},{"id":"5959dc0c.ad3044","type":"inject","z":"b54cfc20.fecb38","name":"select count","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":650,"y":60,"wires":[["9bd539da.7d961"]]},{"id":"79c33077.edf418","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":630,"y":180,"wires":[]},{"id":"9bd539da.7d961","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT COUNT(*) from hvac4","output":"str","x":640,"y":100,"wires":[["d58df386.29cda"]]},{"id":"b519b9b3.50ea8","type":"catch","z":"b54cfc20.fecb38","name":"","scope":null,"uncaught":false,"x":400,"y":240,"wires":[["4191d6d0.a0cd38"]]},{"id":"4191d6d0.a0cd38","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":570,"y":240,"wires":[]},{"id":"bbe03555.3275f8","type":"sqlite","z":"b54cfc20.fecb38","mydb":"b48607bf.623f88","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":610,"y":440,"wires":[["32019594.6436a2"]]},{"id":"5a533e19.2c36a8","type":"inject","z":"b54cfc20.fecb38","name":"Insert customer data","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":420,"wires":[["dd91b199.b445f8"]]},{"id":"32019594.6436a2","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":750,"y":440,"wires":[]},{"id":"dd91b199.b445f8","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO customers\n\t(CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country)\nVALUES\n\t(1,\"Alfreds Futterkiste\",\"Maria Anders\",\"Obere Str. 57\",\"Berlin\",\"12209\",\"Germany\"),\n\t(2,\"Ana Trujillo Emparedados y helados\",\"Ana Trujillo\",\"Avda. de la Constitución 2222\",\"México D.F.\",\"05021\",\"Mexico\"),\n\t(3,\"Antonio Moreno Taquería\",\"Antonio Moreno\",\"Mataderos 2312\",\"México D.F.\",\"05023\",\"Mexico\"),\n\t(4,\"Around the Horn\",\"Thomas Hardy\",\"120 Hanover Sq.\",\"London\",\"WA1 1DP\",\"UK\"),\n\t(5,\"Berglunds snabbköp\",\"Christina Berglund\",\"Berguvsvägen 8\",\"Luleå\",\"S-958 22\",\"Sweden\"),\n\t(6,\"Blauer See Delikatessen\",\"Hanna Moos\",\"Forsterstr. 57\",\"Mannheim\",\"68306\",\"Germany\"),\n\t(7,\"Blondel père et fils\",\"Frédérique Citeaux\",\"24, place Kléber\",\"Strasbourg\",\"67000\",\"France\"),\n\t(8,\"Bólido Comidas preparadas\",\"Martín Sommer\",\"C/ Araquil, 67\",\"Madrid\",\"28023\",\"Spain\"),\n\t(9,\"Bon app'\",\"Laurence Lebihans\",\"12, rue des Bouchers\",\"Marseille\",\"13008\",\"France\"),\n\t(10,\"Wolski\",\"Zbyszek\",\"ul. Filtrowa 68\",\"Walla\",\"01-012\",\"Poland\" )\n\t","output":"str","x":380,"y":420,"wires":[["bbe03555.3275f8"]]},{"id":"724fcf56.87c3b","type":"sqlite","z":"b54cfc20.fecb38","mydb":"b48607bf.623f88","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":850,"y":140,"wires":[["3c34509f.0411d8"]]},{"id":"50f1479e.da212","type":"inject","z":"b54cfc20.fecb38","name":"delete data","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":860,"y":60,"wires":[["6fbe4803.d0e52"]]},{"id":"3c34509f.0411d8","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":850,"y":180,"wires":[]},{"id":"6fbe4803.d0e52","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"DELETE FROM hvac4","output":"str","x":860,"y":100,"wires":[["724fcf56.87c3b"]]},{"id":"d98abcd1.951c48","type":"comment","z":"b54cfc20.fecb38","name":"Customers table - sql examples","info":"","x":170,"y":320,"wires":[]},{"id":"654a2d93.1a9494","type":"inject","z":"b54cfc20.fecb38","name":"Create CUSTOMERS Table","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":170,"y":380,"wires":[["adf54a0f.2e44f8"]]},{"id":"adf54a0f.2e44f8","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"CREATE TABLE customers (\n    CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,\n    CustomerName TEXT,\n    ContactName TEXT,\n    Address TEXT,\n    City TEXT,\n    PostalCode TEXT,\n    Country TEXT\n)","output":"str","x":380,"y":380,"wires":[["bbe03555.3275f8"]]},{"id":"12851785.ee1568","type":"inject","z":"b54cfc20.fecb38","name":"select count","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":130,"y":460,"wires":[["c5f03349.753ed8"]]},{"id":"c5f03349.753ed8","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT COUNT(*) from customers","output":"str","x":380,"y":460,"wires":[["bbe03555.3275f8"]]},{"id":"abdfe668.954998","type":"inject","z":"b54cfc20.fecb38","name":"select count by country","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":500,"wires":[["21c0f11.f808a8e"]]},{"id":"21c0f11.f808a8e","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT Country, COUNT(Country) from customers\nGROUP BY Country","output":"str","x":380,"y":500,"wires":[["bbe03555.3275f8"]]},{"id":"1dbb655b.5f83b3","type":"debug","z":"b54cfc20.fecb38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":610,"y":360,"wires":[]},{"id":"637eca86.f8d084","type":"inject","z":"b54cfc20.fecb38","name":"select * by country","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":540,"wires":[["fd213018.5a542"]]},{"id":"fd213018.5a542","type":"template","z":"b54cfc20.fecb38","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT * from customers\nORDER BY Country","output":"str","x":380,"y":540,"wires":[["bbe03555.3275f8"]]},{"id":"b48607bf.623f88","type":"sqlitedb","db":"/Users/Paul/databases/hvac4.db","mode":"RWC"}]
2 Likes

You can get sqlite to sum the values for you SQLite SUM: Return The Sum value in A Set of Values

[Edit Or indeed to work out the mean SQLite AVG: Calculate The Average Value In a Set

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.