How to manipulate query result

[Mike Arney]

I have a query that adds the total time when payload = 1.

SQL Result =  00:01:21.0000000

Node RED Result in msg.payload =

"1970-01-01T00:14:10.000Z"

How can I remove "1970-01-01T" from the result?

Thanks,

Mike

[Nick O'Leary]

HI Mike,

that suggests msg.payload is a String - so you just need to do a bit of JavaScript string manipulation in a Function node. Lots of examples online on how to work with Strings in JavaScript.

One of many options, given you know the year prefix will always be the same length is:

msg.payload = msg.payload.substring(11);

return msg;

Nick

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/c5604e09-7a9b-4899-afe3-21f009e47ff2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Colin Law]

Are you sure the query is returning a string and not a timestamp?

Colin

On 12 October 2017 at 19:44, Mike Arney <mikespc...@gmail.com> wrote:

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to node-red+u...@googlegroups.com.

[steve rickus]

Mike, you are returning that elapsed time (without any date information) as a sql datetime string, which is messy and can be shifted by the timezone offset. If you want to make it more bullet-proof, you should return a number representing some units of time (probably milliseconds). Then, in the payload there is no ambiguity or parsing -- just formatting to a time string. The node-red-contrib-moment node is really good for this -- just pass in the millis in the payload and set the output format to be just the time portion of the datetime.

Of course, you can just use a function node as well -- something like this should create the format you need:

var num = +msg.payload || 0;  // coerce payload to millis (or 0)
var dtm = new Date(+msg.payload);  // create a date object
var iso = dtm.toISOString();  // ISO formatted UTC datetime
var tot = iso.replace(/^.*T(.*)Z$/, "$1"); // extract the time portion

How you tell mssql to return the total time as millis is a separate issue, but I don't have time to look that up right now. I would try without it first...

--

Steve

[Mike Arney]

Got it! I redesigned my query to output milliseconds and converted it with a change node. 

Thanks guys!