# Data get from SQLite

**URL:** <https://discourse.nodered.org/t/data-get-from-sqlite/59095>\
**Category:** General\
**Created:** [28 February 2022 08:51 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095 "2022-02-28T08:51:54Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 08:51 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/1 "2022-02-28T08:51:54Z")

</div>

Hello everybody I have a problem and hope you are help to me like before.  
Im saving data in Sqlite db.And I have to visualize this data in dashboard. Is it possible visualize this times, as you see in photo. high/low pin situation time calculating.  
For example : 28.02.2022 : Total : 56 second with chart graphic ?  
If yes please help to me.

![test](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/2/129ffff94aefda01e1d72160aa8ab509c87cd0bc.png)

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [28 February 2022 10:21 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/2 "2022-02-28T10:21:46Z")

</div>

are you able to get the result of above query through Node-Red sqlite node ?  
then you can store the values into flow.context and use the below function.

```auto
[{"id":"1d59c3b7653a0200","type":"function","z":"f0c78620b090a94c","name":"Time-Diff","func":"var one = flow.get('one1') || 0;\nvar zero = flow.get('zero0') || 0;\nvar diffMS=zero-one;\nvar diffm = diffMS/60000;\nmsg.payload1 = Math.round(diffm);\nreturn msg","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1000,"y":120,"wires":[["116d5d9ecffa4b1c"]]}]

```

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 10:25 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/3 "2022-02-28T10:25:54Z")

</div>

Dear friend. I have used sqlite node, but didnt know the codes in function node.  
Datas are saving data base, Im using sqlite node, but I need codes for getting this data from database, just total pin high (1) situation time.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 10:28 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/4 "2022-02-28T10:28:30Z")

</div>

My node is like this

```auto
[{"id":"5bc4fcf5.332164","type":"subflow","name":"Subflow 1","info":"","category":"","in":[{"x":60,"y":80,"wires":[{"id":"2c29ca9d.1f3726"}]}],"out":[{"x":920,"y":100,"wires":[{"id":"26fe58d.da0d2a8","port":0}]},{"x":540,"y":120,"wires":[{"id":"c3cccc67.eba87","port":0}]}]},{"id":"2c29ca9d.1f3726","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":80,"wires":[["c3cccc67.eba87","dc32c467.fb1168"]]},{"id":"dc32c467.fb1168","type":"switch","z":"5bc4fcf5.332164","name":"allow only 1 ","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":390,"y":80,"wires":[["90e7351e.a17e68"],["54e68c10.45d2f4"]]},{"id":"c3cccc67.eba87","type":"trigger","z":"5bc4fcf5.332164","op1":"","op2":"Kapı 30 saniyedir açık!!","op1type":"nul","op2type":"str","duration":"30","extend":false,"units":"s","reset":"1","bytopic":"all","name":"","x":390,"y":120,"wires":[[]]},{"id":"90e7351e.a17e68","type":"change","z":"5bc4fcf5.332164","name":"High","rules":[{"t":"set","p":"bc","pt":"msg","to":"orange","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Kapalı","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":80,"wires":[["26fe58d.da0d2a8"]]},{"id":"54e68c10.45d2f4","type":"change","z":"5bc4fcf5.332164","name":"Low","rules":[{"t":"set","p":"bc","pt":"msg","to":"purple","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Açık","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":120,"wires":[["26fe58d.da0d2a8"]]},{"id":"26fe58d.da0d2a8","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":100,"wires":[[]]},{"id":"3c7c2323.4c861c","type":"debug","z":"978d4790.f6ab88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1130,"y":300,"wires":[]},{"id":"c480b490.309628","type":"ui_chart","z":"978d4790.f6ab88","name":"","group":"e78dad9d.fa084","order":4,"width":"18","height":"10","label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1150,"y":360,"wires":[[]]},{"id":"583b4d14.7fce94","type":"sqlite","z":"978d4790.f6ab88","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"ip_cekili","x":780,"y":360,"wires":[["8eab3557.2a9e78"]]},{"id":"4578ba13.421184","type":"ui_button","z":"978d4790.f6ab88","name":"","group":"c55d32a2.85a79","order":9,"width":"3","height":"1","passthru":false,"label":"B5 Kapı - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":570,"y":320,"wires":[[]]},{"id":"3581ace4.658574","type":"dsm","z":"978d4790.f6ab88","name":"sensör_durumu","sm_config":"{\n \"triggerInput\": \"payload\",\n \"currentState\": \"running\",\n \"states\": {\n \"stopped\": {\n \"0\": \"running\"\n },\n \"running\": {\n \"1\": \"stopped\"\n }\n },\n \"methods\": {\n \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n \"1\": \"sta.fill='green';msg.background='red';msg.label='S';\",\n \"0\": \"sta.fill='red';msg.background='green';msg.label='S';\"\n }\n}","x":370,"y":380,"wires":[["bb9b7d97.090aa"]]},{"id":"bb9b7d97.090aa","type":"template","z":"978d4790.f6ab88","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{payload}},{{pyload}});","output":"str","x":560,"y":400,"wires":[["583b4d14.7fce94"]]},{"id":"e3ba3cdb.5d9ad","type":"rpi-gpio in","z":"978d4790.f6ab88","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":170,"y":340,"wires":[["3581ace4.658574","510c4a13.094294"]]},{"id":"510c4a13.094294","type":"subflow:5bc4fcf5.332164","z":"978d4790.f6ab88","name":"Pin Status Monitoring","x":360,"y":340,"wires":[["4578ba13.421184"],["e4574aad.de7308"]]},{"id":"e4574aad.de7308","type":"ui_toast","z":"978d4790.f6ab88","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":570,"y":360,"wires":[[]]},{"id":"8eab3557.2a9e78","type":"function","z":"978d4790.f6ab88","name":"Time-Diff","func":"var one = flow.get('one1') || 0;\nvar zero = flow.get('zero0') || 0;\nvar diffMS=zero-one;\nvar diffm = diffMS/60000;\nmsg.payload1 = Math.round(diffm);\nreturn msg","outputs":1,"noerr":0,"x":940,"y":340,"wires":[["c480b490.309628","3c7c2323.4c861c"]]},{"id":"e78dad9d.fa084","type":"ui_group","z":"","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]

```

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [28 February 2022 10:30 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/5 "2022-02-28T10:30:03Z")

</div>

i dont use sqlite but have mysql. the logic should be same. (not sure).

build a query to get the time field for pin high position, hope you will sort the data to get required result. as per the screenshot you have posted i assume you are only capturing _changes_ in the pin position and need the total duration when pin was in **high** position.  
then use my function code to get the difference of time

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [28 February 2022 10:31 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/6 "2022-02-28T10:31:06Z")

</div>

you need to store the values into flow context.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 10:32 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/7 "2022-02-28T10:32:09Z")

</div>

How can I store them, could you please show me ? Then I will use your function right ?

Normally Im saving data into SQLite db

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [28 February 2022 10:33 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/8 "2022-02-28T10:33:01Z")

</div>

i will wait for people with sqlite nodes to respond.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [28 February 2022 10:42 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/9 "2022-02-28T10:42:15Z")

</div>

What type of graph?  
How do you decide which two rows go together?  
What if the second reading goes to the next day?  
What if a reading is missed?

Have you thought about altering the code that writes to the database to have one row per on/off time. The table would have  
ID, start-time, end-time, duration, current-gpio-status,gpio-number  
When a gpio is turned on, you write out a row with the start-time. When the gpio is turned off you get the current time then retrieve the prior row and calculate the duration then update the row.

The in NR you just need to get the date/time (probably the start-time) and duration and send them to a graph.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [28 February 2022 10:48 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/10 "2022-02-28T10:48:18Z")

</div>

@wtrk similar to what Paul is suggesting, something like this .

I log fdate and tdate both in a single row, and calculate the difference there itself (dtmin)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/f/bf492a5015df98cb5212ee871e4991f0c4951631.png)

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 11:26 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/11 "2022-02-28T11:26:44Z")

</div>

Dear @smanjunath211 friend I want completly like you shared photo.Could you please show me your complete flow.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 February 2022 11:32 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/12 "2022-02-28T11:32:15Z")

</div>

As @zenofmud suggest you would be better storing the start time(epoch) and end time(durum). Then when you want to recall your data from db it would be easier.  
I show an example below, it will need work, but might put you on a better track.

```auto
[{"id":"c8f65c8c.8326c","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1","payloadType":"num","x":130,"y":1500,"wires":[["322f98cf.1783f"]]},{"id":"322f98cf.1783f","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":240,"y":1560,"wires":[["91465b8.46fae28"],["5677043f.1a324c"]]},{"id":"f133b5ce.23dbd","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":130,"y":1600,"wires":[["322f98cf.1783f"]]},{"id":"91465b8.46fae28","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":1540,"wires":[["4dd02718.cd69e8"]]},{"id":"5677043f.1a324c","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":410,"y":1580,"wires":[["4dd02718.cd69e8"]]},{"id":"4dd02718.cd69e8","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":570,"y":1560,"wires":[["3fda863b.17afa2","b67ce6c3.2e1688"]]},{"id":"3fda863b.17afa2","type":"template","z":"bf9e1e33.030598","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{payload.durum}},{{payload.epoch}});","output":"str","x":750,"y":1560,"wires":[["2ae33415.ea28ec"]]},{"id":"b67ce6c3.2e1688","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":770,"y":1480,"wires":[["c4680fa0.bc3068","4b0d4a88.3bac64"]]},{"id":"2ae33415.ea28ec","type":"debug","z":"bf9e1e33.030598","name":"store in db","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":960,"y":1560,"wires":[]},{"id":"c4680fa0.bc3068","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"e71e459a.47b628","order":4,"width":"18","height":"10","label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":960,"y":1480,"wires":[[]]},{"id":"4b0d4a88.3bac64","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1000,"y":1440,"wires":[]},{"id":"e71e459a.47b628","type":"ui_group","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]

```

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 11:41 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/13 "2022-02-28T11:41:53Z")

</div>

Dear @E1cid I know but Im lookng for the easy and basic way, because I dont know how can I create epoc also storing data etc.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [28 February 2022 12:01 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/14 "2022-02-28T12:01:58Z")

</div>

just follow his example, it is straight forward. replace inject nodes with your flow output

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 18:04 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/15 "2022-02-28T18:04:56Z")

</div>

Hello dear @E1cid I have done your flow. I need a little bit more help.  
this is my flow

```auto
[{"id":"5bc4fcf5.332164","type":"subflow","name":"Subflow 1","info":"","category":"","in":[{"x":60,"y":80,"wires":[{"id":"2c29ca9d.1f3726"}]}],"out":[{"x":920,"y":100,"wires":[{"id":"26fe58d.da0d2a8","port":0}]},{"x":540,"y":120,"wires":[{"id":"c3cccc67.eba87","port":0}]}]},{"id":"2c29ca9d.1f3726","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":200,"y":80,"wires":[["c3cccc67.eba87","dc32c467.fb1168"]]},{"id":"dc32c467.fb1168","type":"switch","z":"5bc4fcf5.332164","name":"allow only 1 ","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":390,"y":80,"wires":[["90e7351e.a17e68"],["54e68c10.45d2f4"]]},{"id":"c3cccc67.eba87","type":"trigger","z":"5bc4fcf5.332164","op1":"","op2":"Kapı 30 saniyedir açık!!","op1type":"nul","op2type":"str","duration":"30","extend":false,"units":"s","reset":"1","bytopic":"all","name":"","x":390,"y":120,"wires":[[]]},{"id":"90e7351e.a17e68","type":"change","z":"5bc4fcf5.332164","name":"High","rules":[{"t":"set","p":"bc","pt":"msg","to":"orange","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Kapalı","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":80,"wires":[["26fe58d.da0d2a8"]]},{"id":"54e68c10.45d2f4","type":"change","z":"5bc4fcf5.332164","name":"Low","rules":[{"t":"set","p":"bc","pt":"msg","to":"purple","tot":"str"},{"t":"set","p":"status","pt":"msg","to":"Açık","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":590,"y":120,"wires":[["26fe58d.da0d2a8"]]},{"id":"26fe58d.da0d2a8","type":"function","z":"5bc4fcf5.332164","name":"","func":"\nreturn msg;","outputs":1,"noerr":0,"x":780,"y":100,"wires":[[]]},{"id":"8665aba4.942bc8","type":"change","z":"f3966c66.61ab9","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":180,"wires":[["7ace719e.dd215"]]},{"id":"c3ed8690.589198","type":"switch","z":"f3966c66.61ab9","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":340,"y":200,"wires":[["8665aba4.942bc8"],["88fc78b8.fd7988"]]},{"id":"88fc78b8.fd7988","type":"change","z":"f3966c66.61ab9","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":220,"wires":[["7ace719e.dd215"]]},{"id":"7ace719e.dd215","type":"join","z":"f3966c66.61ab9","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":670,"y":200,"wires":[["a26b3aa1.1029c8","ffbc2c4e.3259e"]]},{"id":"a26b3aa1.1029c8","type":"template","z":"f3966c66.61ab9","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{payload.durum}},{{payload.epoch}});","output":"str","x":850,"y":200,"wires":[["1fa43285.c6713d"]]},{"id":"ffbc2c4e.3259e","type":"function","z":"f3966c66.61ab9","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nreturn msg;","outputs":1,"noerr":0,"x":870,"y":120,"wires":[["53fb1968.72f428","b179a5fa.9bd698"]]},{"id":"1fa43285.c6713d","type":"debug","z":"f3966c66.61ab9","name":"store in db","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1060,"y":200,"wires":[]},{"id":"53fb1968.72f428","type":"ui_chart","z":"f3966c66.61ab9","name":"","group":"7f157da0.9d75f4","order":4,"width":"10","height":"10","label":"mySQL data","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1060,"y":120,"wires":[[]]},{"id":"b179a5fa.9bd698","type":"debug","z":"f3966c66.61ab9","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1080,"y":80,"wires":[]},{"id":"ab569222.21cbe","type":"sqlite","z":"f3966c66.61ab9","mydb":"e35ee04b.a8bff","sqlquery":"msg.topic","sql":"","name":"sqliteDB","x":960,"y":420,"wires":[["255d44ec.4fe69c"]]},{"id":"bbc0d33e.3c661","type":"ui_button","z":"f3966c66.61ab9","name":"","group":"c55d32a2.85a79","order":9,"width":"3","height":"1","passthru":false,"label":"B5 Kapı - 1 {{msg.status}}","color":"","bgcolor":"{{bc}}","icon":"","payload":"1","payloadType":"num","topic":"topic","x":590,"y":340,"wires":[[]]},{"id":"decb95c3.0e60b8","type":"template","z":"f3966c66.61ab9","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO 'sensor_durumu' ('durum','epoc') VALUES ({{pyload}},{{zaman}});","output":"str","x":800,"y":420,"wires":[["ab569222.21cbe"]]},{"id":"f94f4ab.35715b8","type":"rpi-gpio in","z":"f3966c66.61ab9","name":"","pin":"40","intype":"down","debounce":"25","read":false,"x":130,"y":380,"wires":[["62f47367.f400cc","516c7814.fdeab8"]]},{"id":"62f47367.f400cc","type":"subflow:5bc4fcf5.332164","z":"f3966c66.61ab9","name":"Pin Status Monitoring","x":340,"y":380,"wires":[["bbc0d33e.3c661","c3ed8690.589198"],["9011dffb.52cb5"]]},{"id":"9011dffb.52cb5","type":"ui_toast","z":"f3966c66.61ab9","position":"dialog","displayTime":"","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Uyarı","name":"","x":570,"y":380,"wires":[[]]},{"id":"b6b8950.87d4068","type":"function","z":"f3966c66.61ab9","name":"Zamanın Oluşturulması","func":"var d= new Date();\nvar zaman = d.getTime();\nmsg.zaman=zaman;\nreturn msg;","outputs":1,"noerr":0,"x":570,"y":420,"wires":[["decb95c3.0e60b8"]]},{"id":"255d44ec.4fe69c","type":"debug","z":"f3966c66.61ab9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1110,"y":420,"wires":[]},{"id":"516c7814.fdeab8","type":"dsm","z":"f3966c66.61ab9","name":"sensör_durumu","sm_config":"{\n \"triggerInput\": \"payload\",\n \"currentState\": \"running\",\n \"states\": {\n \"stopped\": {\n \"0\": \"running\"\n },\n \"running\": {\n \"1\": \"stopped\"\n }\n },\n \"methods\": {\n \"onBeforeTransition\": \"msg.pyload = msg.payload.toString();\",\n \"1\": \"sta.fill='green';msg.background='red';msg.label='S';\",\n \"0\": \"sta.fill='red';msg.background='green';msg.label='S';\"\n }\n}","x":330,"y":420,"wires":[["b6b8950.87d4068","decb95c3.0e60b8"]]},{"id":"7f157da0.9d75f4","type":"ui_group","z":"","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"10","collapse":false},{"id":"e35ee04b.a8bff","type":"sqlitedb","z":"","db":"/var/www/html/database/kapi","mode":"RWC"},{"id":"c55d32a2.85a79","type":"ui_group","z":"","name":"Orginal","tab":"6f5ce300.48f7bc","order":9,"disp":true,"width":"6","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","z":"","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]

```

And after injection it looks like at photo. Is it possible to calculate automatically and showing the screen time difference.

This is my flow photos

 ![test](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/b/6b478e59857677b37f7d99f0df0b94d54be316c5.png)

This is my dashboard photo.

 ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/b/7b4fe63523b3cdd685c3da0ee6c435dba9fe7573.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 February 2022 20:05 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/16 "2022-02-28T20:05:28Z")

</div>

Not sure what your asking, I gave an example to make you question your process. I do not have all your nodes installed, so difficult to say, as i don't have an working example of your flow.

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [28 February 2022 20:29 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/17 "2022-02-28T20:29:20Z")

</div>

Dear @E1cid as I said I want to calculate difference between 0 and 1 position. And also show this time on the dashboard, just like for example

27.02.2022 : 117 min  
28.02.2022 : 130 min  
29.02.2022 : 110 min

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 February 2022 20:43 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/18 "2022-02-28T20:43:22Z")

</div>

Try this

```auto
[{"id":"21ccc01f.e14f6","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1","payloadType":"num","x":210,"y":1140,"wires":[["e86b671f.563508"]]},{"id":"e86b671f.563508","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":320,"y":1200,"wires":[["9e3a33fa.b9cd88"],["d72a34d0.0d12d8"]]},{"id":"9e3a33fa.b9cd88","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1180,"wires":[["26cee816.93d928"]]},{"id":"d72a34d0.0d12d8","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1220,"wires":[["26cee816.93d928"]]},{"id":"766a2757.16fc2","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":210,"y":1240,"wires":[["e86b671f.563508"]]},{"id":"26cee816.93d928","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":1200,"wires":[["99f0be0b.56c148"]]},{"id":"99f0be0b.56c148","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nmsg.label = \"mySQL data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Total \" + msg.payload\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":850,"y":1120,"wires":[["8ded8649.e2dec","24e3ccb9.34163c"]]},{"id":"8ded8649.e2dec","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"91add270.8b6d88","order":4,"width":"18","height":"10","label":"{{msg.label}}","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1010,"y":1120,"wires":[[]]},{"id":"24e3ccb9.34163c","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1080,"y":1080,"wires":[]},{"id":"91add270.8b6d88","type":"ui_group","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]

```

---

<div class="post-metadata">

**Author:** ![wtrk](https://avatars.discourse-cdn.com/v4/letter/w/97f17d/32.png) [@wtrk](https://discourse.nodered.org/u/wtrk)\
**Post date:** [1 March 2022 06:21 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/19 "2022-03-01T06:21:56Z")

</div>

> [@E1cid](#):
>
> ```auto
> [{"id":"21ccc01f.e14f6","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"1","payloadType":"num","x":210,"y":1140,"wires":[["e86b671f.563508"]]},{"id":"e86b671f.563508","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"1","vt":"num"},{"t":"eq","v":"0","vt":"num"}],"checkall":"false","repair":false,"outputs":2,"x":320,"y":1200,"wires":[["9e3a33fa.b9cd88"],["d72a34d0.0d12d8"]]},{"id":"9e3a33fa.b9cd88","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"epoch","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1180,"wires":[["26cee816.93d928"]]},{"id":"d72a34d0.0d12d8","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"date"},{"t":"set","p":"topic","pt":"msg","to":"durum","tot":"str"},{"t":"set","p":"complete","pt":"msg","to":"true","tot":"bool"}],"action":"","property":"","from":"","to":"","reg":false,"x":490,"y":1220,"wires":[["26cee816.93d928"]]},{"id":"766a2757.16fc2","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"0","payloadType":"num","x":210,"y":1240,"wires":[["e86b671f.563508"]]},{"id":"26cee816.93d928","type":"join","z":"bf9e1e33.030598","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":650,"y":1200,"wires":[["99f0be0b.56c148"]]},{"id":"99f0be0b.56c148","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = (msg.payload.durum - msg.payload.epoch)/1000;\nmsg.topic = \"time diff\";\nmsg.label = \"mySQL data&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Total \" + msg.payload\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":850,"y":1120,"wires":[["8ded8649.e2dec","24e3ccb9.34163c"]]},{"id":"8ded8649.e2dec","type":"ui_chart","z":"bf9e1e33.030598","name":"","group":"91add270.8b6d88","order":4,"width":"18","height":"10","label":"{{msg.label}}","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"2","removeOlderPoints":"1000","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":1010,"y":1120,"wires":[[]]},{"id":"24e3ccb9.34163c","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1080,"y":1080,"wires":[]},{"id":"91add270.8b6d88","type":"ui_group","name":"Home","tab":"6f5ce300.48f7bc","order":10,"disp":true,"width":"18","collapse":false},{"id":"6f5ce300.48f7bc","type":"ui_tab","name":"Mercedes-Benz Türk AŞ Kamyon Fabrikası Hol Kapıları","icon":"dashboard","order":1}]
> 
> ```

Dear @E1cid its almost done thank you for your support.  
the only thing I see on the screen latest value, not total value, I showed in picture. Is it possible to show total value ?

 ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9cfa9101fb3c2abb3fca6e01519c7ce93cb9965.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [1 March 2022 08:45 UTC](https://discourse.nodered.org/t/data-get-from-sqlite/59095/20 "2022-03-01T08:45:48Z")

</div>

As people here have been trying to point out to you, your db needs to be re-thought, on and off times need to be stored on same table row and a duration, then it would be an easy sql query to get a total. As you have it, the sql query is vastly more complicated, I suggest you redisign your db storage. I have no sqlite db to test queries to get the sum of the on duration. Maybe someone else can help or ask on a sql forum.

[Next page](https://discourse.nodered.org/t/data-get-from-sqlite/59095.md?page=2)