Hello, I have a query that calculates the time between the last entry and the current time. The query gets executed every 30 seconds.
This is the result im getting from the database. So far so good. Now i want to use that value in the msg.payload.SELECT TIMEDIFF (NOW(), (SELECT timestamp FROM algemeen WHERE machine='N-4-7' ORDER BY id DESC LIMIT 1));. With that value i want to check if the difference is greater than 30min. If that's true i want to send an email, i am able to send an email already..
this is what my flow looks like
Your email wont send because there is no wire connected it to fire it off.
yes i know. i did that on purpose because the whole calculating section is still missing because i don't know how i can proces the message.
Ah, my apologies, the column name is the query - most odd.
Then you need to use bracket notation to access the value.
var value = msg.payload[0]["name of field"];
Better still - use the provided tools...
There’s a great page in the docs that will explain how to use the debug panel to find the right path to any data item.
Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.
https://nodered.org/docs/user-guide/messages
PS: you would fair better providing a reasonable column name for the query.
I assume the difficulty you are having is the property name returned by the db query.
Try adding AS to the query to get a usable property name
SELECT TIMEDIFF (NOW(), (SELECT timestamp FROM algemeen WHERE machine='N-4-7' ORDER BY id DESC LIMIT 1)) AS time_dif
Then you will get the property returned as msg.payload[0].time_dif
Which you can access easier to do your comparison that it is greater than 30mins.
[edit] Here is one way to check if the returned value is greater than 30mins. It uses JSONata in the switch node to calculate the minutes.
[{"id":"3632a93e.af1496","type":"inject","z":"c791cbc0.84f648","name":"","props":[{"p":"payload[0].time_dif","v":"01:09:53","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":100,"y":640,"wires":[["d154dcbb.c35f8"]]},{"id":"d154dcbb.c35f8","type":"switch","z":"c791cbc0.84f648","name":"","property":"($parts := $split($$.payload[0].time_dif,\":\").$number($);\t$parts[0] * 60 + $parts[1])","propertyType":"jsonata","rules":[{"t":"gt","v":"30","vt":"num"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":250,"y":640,"wires":[["328b8b0a.3018c4"],["698c8cce.fa5fe4"]]},{"id":"328b8b0a.3018c4","type":"debug","z":"c791cbc0.84f648","name":"greater","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":430,"y":620,"wires":[]},{"id":"698c8cce.fa5fe4","type":"debug","z":"c791cbc0.84f648","name":"less","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":420,"y":656,"wires":[]}]
The JSONata expression
(
$parts := $split($$.payload[0].time_dif,":").$number($);
$parts[0] * 60 + $parts[1]
)
2 Likes
oh that is a good idea. I hadn't even thought of that yet.
yep that worked beautifuly.
The JSON part isn't working yet. This is what my flow looks like now:
and this is what I wrote in the switch node:
($parts := $split($$.payload[0].time_diff,":").$number($); $parts[0] * 60 + $parts[1])
[edit]
i was looking at the wrong section in the debug screen. it is working
I've got a question. This question still applies to my previous questions.
Because the value doesn't change all that often the chances that a mail is being sent every 30 seconds is quite high. How can i make it so that the query is still being executed every 30 seconds but that the mail only gets sent once?
Try setting a pass variable with the jsonata expression in a change node. Then use filter/rbe node to only let the msg pass if pass var changes
e.g.
[{"id":"3632a93e.af1496","type":"inject","z":"c791cbc0.84f648","name":"greater than 30","props":[{"p":"payload[0].time_dif","v":"01:09:53","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":122,"y":579.000036239624,"wires":[["8b5b204c.ca68b"]]},{"id":"8b5b204c.ca68b","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"pass","pt":"msg","to":"(\t $parts := $split($$.payload[0].time_dif,\":\").$number($);\t ($parts[0] * 60 + $parts[1]) > 30\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":262,"y":639.000036239624,"wires":[["ae1b7a27.ea63d8"]]},{"id":"ae1b7a27.ea63d8","type":"rbe","z":"c791cbc0.84f648","name":"","func":"rbe","gap":"","start":"","inout":"out","septopics":true,"property":"pass","x":412,"y":639.000036239624,"wires":[["d154dcbb.c35f8"]]},{"id":"75495fca.b5a0a","type":"inject","z":"c791cbc0.84f648","name":"less than 30","props":[{"p":"payload[0].time_dif","v":"00:09:53","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":112,"y":639.000036239624,"wires":[["8b5b204c.ca68b"]]},{"id":"d154dcbb.c35f8","type":"switch","z":"c791cbc0.84f648","name":"","property":"pass","propertyType":"msg","rules":[{"t":"true"},{"t":"else"}],"checkall":"false","repair":false,"outputs":2,"x":552,"y":639.000036239624,"wires":[["328b8b0a.3018c4"],["698c8cce.fa5fe4"]]},{"id":"328b8b0a.3018c4","type":"debug","z":"c791cbc0.84f648","name":"greater","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":702,"y":619.000036239624,"wires":[]},{"id":"698c8cce.fa5fe4","type":"debug","z":"c791cbc0.84f648","name":"less","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":712,"y":659.000036239624,"wires":[]}]
The expression has changed a little to output a true or false.
(
$parts := $split($$.payload[0].time_dif,":").$number($);
($parts[0] * 60 + $parts[1]) > 30
)
2 Likes
wow. amazing. Thank you so much!
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
