State-Trail from database

Continuing the discussion from State trail problem:

can you please post how did you change string to array ?

In case you have similar string - it is most probably valid JSON string so use the JSON node. It does the magic with default configuration.

the output of debug seems to show array as expected, but getting error in State-Trail.

what am i doing wrong. ?

i have copied the sample straight from your example as below
image

[
    {
        "id": "69a83901969741f7",
        "type": "tab",
        "label": "Flow 2",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "8e28907718bd747a",
        "type": "inject",
        "z": "69a83901969741f7",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 340,
        "y": 240,
        "wires": [
            [
                "5e40faae54cd772d"
            ]
        ]
    },
    {
        "id": "5e40faae54cd772d",
        "type": "function",
        "z": "69a83901969741f7",
        "name": "",
        "func": "msg.payload = [{ state: true, timestamp: 1579362774639 }, { state: false, timestamp: 1579362795665 }, { state: true, timestamp: 1579362895432 }]\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 510,
        "y": 240,
        "wires": [
            [
                "dc2f71f1641ac2e6",
                "1fefc2755b50c2f5"
            ]
        ]
    },
    {
        "id": "dc2f71f1641ac2e6",
        "type": "debug",
        "z": "69a83901969741f7",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 720,
        "y": 180,
        "wires": []
    },
    {
        "id": "1fefc2755b50c2f5",
        "type": "ui_statetrail",
        "z": "69a83901969741f7",
        "group": "01bcb7cbe5728a58",
        "order": 1,
        "width": "58",
        "height": "4",
        "name": "",
        "label": "TIMELINE",
        "states": [
            {
                "state": true,
                "col": "#009933",
                "t": "bool",
                "label": ""
            },
            {
                "state": false,
                "col": "#ec0404",
                "t": "bool",
                "label": ""
            }
        ],
        "periodLimit": "24",
        "periodLimitUnit": "3600",
        "timeformat": "HH:mm",
        "tickmarks": "24",
        "persist": false,
        "legend": 1,
        "combine": true,
        "blanklabel": "",
        "x": 730,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "01bcb7cbe5728a58",
        "type": "ui_group",
        "name": "DETAILS",
        "tab": "fd1b6ea7b13a1e71",
        "order": 21,
        "disp": false,
        "width": "58",
        "collapse": false,
        "className": ""
    },
    {
        "id": "fd1b6ea7b13a1e71",
        "type": "ui_tab",
        "name": "HISTORY",
        "icon": "dashboard",
        "order": 2,
        "disabled": false,
        "hidden": false
    }
]

You hit the bug. :slight_smile:
Fixed now. Version 0.4.2 available.

Working Now...
But just got it right for static input, i am struggling to get it from database with an SQL Query,
will keep working, till i get it right.

Thanks a Lot.

I was finally able to get the SQL Query to yield array like this

image

and I changed the states of state_trail to accept string 'TRUE' and 'FALSE'

it did plot the trail, but I believe since timestamp is also string type (?) i am not getting the ticks at the bottom. how to solve this ? i think i see Nan at the bottom of first tick

if i click any portion of the trail, i get the following message
image

The timestamp must be given in milliseconds (number type).

1 Like

hmmm. need help from a SQL query perspective, i dont know how to convert a timestamp in timezone format to milliseconds. i can do it in Jsonata using $frommillis but this is a query output in the form of an array.

Internet is such a wonderful place, you just need to know how to SEARCH

I got the required syntax with a simple search in google.

Select if(M01>0,'TRUE','FALSE') AS state, (UNIX_TIMESTAMP(datetime)*1000) as timestamp FROM fffpl.rawdata order by sort desc limit 480;

now i get required output and upon clicking trail , i get desired result..

image

Thanks a Lot @hotNipi and Patrick

you can use . to map the array and apply the JSONata $fromMillis() to every element of the array
e.g.

$$.payload.$merge(
   [
       $,
       {"timestamp":$fromMillis($.timestamp, "[Y]-[M]-[D] [H]:[m]:[s]")}
   ]
)
[{"id":"deda35b5.6a984","type":"inject","z":"7f59364f045fd16d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"timestamp\":2636373989654,\"test\":1},{\"timestamp\":1636383989654,\"test\":2},{\"timestamp\":1636393989654,\"test\":3}]","payloadType":"json","x":140,"y":560,"wires":[["71c1b7c2.34c818"]]},{"id":"71c1b7c2.34c818","type":"change","z":"7f59364f045fd16d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.$merge(\t   [\t       $,\t       {\"timestamp\":$fromMillis($.timestamp, \"[Y]-[M]-[D] [H]:[m]:[s]\")}\t   ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":580,"wires":[["62e30f3.ab3317"]]},{"id":"62e30f3.ab3317","type":"debug","z":"7f59364f045fd16d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":440,"y":520,"wires":[]}]

That's amazing, will sure try it out and get back. :grinning:

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