Edit inject node working with Between : missing inject

General
1

Lectori Salutem,

18 Feb 15:21:58 - [info] Node-RED version: v3.1.5
18 Feb 15:21:58 - [info] Node.js version: v18.19.1

PC Regional Time: Brussels + DayLightSaving, so is now a 2h difference
MariaDB : field “sqldt” set as TimeStamp UTC

Flow : Every hh:30 (local time) an inject triggers an API-call and the return is saved in DB with sqldt = (hh-2):00 while working with ‘ date.getTimezoneOffset()’;

  • Inject
  • Function : Format SQL
  • http request node
  • Function : extract and format SQL
  • MySQL node

Config of the EditInjectNode:

  • Repeat: Interval between times
  • Every: 30 minutes
  • Between: 00:00 and 00:00

Works fine but missing record 02:00.

So I logged extra in the function node msg.topic which holds the request-SQL, to a txt-file
msg.payload = { dd: new Date(), sqldt };

This is the txt-result:
{"dd":"2024-05-30T22:30:00.160Z","sqldt":"2024-05-30 22:00:00"}
{"dd":"2024-05-30T23:30:00.125Z","sqldt":"2024-05-30 23:00:00"}
{"dd":"2024-05-31T00:30:00.113Z","sqldt":"2024-05-31 00:00:00"}
{"dd":"2024-05-31T01:30:00.126Z","sqldt":"2024-05-31 01:00:00"}
{"dd":"2024-05-31T03:30:00.119Z","sqldt":"2024-05-31 03:00:00"}
{"dd":"2024-05-31T04:30:00.120Z","sqldt":"2024-05-31 04:00:00"}

If "Between" is set to 00:00 and 23:00, then record 21:00 is missing.

I tried to set “Between”: 00:00 and 23:59 but “Between” only accepts minutes=00

Thanks in advance for any suggestion

T

PS : a debug of the SQL in/out… @ 08:53…

31/05/2024, 08:53:57node: debug 112

msg.payload : number

2024-05-31T06:53:57.930Z

31/05/2024, 08:53:57node: debug Meteo extract

INSERT INTO AnnalesMonasterii (dateTime, KMI_temp) VALUES ('2024-05-31 06:00:00', 14) ON DUPLICATE KEY UPDATE KMI_temp = 14; :

msg.payload : Object

{ dd: "2024-05-31T06:53:57.999Z", sqldt: "2024-05-31 06:00:00" }

31/05/2024, 08:53:57node: debug 115

INSERT INTO AnnalesMonasterii (dateTime, KMI_temp) VALUES ('2024-05-31 06:00:00', 14) ON DUPLICATE KEY UPDATE KMI_temp = 14; :

msg.payload : Object

{ dd: "2024-05-31T06:53:57.999Z", sqldt: "2024-05-31 06:00:00" }

2

Hi @Tany

May I suggest you provide your flow export (removing anything secret)
Trying to understand the schematic of your flow as a description is incredibly difficult, and not easy to follow

And please remember to format it. the </> button at the top

```
Export JSON
```
3

To be certain what is going on, add a debug node set the Output to the console. Then you can look back through the log to see what the inject node is doing.

4 
[
    {
        "id": "940cc8addcfad1ae",
        "type": "comment",
        "z": "766610991b80293d",
        "name": "Meteo.be",
        "info": "",
        "x": 120,
        "y": 60,
        "wires": []
    },
    {
        "id": "9c907755cf793d56",
        "type": "http request",
        "z": "766610991b80293d",
        "name": "Meteo.be",
        "method": "GET",
        "ret": "obj",
        "paytoqs": "ignore",
        "url": "",
        "tls": "",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "x": 480,
        "y": 100,
        "wires": [
            [
                "e3ba358f99545aa8",
                "9f1809c8cfb33afb"
            ]
        ]
    },
    {
        "id": "ab4d51eec69c97e4",
        "type": "inject",
        "z": "766610991b80293d",
        "name": "test",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 170,
        "y": 140,
        "wires": [
            [
                "df11b9ae83dc4b2e"
            ]
        ]
    },
    {
        "id": "e3ba358f99545aa8",
        "type": "switch",
        "z": "766610991b80293d",
        "name": "Check Sts",
        "property": "statusCode",
        "propertyType": "msg",
        "rules": [
            {
                "t": "neq",
                "v": "200",
                "vt": "num"
            },
            {
                "t": "eq",
                "v": "200",
                "vt": "str"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 2,
        "x": 650,
        "y": 100,
        "wires": [
            [
                "bdcac97273a29fb5"
            ],
            [
                "ab56c6665fbb27ee",
                "ebc2d1bbb85dc4ae"
            ]
        ]
    },
    {
        "id": "bdcac97273a29fb5",
        "type": "change",
        "z": "766610991b80293d",
        "name": "Status NOK",
        "rules": [
            {
                "t": "set",
                "p": "#:(file)::KMIStatus",
                "pt": "global",
                "to": "payload.response_status.error_code",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "#:(file)::KMILastFetch",
                "pt": "global",
                "to": "0",
                "tot": "num"
            },
            {
                "t": "set",
                "p": "#:(file)::KMILastFailed",
                "pt": "global",
                "to": "",
                "tot": "date"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 850,
        "y": 80,
        "wires": [
            [
                "e3b7d6bd4f5bd408"
            ]
        ]
    },
    {
        "id": "e3b7d6bd4f5bd408",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "Meteo Failed",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1170,
        "y": 80,
        "wires": []
    },
    {
        "id": "08f9881e979e9dc8",
        "type": "change",
        "z": "766610991b80293d",
        "name": "Status OK",
        "rules": [
            {
                "t": "set",
                "p": "#:(file)::KMIStatus",
                "pt": "global",
                "to": "Success",
                "tot": "str"
            },
            {
                "t": "set",
                "p": "#:(file)::KMILastFetch",
                "pt": "global",
                "to": "",
                "tot": "date"
            },
            {
                "t": "set",
                "p": "#:(file)::KMILastFailed",
                "pt": "global",
                "to": "0",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 850,
        "y": 120,
        "wires": [
            [
                "2bf169adaf77e45d"
            ]
        ]
    },
    {
        "id": "2bf169adaf77e45d",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "Meteo Success",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1180,
        "y": 120,
        "wires": []
    },
    {
        "id": "ebc2d1bbb85dc4ae",
        "type": "function",
        "z": "766610991b80293d",
        "name": "extract",
        "func": "var pl=msg.payload;\n\nvar obj;\nobj = pl.features[0].properties.code;\nglobal.set(\"KMIStation\", obj, \"file\");\n\nvar objUTC;\n    objUTC = new Date(pl.features[0].properties.timestamp);    \nobj = objUTC.getTime();\nglobal.set(\"KMILastFetch\", obj, \"file\");\n\nobj = pl.features[0].properties.precip_quantity;\nglobal.set(\"KMI_precip_qty\", obj, \"file\");\nobj = pl.features[0].properties.precip_range;\nglobal.set(\"KMI_precip_range\", obj, \"file\");\n\nobj = pl.features[0].properties.temp;\nglobal.set(\"KMI_temp\", obj, \"file\");\nvar sqlTemp = obj;\n\nobj = pl.features[0].properties.temp_min;\nglobal.set(\"KMI_temp_min\", obj, \"file\");\nobj = pl.features[0].properties.temp_max;\nglobal.set(\"KMI_temp_max\", obj, \"file\");\nobj = pl.features[0].properties.wind_speed;\nglobal.set(\"KMI_wind_speed\", obj, \"file\");\nobj = pl.features[0].properties.wind_direction;\nglobal.set(\"KMI_wind_direction\", obj, \"file\");\nobj = pl.features[0].properties.wind_peak_speed;\nglobal.set(\"KMI_wind_peak_speed\", obj, \"file\");\nobj = pl.features[0].properties.humidity_relative;\nglobal.set(\"KMI_hum_rel\", obj, \"file\");\nobj = pl.features[0].properties.pressure;\nglobal.set(\"KMI_pressure\", obj, \"file\");\n\n// SQL\nmsg.payload = null;\nvar date = new Date(objUTC.getTime());\nvar tzo = date.getTimezoneOffset();\nvar time = date.getTime();\nvar utime = time + (tzo * 60000);\nvar udate = new Date(utime);\nvar uy = udate.getFullYear();\nvar um = udate.getMonth() + 1;\nvar ud = udate.getDate();\nvar uh = udate.getHours();\n\nvar sqldt = \"\" + uy + \"-\" + (\"0\"+um).slice(-2) + \"-\" + (\"0\"+ud).slice(-2) + \" \" + (\"0\"+uh).slice(-2) + \":\" + \"00:00\"\n\nmsg.topic = \"INSERT INTO  AnnalesMonasterii (dateTime, KMI_temp) VALUES ('\" + sqldt + \"', \" + sqlTemp + \") \" +\n    \"ON DUPLICATE KEY UPDATE KMI_temp = \" + sqlTemp + \";\"\n\nmsg.payload = { dd: new Date(), sqldt };\n\nreturn msg;\n\n\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 830,
        "y": 160,
        "wires": [
            [
                "b0f47fdd12759d1e",
                "4021c85791ca985f",
                "47e526e2378e5e76"
            ]
        ]
    },
    {
        "id": "ab56c6665fbb27ee",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "debug 116",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 850,
        "y": 220,
        "wires": []
    },
    {
        "id": "df11b9ae83dc4b2e",
        "type": "function",
        "z": "766610991b80293d",
        "name": "set URL",
        "func": "var  date   = new Date();\nvar tzo = date.getTimezoneOffset();\nvar time = date.getTime();\nvar utime = time + (tzo * 60000);\nvar udate = new Date(utime);\nvar uy  = udate.getFullYear();\nvar um  = udate.getMonth()+1;\nvar ud  = udate.getDate();\nvar uh  = udate.getHours();\n\n//kennelijk per uur. per 15 of 30 minuten zou ook moeten kunnen, maar nog niet gelukt\nvar strd = uy + \"-\" + (\"0\"+um).slice(-2) + \"-\" + (\"0\"+ud).slice(-2);    \nvar strt = (\"0\"+uh).slice(-2) + \":00:00.000\"\nvar obj = strd + \" \" + strt;\n\nvar url = \"https://opendata.meteo.be/service/ows?service=WFS&version=2.0.0&request=GetFeature&typenames=synop:synop_data&outputformat=json&CQL_FILTER=((BBOX(the_geom,5.450296,50.915458,5.450296,50.915458,%20%27EPSG:4326%27))%20AND%20(timestamp%20%3E=%20%27\" + strd + \"%20\" + strt + \"%27%20AND%20timestamp%20%3C=%20%27\" + strd + \"%20\" + strt + \"%27))&sortby=timestamp\";\n\nmsg.url = url;\n\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 320,
        "y": 100,
        "wires": [
            [
                "9c907755cf793d56",
                "7c95d23cd9479615"
            ]
        ]
    },
    {
        "id": "39208988d1e8879f",
        "type": "inject",
        "z": "766610991b80293d",
        "name": "30 m",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "*/30 0-23 * * *",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 150,
        "y": 100,
        "wires": [
            [
                "df11b9ae83dc4b2e"
            ]
        ]
    },
    {
        "id": "4021c85791ca985f",
        "type": "mysql",
        "z": "766610991b80293d",
        "mydb": "03a85ce256ca9d57",
        "name": "",
        "x": 1160,
        "y": 160,
        "wires": [
            []
        ]
    },
    {
        "id": "9f1809c8cfb33afb",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "debug 117",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 650,
        "y": 220,
        "wires": []
    },
    {
        "id": "7c95d23cd9479615",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "debug 118",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 450,
        "y": 220,
        "wires": []
    },
    {
        "id": "b0f47fdd12759d1e",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "debug Meteo extract",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1080,
        "y": 220,
        "wires": []
    },
    {
        "id": "251d11861c1fa6ba",
        "type": "comment",
        "z": "766610991b80293d",
        "name": "slechts 1x/h, maar lijkt enkel te werken per > hh:30",
        "info": "",
        "x": 370,
        "y": 60,
        "wires": []
    },
    {
        "id": "47e526e2378e5e76",
        "type": "file",
        "z": "766610991b80293d",
        "name": "to SSD",
        "filename": "/mnt/ssd/node-red/DebugLog.txt",
        "filenameType": "str",
        "appendNewline": true,
        "createDir": false,
        "overwriteFile": "false",
        "encoding": "none",
        "x": 1030,
        "y": 280,
        "wires": [
            [
                "c2855fbd4cbbdf20"
            ]
        ]
    },
    {
        "id": "c2855fbd4cbbdf20",
        "type": "debug",
        "z": "766610991b80293d",
        "name": "debug 119",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1230,
        "y": 280,
        "wires": []
    },
    {
        "id": "fe9600b37ad14c52",
        "type": "comment",
        "z": "766610991b80293d",
        "name": "Debug 21:00 ???",
        "info": "",
        "x": 1120,
        "y": 320,
        "wires": []
    },
    {
        "id": "03a85ce256ca9d57",
        "type": "MySQLdatabase",
        "name": "",
        "host": "RPISANDBOX",
        "port": "3306",
        "db": "S8AB01",
        "tz": "\"Europe/Brussels\"",
        "charset": "UTF8"
    }
]
5

Thanks Marcus.
just posted the flow. No secrets.
Cheers,
T

6

Thanks for the suggestion.

T

7

Looking at the flow...

Can you confirm that every HH:30 - you are pulling data from a web service.
with a date range?

  • Does this web service require UTC (or local time)?
  • What date format is required?

Your function node, I feel can be massively simplified (and may not be needed at all)

Currently trying to decipher what's actually happening here.

(Sorry its early for me)

8

there is no formal API available, and the pulling SQL is the result of "discovery mode". Do it manual and copy the URL, and work from there.
So, yes... looks like date range & UTC are necessary. At least... it works.
It also seems that the website has a certain delay before publishing... that is why I inject every hh:30... and the reminder in my comment-node
By now I have > 500 hourly records, landing perfect... but 1 missing/day... depending on the Inject-setting Between.

I realize the coding does not get a price of elegancy... all this is new to me.

Appreciating your "trying".
If the Forum was depicting your location, I could respect your "early" and post later.

Belgian greetz.

9

After changing the config of the EditInjectNode:
Repeat: Interval between times
Every: 30 minutes
Between: 00:00 and 00:00, and

observing the outcome for 15 days... all records seem to land.

Cheers,

T

2 Likes
10

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