SQLite and localtime operations

I have been trying to modify Pete Scargill's Track Broadband Outage in Node Red to report the results in my local time (zone), but I keep hitting a brick wall.

Primarily the problem appears to be in the section that generates the email

var a=msg.payload;
msg.payload=””;

if (a.length!==0)
{
for (var i in a)
{
msg.payload+=”\r\n”+ ((a[i].ping !== 0) ? “Regained” : “Lost”) +” connection ” + a[i].thetime;
}
return(msg);
}

I tried a few different Javascript calls to try to change "thetime" to local time, but they didn't work.

I was able to create a SQLite query to return the local time, but then I couldn't match this up with above code. This is the query that worked as a plan query.

SELECT datetime(thetime,'localtime'), ping FROM pings ORDER BY thetime DESC LIMIT 0, 30

However this was generating the result:
datetime(thetime,'localtime'): "2020-04-22 14:41:40"
thetime: "2020-04-22 14:41:40"

Any suggestions would be appreciated.

Ok I found a solution to this problem:

SELECT datetime(thetime,'localtime'), ping FROM pings ORDER BY thetime DESC LIMIT 0, 30

Change this to

SELECT datetime(thetime,'localtime') AS brisbanetime, ping FROM pings ORDER BY thetime DESC LIMIT 0, 30

Then in the generated result it will be brisbanetime: "2020-04-22 14:41:40"

Then in the Build Email String, just change "thetime" to "brisbanetime".

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