# SQLite and localtime operations

**URL:** <https://discourse.nodered.org/t/sqlite-and-localtime-operations/25396>\
**Category:** General\
**Created:** [24 April 2020 05:46 UTC](https://discourse.nodered.org/t/sqlite-and-localtime-operations/25396 "2020-04-24T05:46:12Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![whatsupskip](https://avatars.discourse-cdn.com/v4/letter/w/5f9b8f/32.png) [@whatsupskip](https://discourse.nodered.org/u/whatsupskip)\
**Post date:** [24 April 2020 05:46 UTC](https://discourse.nodered.org/t/sqlite-and-localtime-operations/25396/1 "2020-04-24T05:46:12Z")

</div>

I have been trying to modify Pete Scargill's [Track Broadband Outage in Node Red](https://tech.scargill.net/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.

---

<div class="post-metadata">

**Author:** ![whatsupskip](https://avatars.discourse-cdn.com/v4/letter/w/5f9b8f/32.png) [@whatsupskip](https://discourse.nodered.org/u/whatsupskip)\
**Post date:** [25 April 2020 00:21 UTC](https://discourse.nodered.org/t/sqlite-and-localtime-operations/25396/2 "2020-04-25T00:21:25Z")

</div>

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".

---

<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:** [9 May 2020 00:21 UTC](https://discourse.nodered.org/t/sqlite-and-localtime-operations/25396/3 "2020-05-09T00:21:29Z")

</div>

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