[Solved] How to change timestamps in objects to date/time for ui_table

zenofmud · 13 June 2020 19:15

I'm sending an array of objects to a ui_table and that works fine. One of the properties - in each of the objects - is a timestamp that I want to convert to a human readable format. I've been trying to use jsonata to see if I could change it (and have failed).

Any suggestions? Attached is an inject -> change -> debug node flow with the data in the inject node if anyone wants to play with it. The change node currently doesn't have anything in it.

Here's what the data looks like:

[
  {
    "last_action" : "msg",
    "last_seen" : 1592070983674,
    "location" : "testing",
    "mqtt_mode" : "local",
    "node_id" : "node28",
    "node_status" : "sleep",
    "sleep_time" : 0
  },
  {
    "last_action" : "msg",
    "last_seen" : 1592070444879,
    "location" : "greenhouse",
    "mqtt_mode" : "local",
    "node_id" : "node42",
    "node_status" : "sleep",
    "sleep_time" : 0
  },
  {
    "last_action" : "msg",
    "last_seen" : 1592070734128,
    "location" : "basement",
    "mqtt_mode" : "local",
    "node_id" : "node44",
    "node_status" : "sleep",
    "sleep_time" : 0
  }
]

and here is the flow

[{"id":"4210db69.6dfdcc","type":"change","z":"3822972f.7f2b38","name":"","rules":[],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":420,"wires":[["b8dac0.466ded4"]]},{"id":"eb354e69.2bf12","type":"inject","z":"3822972f.7f2b38","name":"Data in Payload","topic":"","payload":"[{\"last_action\":\"msg\",\"last_seen\":1592070983674,\"location\":\"testing\",\"mqtt_mode\":\"local\",\"node_id\":\"node28\",\"node_status\":\"sleep\",\"sleep_time\":0},{\"last_action\":\"msg\",\"last_seen\":1592070444879,\"location\":\"greenhouse\",\"mqtt_mode\":\"local\",\"node_id\":\"node42\",\"node_status\":\"sleep\",\"sleep_time\":0},{\"last_action\":\"msg\",\"last_seen\":1592070734128,\"location\":\"basement\",\"mqtt_mode\":\"local\",\"node_id\":\"node44\",\"node_status\":\"sleep\",\"sleep_time\":0}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":420,"wires":[["4210db69.6dfdcc"]]},{"id":"b8dac0.466ded4","type":"ui_table","z":"3822972f.7f2b38","group":"ba6fbc1c.dd7588","name":"table","order":2,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":530,"y":420,"wires":[]},{"id":"ba6fbc1c.dd7588","type":"ui_group","z":"","name":"Device Status","tab":"5208bb69.642964","order":1,"disp":true,"width":"12","collapse":false},{"id":"5208bb69.642964","type":"ui_tab","z":"","name":"Status table","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Andrei · 13 June 2020 21:22

Hi Paul, try this:

1- Add an ui_template (head session) with below config:

<script src= "https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.26.0/moment.min.js" type="text/javascript"></script>

2- Add a formatter to the column that has the timestamp

    {
        "title": "LastSeen",
        "field": "lastSeen",
        "width": 150,
        "align": "left",
        "formatter": "function(cell, formatterParams, onRendered) {return moment(cell.getValue()).format('DD-MM-YYYY HH:mm');}"
    }

Christian-Me · 13 June 2020 21:28

Hi,

I would let ui-table (tabulator) do the formatting. This would ensure that sorting, filtering and all other features work as expected. Moment.js features are build into the dashboard (at least in my installations).
You can use msg.ui_control to activated the build in datetime formatter:

{
    "tabulator": {
        "columns": [
            {
                "formatter": "datetime",
                "formatterParams": {
                    "inputFormat": "x",
                    "outputFormat": "DD. MMMM YYYY hh:mm:ss",
                    "invalidPlaceholder": "(invalid date)"
                },
                "title": "Last Seen",
                "field": "last_seen"
            }
        ]
    }
}

gives you this

[{"id":"d29f47f1.983ac8","type":"inject","z":"bfc08422.a451e8","name":"Data in Payload","topic":"","payload":"[{\"last_action\":\"msg\",\"last_seen\":1592070983674,\"location\":\"testing\",\"mqtt_mode\":\"local\",\"node_id\":\"node28\",\"node_status\":\"sleep\",\"sleep_time\":0},{\"last_action\":\"msg\",\"last_seen\":1592070444879,\"location\":\"greenhouse\",\"mqtt_mode\":\"local\",\"node_id\":\"node42\",\"node_status\":\"sleep\",\"sleep_time\":0},{\"last_action\":\"msg\",\"last_seen\":1592070734128,\"location\":\"basement\",\"mqtt_mode\":\"local\",\"node_id\":\"node44\",\"node_status\":\"sleep\",\"sleep_time\":0}]","payloadType":"json","repeat":"","crontab":"","once":true,"onceDelay":0.1,"x":285,"y":221,"wires":[["d54c7a85.46e848"]]},{"id":"dc0c2b3c.bd0a88","type":"ui_table","z":"bfc08422.a451e8","group":"68300045.dcdbf","name":"table","order":2,"width":"12","height":"7","columns":[],"outputs":1,"cts":true,"x":675,"y":221,"wires":[["c6384aa1.cbe018"]]},{"id":"d54c7a85.46e848","type":"change","z":"bfc08422.a451e8","name":"ui_control","rules":[{"t":"set","p":"ui_control","pt":"msg","to":"{\"tabulator\":{\"columns\":[{\"formatter\":\"datetime\",\"formatterParams\":{\"inputFormat\":\"x\",\"outputFormat\":\"DD. MMMM YYYY hh:mm:ss\",\"invalidPlaceholder\":\"(invalid date)\"},\"title\":\"Last Seen\",\"field\":\"last_seen\"},{\"title\":\"Action\",\"field\":\"last_action\"},{\"title\":\"Location\",\"field\":\"location\"},{\"title\":\"MQTT node\",\"field\":\"mqtt_mode\"},{\"title\":\"Id\",\"field\":\"node_id\"},{\"title\":\"Status\",\"field\":\"node_status\"},{\"title\":\"sleep\",\"field\":\"sleep_time\"}]}}","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":502,"y":221,"wires":[["dc0c2b3c.bd0a88"]]},{"id":"c6384aa1.cbe018","type":"debug","z":"bfc08422.a451e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":832,"y":221,"wires":[]},{"id":"68300045.dcdbf","type":"ui_group","z":"","name":"Device Status","tab":"baf5f94f.3685c8","order":1,"disp":true,"width":"12","collapse":false},{"id":"baf5f94f.3685c8","type":"ui_tab","z":"","name":"Status table","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

If you format one column you should format all other columns too. (More examples for using ui_control are in the examples including custom formatters) and more about tabulator formatters and other features you find here:

Andrei · 13 June 2020 21:36

Nice!! @Christian-Me, I did not know that "inputFormat": "x", could be used to match the timestamp (that is why I was using custom formatter). It worked fine for me. Thanks.

Christian-Me · 13 June 2020 21:42

It took me some time reading the moment.js docs ... so I wrote my custom formatters too

~~https://momentjs.com/docs/#/parsing/string-format/~~

comprehensive moment.js formats table here:

https://momentjs.com/docs/#/displaying/

dceejay · 13 June 2020 21:46

I found the table here easier to use - https://momentjs.com/docs/#/displaying/

Andrei · 13 June 2020 21:48

Indeed, that is a comprehensive table. Bookmarked. Thanks !

Christian-Me · 13 June 2020 21:49

Great - will bookmark it, I was looking for a "complete" table for a long time ...

zenofmud · 13 June 2020 22:50

Brilliant! Thank you and I'm bookmarking this too!

zenofmud · 13 June 2020 23:09

Ohhh I just found "layout": "fitData", which automatically fits the column to the width of the data. Without it the table looks like this:
Screen Shot 2020-06-13 at 7.07.53 PM
with it the table looks like this
Screen Shot 2020-06-13 at 7.08.10 PM

{
    "tabulator": {
        "layout": "fitData",  <=== I added this line
        "columns": [
            {
                "formatter": "datetime",
                "formatterParams": {
                    "inputFormat": "x",
                    "outputFormat": "MM/DD/YY hh:mm:ss",
                    "invalidPlaceholder": "(invalid date)"
                },
                "title": "Last Seen",
                "field": "last_seen"
            },
            {
                "title": "Action",
                "field": "last_action"
            },
            {
                "title": "Location",
                "field": "location"
            },
            {
                "title": "MQTT node",
                "field": "mqtt_mode"
            },
            {
                "title": "Id",
                "field": "node_id"
            },
            {
                "title": "Status",
                "field": "node_status"
            },
            {
                "title": "sleep",
                "field": "sleep_time"
            }
        ]
    }
}

zenofmud · 13 June 2020 23:22

One more thing, the template node is even easier to use than the change node. Just set it to Output to Parsed JSON

Christian-Me · 14 June 2020 10:35

Tabulator has so many cool features and a very good documentation! All features should be available through ui-table.

zenofmud · 28 June 2020 10:35

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

Topic		Replies	Views
Round down Timestamps to minutes General function-node	5	359	15 July 2022
Puzzling JSONATA problem in Change Node General	2	205	13 January 2023
Extract data from a JSON structure to be transferred into the table-ui General	6	453	12 December 2020
Timestamp conversion going wrong General	7	1596	20 April 2021
Label of a table with data from the influxdb database General	5	230	8 January 2023

[Solved] How to change timestamps in objects to date/time for ui_table

Related topics