# Calculating with a value from a database

**URL:** <https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514>\
**Category:** General\
**Tags:** database\
**Created:** [12 January 2022 10:21 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514 "2022-01-12T10:21:14Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [12 January 2022 10:21 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/1 "2022-01-12T10:21:14Z")

</div>

Hello, I have a query that calculates the time between the last entry and the current time. The query gets executed every 30 seconds.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/d/dd1a44a6e4dd25576a3ccebd06bead755398edd1.png)  
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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/6/165c3be53156d1598e2c5c87ed46b92954470813.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 January 2022 10:29 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/2 "2022-01-12T10:29:26Z")

</div>

> [@evanic14](#):
>
> If that's true i want to send an email, i am able to send an email already

Your email wont send because there is no wire connected it to fire it off.

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [12 January 2022 10:30 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/3 "2022-01-12T10:30:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 January 2022 10:34 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/4 "2022-01-12T10:34:25Z")

</div>

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.

![BX00Cy7yHi](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd1f333a9e043b061b39917c328b0094d3e52d30.gif)

> **[Working with messages : Node-RED](https://nodered.org/docs/user-guide/messages)**

* * *

### PS: you would fair better providing a reasonable column name for the query.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [12 January 2022 10:34 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/5 "2022-01-12T10:34:27Z")

</div>

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

```auto
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.

```auto
[{"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

```auto
(
   $parts := $split($$.payload[0].time_dif,":").$number($);
   $parts[0] * 60 + $parts[1]
)

```

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [12 January 2022 11:02 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/6 "2022-01-12T11:02:16Z")

</div>

oh that is a good idea. I hadn't even thought of that yet.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/b/1baf86c3e48cc9998a9efe782d8fb56efb90a4d5.png)  
yep that worked beautifuly.

The JSON part isn't working yet. This is what my flow looks like now:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/5/5529d17d16d3011bd27f6c2b95a2163120ceb1e6.png)  
and this is what I wrote in the switch node:

```json
($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

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [12 January 2022 12:11 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/7 "2022-01-12T12:11:59Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [12 January 2022 12:18 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/8 "2022-01-12T12:18:31Z")

</div>

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.

```auto
[{"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.

```auto
(
   $parts := $split($$.payload[0].time_dif,":").$number($);
   ($parts[0] * 60 + $parts[1]) > 30
)

```

---

<div class="post-metadata">

**Author:** ![evanic14](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/evanic14/32/23480_2.png) [@evanic14](https://discourse.nodered.org/u/evanic14)\
**Post date:** [12 January 2022 12:23 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/9 "2022-01-12T12:23:52Z")

</div>

wow. amazing. Thank you so much!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [26 January 2022 12:24 UTC](https://discourse.nodered.org/t/calculating-with-a-value-from-a-database/56514/10 "2022-01-26T12:24:22Z")

</div>

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