How to convert a number that is 0 to a decimal 0.0?

Hi,

I have looked at many posts on here but cannot find the answer.

I have a counter that resets to a number 0 at midnight.
But my graph requires it as decimal 0.0

How do i convert and keep it a as a decimal number 0.0 ?

Thanks

In JavaScript there is no difference. If what you have doesn't work then are you sure that what you have is not a string "0". Feed it into debug node and see what it says.

Hi Colin,

I did look at many of your answers on here , before posting :smiley:

The number is definitely a number as it is sent from Node-RED to my database and it shows it as a number 0 in my database.

The problem is I use Grafana for my chart and unless the number is 0.0
I get no graph.

If I manually change the database number to 0.0 the graph works perfectly.

So I need to make Node-RED send it as a decimal 0.0

Node-red sends all number as decimal numbers perfectly to my database, except 0.0 ?

Thanks

What database type is it and what type is the field containing the value?

Show us the result.

Here is a test flow ...

[
    {
        "id": "824b0d3e2647818d",
        "type": "inject",
        "z": "9ae732bd.ba304",
        "name": "",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "0.00",
        "payloadType": "num",
        "x": 340,
        "y": 410,
        "wires": [
            [
                "e53c35c601d6c4d8"
            ]
        ]
    },
    {
        "id": "4563c529d740275f",
        "type": "debug",
        "z": "9ae732bd.ba304",
        "name": "MSG 02",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 760,
        "y": 470,
        "wires": []
    },
    {
        "id": "68fed087146b000e",
        "type": "inject",
        "z": "9ae732bd.ba304",
        "name": "For testing ... reset to 0",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "0",
        "payloadType": "str",
        "x": 390,
        "y": 470,
        "wires": [
            [
                "ba818ad66ae3cdcb",
                "810b350e78143503"
            ]
        ]
    },
    {
        "id": "ba818ad66ae3cdcb",
        "type": "function",
        "z": "9ae732bd.ba304",
        "name": "Str > Num",
        "func": "msg.payload = Number(msg.payload);\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 610,
        "y": 470,
        "wires": [
            [
                "4563c529d740275f"
            ]
        ]
    },
    {
        "id": "810b350e78143503",
        "type": "debug",
        "z": "9ae732bd.ba304",
        "name": "MSG - 03",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 600,
        "y": 530,
        "wires": []
    },
    {
        "id": "e53c35c601d6c4d8",
        "type": "debug",
        "z": "9ae732bd.ba304",
        "name": "MSG 01",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 600,
        "y": 410,
        "wires": []
    }
]

and this is how it looks in the SQLite JSON database

31

and my setup

Thanks

Although you have not posted the picture requested, your flow does have this:


Which strongly suggests that you are dealing with "0" not 0.

Hi,

Yes i created this 'meter reading' years ago and i have it set as a string variable that is Global, that's why I convert from string to number.

OK I understand if it's emulating an historical data point.
Yes it comes out in debug as a number.

Sorry I don't have SQLite but if I was defining a Mariadb schema I would have discrete fields like this.

create table `foobar` (
`iso` datetime not null,  
`cost` float,  
`location` varchar(32),  
`meter_reading` float, 
primary key (`iso`)
 );

Your schema looks like everything is packed into a text field? I have no idea of the effect of that.

Hi

Yes I agree it is text SQLite is limited,I think I will ask on Grafana forum.
To be honest I would expect there chart to accept 0 as a feasible number for a chart, I just thought it would be easier to change in Node-RED
:pensive:

Not Node-RED's fault, maybe I am doing something wrong in Grafana, it's all new to me, but fun to see all my other graphs working.

Thanks for your help.

Show us what is actually in the database for the 0 and 0.0 case, by running a query on the database to show it.

Hi Colin,

I have limited knowledge with SQLite so I only know how to do this in DB Browser for SQLite, hope this is what you need ?

What does it look like when there are numbers?

What query are you running in Grafana?
What happens in Grafana if you have some numbers and some 0 in the same window?

select 	json_extract ( DATA,'$.meter_reading' ) as meter_reading,
		json_extract ( DATA,'$.iso') 			as time
from 	Energy
where 	DEVICE = 'IP15' ;

If my dataset starts with 0 i get a line on chart and everything is at 0

If I do manually change in the database to 0.0 all works.

Workaround for Grafana not displaying a graph if it gets 0 at start.
( It will work with 0.0 if I change it manually in database ? ).

I think it needs a float number for reference at the start of reading from SQLite JSON database.
as SQLite using JSON stores values in a text field.

I do have a workaround for it, but I don't like it, but I think it will work

msg.payload = Number(msg.payload);
msg.payload = msg.payload + .001; 
return msg;

Why does the second one have a different title? Also why does the second one not have a left axis?

The 1st one is my test bed dashboard for fault finding.

The 2nd is my actual working Dashboard with the 0.0 added into my database manually as the very 1st entry.

Visually they are slightly different but underneath they are the same.