Calculating values in SQL instead of NodeRed

[demokedes]

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?

[UnborN]

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

[demokedes]

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,

[Steve-Mcl]

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

not ... LIMIT 1

[UnborN]

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