# State-Trail from database

**URL:** <https://discourse.nodered.org/t/state-trail-from-database/53164>\
**Category:** Dashboard\
**Created:** [3 November 2021 10:14 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164 "2021-11-03T10:14:24Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [3 November 2021 10:14 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/1 "2021-11-03T10:14:24Z")

</div>

Continuing the discussion from [State trail problem](https://discourse.nodered.org/t/state-trail-problem/22783/11):

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

---

<div class="post-metadata">

**Author:** ![hotNipi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hotnipi/32/383_2.png) [@hotNipi](https://discourse.nodered.org/u/hotNipi)\
**Post date:** [3 November 2021 12:17 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/2 "2021-11-03T12:17:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 08:10 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/3 "2021-11-08T08:10:20Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/8/b89aade63922e5f998fc4afdc4abfb15e0081702.png)

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/0/b065bdeb1fd5e060ef225ed425a86517d299756d.png)

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 08:15 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/4 "2021-11-08T08:15:17Z")

</div>

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

```

---

<div class="post-metadata">

**Author:** ![hotNipi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hotnipi/32/383_2.png) [@hotNipi](https://discourse.nodered.org/u/hotNipi)\
**Post date:** [8 November 2021 09:03 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/5 "2021-11-08T09:03:10Z")

</div>

You hit the bug. 🙂  
Fixed now. Version 0.4.2 available.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 09:37 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/6 "2021-11-08T09:37:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 11:48 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/7 "2021-11-08T11:48:26Z")

</div>

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/3/23071c3c7dfbb7dafdbc62b17fa8c2a5832a4404.png)

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/5/957631f0a4de051af28463f791793765ed7cb992.png)

if i click any portion of the trail, i get the following message  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/e/fea185222aa7116908e6a14379422626db81c44d.png)

---

<div class="post-metadata">

**Author:** ![hotNipi](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hotnipi/32/383_2.png) [@hotNipi](https://discourse.nodered.org/u/hotNipi)\
**Post date:** [8 November 2021 12:03 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/8 "2021-11-08T12:03:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 12:15 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/9 "2021-11-08T12:15:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 12:26 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/10 "2021-11-08T12:26:19Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/b/2bffa1f2d0819272358e6dbd4d88c3d574ea0970.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/8/c88624df1ad8230fd7c5c5ada037d5d4fdfc0786.png)

Thanks a Lot @hotNipi and Patrick

> <https://stackoverflow.com/questions/28563195/convert-date-to-milliseconds-in-mysql/30897930#30897930>

---

<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:** [8 November 2021 12:35 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/11 "2021-11-08T12:35:34Z")

</div>

> [@smanjunath211](#):
>
> i can to it in Jsonata using $frommillis but this is a query output in the form of an array

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

```auto
$$.payload.$merge(
   [
       $,
       {"timestamp":$fromMillis($.timestamp, "[Y]-[M]-[D] [H]:[m]:[s]")}
   ]
)

```

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

```

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 November 2021 12:51 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/12 "2021-11-08T12:51:55Z")

</div>

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

---

<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:** [22 November 2021 12:52 UTC](https://discourse.nodered.org/t/state-trail-from-database/53164/13 "2021-11-22T12:52:44Z")

</div>

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