install the node red addon.
Install HACS addon
Install HACS integration
Using HACS you install the Node-RED Companion Integration
In node red, go to the menu and click on manage pallete
Install node-red-node-sqlite
Then import the flow below:
[
{
"id": "258e651031754485",
"type": "sqlite",
"z": "7547a558cf945eb1",
"mydb": "f235761944d077a5",
"sqlquery": "msg.topic",
"sql": "SELECT\n state\nFROM\n statistics\n INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id\nWHERE\n statistics_meta.statistic_id = 'sensor.consumo_total'\n AND statistics.start_ts = strftime('%s', '2025-01-06 11:00:00')\nLIMIT\n 1;\n",
"name": "",
"x": 1680,
"y": 700,
"wires": [
[
"dfeeca3795cb819f",
"986254e886b19148"
]
]
},
{
"id": "92151399e29a39c6",
"type": "inject",
"z": "7547a558cf945eb1",
"name": "",
"props": [
{
"p": "sensor",
"v": "sensor.consumo_total",
"vt": "str"
},
{
"p": "data_ultima_leitura",
"v": "2025-01-09T11:00",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"x": 1250,
"y": 700,
"wires": [
[
"8e60c843a93faa17"
]
]
},
{
"id": "dfeeca3795cb819f",
"type": "debug",
"z": "7547a558cf945eb1",
"name": "debug 22",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"statusVal": "",
"statusType": "auto",
"x": 1960,
"y": 600,
"wires": []
},
{
"id": "8e60c843a93faa17",
"type": "function",
"z": "7547a558cf945eb1",
"name": "function 3",
"func": "msg.topic = `\nSELECT\n state\nFROM\n statistics\n INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id\nWHERE\n statistics_meta.statistic_id = '${msg.sensor}'\n AND statistics.start_ts = strftime('%s', '${msg.data_ultima_leitura}')\nLIMIT\n 1;\n`;\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1420,
"y": 700,
"wires": [
[
"258e651031754485"
]
]
},
{
"id": "986254e886b19148",
"type": "ha-sensor",
"z": "7547a558cf945eb1",
"name": "simplesql",
"entityConfig": "2bd5e7607412cee5",
"version": 0,
"state": "payload[0].state",
"stateType": "msg",
"attributes": [],
"inputOverride": "allow",
"outputProperties": [],
"x": 1940,
"y": 700,
"wires": [
[]
]
},
{
"id": "f235761944d077a5",
"type": "sqlitedb",
"db": "/share/ha_db/home-assistant_v2.db",
"mode": "RO"
},
{
"id": "2bd5e7607412cee5",
"type": "ha-entity-config",
"server": "33803899.487d48",
"deviceConfig": "",
"name": "simplesql",
"version": 6,
"entityType": "sensor",
"haConfig": [
{
"property": "name",
"value": "simplesql"
},
{
"property": "icon",
"value": ""
},
{
"property": "entity_picture",
"value": ""
},
{
"property": "entity_category",
"value": ""
},
{
"property": "device_class",
"value": ""
},
{
"property": "unit_of_measurement",
"value": ""
},
{
"property": "state_class",
"value": ""
}
],
"resend": false,
"debugEnabled": false
},
{
"id": "33803899.487d48",
"type": "server",
"name": "Home Assistant",
"version": 5,
"addon": true,
"rejectUnauthorizedCerts": true,
"ha_boolean": "y|yes|true|on|home|open",
"connectionDelay": true,
"cacheJson": true,
"heartbeat": false,
"heartbeatInterval": "30",
"areaSelector": "friendlyName",
"deviceSelector": "friendlyName",
"entitySelector": "friendlyName",
"statusSeparator": "at: ",
"statusYear": "hidden",
"statusMonth": "short",
"statusDay": "numeric",
"statusHourCycle": "h23",
"statusTimeFormat": "h:m",
"enableGlobalContextStore": true
}
]