# Calculating values in SQL instead of NodeRed

**URL:** <https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224>\
**Category:** General\
**Tags:** database\
**Created:** [25 January 2022 20:43 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224 "2022-01-25T20:43:21Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![demokedes](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/demokedes/32/19082_2.png) [@demokedes](https://discourse.nodered.org/u/demokedes)\
**Post date:** [25 January 2022 20:43 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224/1 "2022-01-25T20:43:21Z")

</div>

I am total newbie in SQL. I am having SQL Database in Azure (Azure Database).

I am having "smart electrical switches" in my home. I managed to gather data about their usage in SQL table. Attached is example of the table. I would like to automatically calculate duration of time when particular light is turned on. This can be calculated as a substracation of switch turned off and swtich turned on.

In my table I am storing several different switches.

When I press "off" on particular switch this is stored in the table. I would like to find the last record for that switch (by Friendlyname) when the event was on and subsract timestamp of that record from current timestamp. In that way I would get the lenght in seconds.

Is is possible to achieve this in SQL? How to do that? Till now I was calculating this in Nodered, but I believe it can be achieved easily with SQL? Am I right?

 ![Screenshot 2022-01-25 212210](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/4/a4b47c739b305e9b5fe28a6020bb28bb1f268bb3.jpeg)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [25 January 2022 21:00 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224/2 "2022-01-25T21:00:18Z")

</div>

date calculations may be different between databases  
maybe something like this ?

`SELECT *, DATEDIFF(CURRENT_DATE(), timestamp) FROM table WHERE friendlyName = 'BalconyLight' ORDER BY timestamp DESC LIMIT 1`

_change `table` with your table name_

---

<div class="post-metadata">

**Author:** ![demokedes](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/demokedes/32/19082_2.png) [@demokedes](https://discourse.nodered.org/u/demokedes)\
**Post date:** [25 January 2022 21:26 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224/3 "2022-01-25T21:26:22Z")

</div>

UnborN,  
Thank you for your help, It is returning me an error. First I had to change some things...  
SELECT \*, DATEDIFF(second, GETDATE(), timestamp) FROM dbo.switches WHERE friendlyName = 'BalconyLight' ORDER BY timestamp DESC LIMIT 1;

But still getting an error.  
I don't think, I was clear enough: For record, where "event" is OFF, command should find the last record with the same friendlyname, but the "event" is ON and substract valued....believe DateDIFF should do the work.  
Thank you in advance,  
 ![Screenshot 2022-01-25 222602](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/3/a3f80f8b54ecbbbd8fa1eaf9f45efbdd2c176772.jpeg)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 January 2022 21:31 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224/4 "2022-01-25T21:31:13Z")

</div>

SQL server uses the syntax `SELECT TOP 1 * FROM ...`

not `... LIMIT 1`

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [25 January 2022 22:12 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224/5 "2022-01-25T22:12:53Z")

</div>

i though sql syntax was standard for most databases .. apparently not 😉

`SELECT TOP 1 *, DATEDIFF(second, GETDATE(), timestamp) as DateDiff FROM dbo.switches WHERE friendlyName = 'BalconyLight' AND event = 'off' ORDER BY timestamp DESC`

---

<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:** [26 March 2022 22:13 UTC](https://discourse.nodered.org/t/calculating-values-in-sql-instead-of-nodered/57224/6 "2022-03-26T22:13:42Z")

</div>

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