Timestamp into the chart

[Ref_A]

Hello,

I am trying to figure out how to chart my DynamoDB values into the chart. Currently I am getting the data with timestamp from DynamoDB which I can view it in Node-red. Problem I get is getting the data into the chart. I tried doing some test with manually entering the data and found the issue.

Right now the timestamp I get from DynamoDb reads like Time: "2020-07-02T13:25:06.468-7:00". I can not plot this into the chart but if I change the timestamp into Time: "2020-07-02T13:25:06.468" then it works good.

Is there any way to remove the "-7:00" evertime I get data from DynamoDB.

Thank you

[zenofmud]

Well you could use the string split() function of Javascript in a function node, or you could use the $split() function in JSONata in a change or if it is always a -7:00 you could use the replace option in the change node. Or you could probably use node-red-contrib-string.

[Colin]

When it shows 13:25:06 -7 what is the actual local time and what time zone are you in (GMT-7?)

[Ref_A]

Local time is same as 13:25:06. I am in Pacific time zone

[Ref_A]

Its always same -7:00. I tired using the chnage option in change node but it did not get rid of it. I will try other options. Thanks

[zenofmud]

It is really simple, try this.

[{"id":"fa446d8f.76d148","type":"inject","z":"ccdf63.79a950a","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"2020-07-02T13:25:06.468-7:00","payloadType":"str","x":200,"y":120,"wires":[["14c9a896.bacf07","1c2fa9b3.5419b6"]]},{"id":"a341340a.618478","type":"debug","z":"ccdf63.79a950a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":610,"y":120,"wires":[]},{"id":"14c9a896.bacf07","type":"change","z":"ccdf63.79a950a","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"-7:00","fromt":"str","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":400,"y":120,"wires":[["a341340a.618478"]]},{"id":"1c2fa9b3.5419b6","type":"debug","z":"ccdf63.79a950a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":360,"y":180,"wires":[]}]

[Colin]

If you feed that into a chart node (without the -7) doesn't it assume it is GMT, and therefore show the wrong time on the chart?

[shrickus]

@Ref_A

Is there any way to remove the "-7:00" evertime I get data from DynamoDB.

A better option would be to fix that datetime string by changing your DynamoDB query to output a timestamp or UTC string. Since I don't know that database, I cannot tell you how to change the query, but most DBs have various formatting options for outputting dates...

So a slightly less desirable solution is to post-process the string, but KEEP the timezone offset, so you don't lose information. As you noted, the existing string with the -7:00 is NOT recognized by the Javascript Date parser -- however, -07:00 IS valid:

> new Date("2020-07-02T13:25:06.468-7:00")
Invalid Date
> new Date("2020-07-02T13:25:06.468-07:00")
2020-07-02T20:25:06.468Z

So you could use a regex to replace that missing leading 0, something like this:

> "2020-07-02T13:25:06.468-7:00".replace(/([+-])(\d):/, "$10$2:")
'2020-07-02T13:25:06.468-07:00'

and then format the JS Date object to your local timezone...

@Ref_A

Its always same -7:00

If your locale observes Daylight Savings, then part of the year it will show -8:00, most likely. So removing that information is not recommended. I like Colin's idea of feeding the chart node with the datetime string (no conversion to Date necessary) -- just make sure to add the leading 0 to the timezone offset first.

[Colin]

I am somewhat surprised that Dynamodb provides non-ISO format timestamps. Does the database know it is a timestamp or is it being written into the database as that string?

[Ref_A]

What's happening is that PLC is collecting the data with the timestamp and sending it to AWS iOT Core where it gets transferred into the DynamoDB. So the timestamp is already in the data and DynamoDB is just storing it as it is . From dynamoDB I wanted it to send to node red to plot the chart.

Chart shows the correct time even if I take the -7:00 out of the timestamp

[Ref_A]

Thank you. Yeah I did not think about the daylight saving time changing the time. I did as you mentioned by adding the .replace at the end. The result I get is "2020-07-02T13:25:06.468-07:00".

But when I add new Date in the front, it gives me the error message saying "TypeError: (intermediate value).replace is not a function"

Any Suggestions why would it give me this error

[Colin]

@Ref_A

"TypeError: (intermediate value).replace is not a function"

I presume that is in a function node. If it is then post the code of the function node (use the </> button in the forum) and show us the debug output of the message going in.

[Ref_A]

var i = 0;
var x = 0;
var myArray1 = [];


for (i = 0; i < msg.payload.ScannedCount; i++) {
    if (msg.payload.Items[i].ID.N == 0){
        myArray1[x] = {
           "Time":(msg.payload.Items[i].topic.M.Time.S).replace(/([+-])(\d):/, "$10$2:"),
           "Poly_Level": Number(msg.payload.Items[i].topic.M.Values.M.Poly_Level.N),
           
           
        }
     
       x = x+1;
    }
}

var chart = [{
    "series":["Level ="],
    "data":[myArray1],
    "labels":[""]
}];

msg.payload = chart;

return msg;

This is the function node that I use convert the time. If I dont put new Date in front of time than it gives me that error.

this is what goes into the function node

[Colin]

Earlier you said

@Ref_A

But when I add new Date in the front, it gives me the error message

Now you say

@Ref_A

If I dont put new Date in front of time than it gives me that error.

So I am confused. Does the function you posted give the error? If not then what does give the error?

[Ref_A]

Sorry mistyped it. If I add new Date it gives me the error. Above function does not give the error.

var i = 0;
var x = 0;
var myArray1 = [];


for (i = 0; i < msg.payload.ScannedCount; i++) {
    if (msg.payload.Items[i].ID.N == 0){
        myArray1[x] = {
           "Time":new Date(msg.payload.Items[i].topic.M.Time.S).replace(/([+-])(\d):/, "$10$2:"),
           "Poly_Level": Number(msg.payload.Items[i].topic.M.Values.M.Poly_Level.N),
           
           
        }
     
       x = x+1;
    }
}

var chart = [{
    "series":["Level ="],
    "data":[myArray1],
    "labels":[""]
}];

msg.payload = chart;

return msg;

This does give the error. Only difference is new Date infront of time

[Colin]

It should be

"Time":new Date(msg.payload.Items[i].topic.M.Time.S.replace(/([+-])(\d):/, "$10$2:")),

However, as you found the chart is ok with the string so you might as well just let it do the conversion for you. It is always good to know why something doesn't work though, so it is less likely to happen again.

[Ref_A]

Thank you Colin. It solved the issue with Time, now I can see -- Time: "2020-07-02T16:26:38.449Z"

But it still shows the chart like this

[Colin]

If you look at the docs for the chart node it shows the data should be formatted like this

[{
"series": ["A", "B", "C"],
"data": [
    [{ "x": 1504029632890, "y": 5 },
     { "x": 1504029636001, "y": 4 },
     { "x": 1504029638656, "y": 2 }
    ],
    [{ "x": 1504029633514, "y": 6 },
     { "x": 1504029636622, "y": 7 },
     { "x": 1504029639539, "y": 6 }
    ],
    [{ "x": 1504029634400, "y": 7 },
     { "x": 1504029637959, "y": 7 },
     { "x": 1504029640317, "y": 7 }
    ]
],
"labels": [""]
}]

In your data array you have the properties Time and Poly_Level instead of x and y.
If that doesn't work fix it then feed the output of the function into a debug node, and show us what it shows.

[Ref_A]

Thank you Colin