Data get from SQLite

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

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.

[{"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"]]}]

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.

My node is like this

[{"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}]

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

you need to store the values into flow context.

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

Normally Im saving data into SQLite db

i will wait for people with sqlite nodes to respond.

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.

1 Like

@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

1 Like

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

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.

[{"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}]
1 Like

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.

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

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

[{"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

This is my dashboard photo.

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.

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

Try this

[{"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                       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 ?

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.