How to calculate delta in timestamp

Question

Post reply

How to calculate delta in timestamp

demokedes

Old Hand

Points: 338
More actions
January 25, 2022 at 8:22 pm
Go to Answer
#3977136

Dear all,
I am total newbie in SQL. I am having SQL Database in Azure (Azure Database), but this is much more general question, not related to Cloud databases.
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 substracation of switch tuned 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.
Is is possible to achieve this in SQL? How to do that?

pietlinden

SSC Guru

Points: 63428
More actions
January 25, 2022 at 9:52 pm
Answer
#3977166
Ouch! Not exactly a rookie question! Since you're brand new, I created a simple table...
```
use tempdb;
go

CREATE TABLE deviceActivity (
deviceID int not null,
EventName CHAR(3) not null,
EventTime datetime not null
CONSTRAINT pkDeviceActivity PRIMARY KEY (deviceID, EventTime));
GO
```
I'm assuming a device cannot have two events recorded at the exact same time. I guess I should have added a constraint to EventName (so it's either "On" or "Off"), but I didn't.
Here is some data to play with. (Yes, I should have added another set of records for another device, just to make sure they're being separated properly in the code.)
```
INSERT INTO deviceActivity VALUES (100,'on','2022-01-25 19:51:48.000'),
(100,'off','2022-01-25 19:51:46.000'),(100,'on','2022-01-25 19:51:44.000'),
(100,'off','2022-01-25 19:50:51.000');
```
Now that we're set up, we can query this:
```
SELECT x.deviceID
, x.eventname
, x.eventtime
, x.PrevEventTime
, Duration = CASE WHEN x.PrevEventTime IS NOT NULL THEN 
DATEDIFF(second,x.PrevEventTime,x.eventtime) ELSE Null END
FROM
(SELECT da.deviceID
, da.EventName
, da.eventTime
, PrevEventTime = LAG(da.EventTime,1) OVER (PARTITION BY da.DeviceID ORDER BY da.EventTime)
FROM deviceActivity da) x
```
Read this from the bottom (the SELECT statement in parentheses) first. PrevEventTime is reading the previous event for the current DeviceID (that's what PARTITION BY is doing). The ORDER BY for the PARTITION BY is telling it to first sort the events for the current deviceID in date order, and then it's looking back one record (that's what the LAG(da.EventTime,1) means).
Then in the outer query (where all the column names are preceded by "x." -- because I'm not imaginative) I can now refer to the PrevEventTime and subtract, using DATEDIFF().
Make sense? If not, don't worry, Windowing functions aren't usually beginner level stuff.
If you have more questions, post back.
January 25, 2022 at 10:51 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Some consumable data (CREATE TABLE and INSERT scripts) would be really nice.

If the On/Off events are in pairs (so you can't two "On" events in a row for the same DeviceID), You could sort by DeviceID and TimeStamp, and then use

LAG(TimeStamp,1) OVER (PARTITION BY DeviceID ORDER BY TimeStamp)

to access the previous value. Then you just subtract using DATEDIFF() and it's trivial.

demokedes Old Hand Points: 338 More actions · Answer 2

Yes, record are always in pairs. It is not possible to turn on the switch unless to tuned if off first.

Can you please give me some more details siggestion..command...since I am meeting SQL for the first time. Thank you.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

It doesn't matter if the 'on' is followed by 'on' or 'off', just add up all the 'on' times.

;WITH cte_switches_with_next_time AS (
    SELECT friendlyName, event, timestamp, 
        LEAD(timestamp) OVER(PARTITION BY friendlyName ORDER BY timestamp) AS next_time
    FROM #switches
)
SELECT friendlyName, SUM(DATEDIFF(SECOND, timestamp, ISNULL(next_time, GETDATE()))) AS total_seconds_on
FROM cte_switches_with_next_time
WHERE event = 'on'
GROUP BY friendlyName
ORDER BY friendlyName

I used this sample data. I changed the date to yesterday to avoid getting negative results (at least in my time zone):

DROP TABLE IF EXISTS #switches
CREATE TABLE #switches ( deviceId varchar(30) NOT NULL, friendlyName varchar(50) NOT NULL, event char(3) NOT NULL, timestamp datetime NOT NULL, id int NOT NULL )
INSERT INTO #switches VALUES
    ('100083881b', 'BalconyLight', 'off', '2022-01-24 19:51:48', 43),
    ('100083881b', 'BalconyLight', 'on',  '2022-01-24 19:51:46', 42),
    ('100083881b', 'BalconyLight', 'on',  '2022-01-24 19:51:44', 41),
    ('100083881b', 'BalconyLight', 'off', '2022-01-24 19:50:51', 40),
    ('100083881b', 'BalconyLight', 'on',  '2022-01-24 19:50:47', 39),
    ('10008c7f28', 'KristofMainLight', 'on',  '2022-01-24 19:48:26', 38),
    ('10008c7f28', 'KristofMainLight', 'off', '2022-01-24 19:48:25', 37),
    ('10008c7f28', 'KristofMainLight', 'off', '2022-01-24 19:48:24', 35),
    ('10008c7f28', 'KristofMainLight', 'on',  '2022-01-24 19:48:24', 36),
    ('10008c7f28', 'KristofMainLight', 'on',  '2022-01-24 19:48:23', 34),
    ('10008c7f28', 'KristofMainLight', 'on',  '2022-01-24 19:48:22', 32),
    ('10008c7f28', 'KristofMainLight', 'off',  '2022-01-24 19:48:22', 33)

Btw, that type of data is much better for us than a picture, because then we can immediately write SQL against that table.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

demokedes Old Hand Points: 338 More actions · Answer 4

Uau pietlinden,

Thank you very much for your effort. I recreated your command and it totally makes sense. During tommorow I will try to "ingest" into my scenario.

Thank you once again!

Rok

demokedes Old Hand Points: 338 More actions · Answer 5

Dear pietlinden,

I managed to get duration for all records with the command:

SELECT x.deviceID
, x.friendlyName
, x.timestamp
, x.PrevEventTime
, Duration = CASE WHEN x.PrevEventTime IS NOT NULL THEN
DATEDIFF(second,x.PrevEventTime,x.timestamp) ELSE Null END
FROM
(SELECT da.deviceID
, da.friendlyName
, da.timestamp
, PrevEventTime = LAG(da.timestamp,1) OVER (PARTITION BY da.DeviceID ORDER BY da.timestamp)
FROM switches da) x

And the results is like I was expecting:

Now I am missing two last minor things:

How to ALTER this value in to the table?

I would like to add (alter) this value only to the records which are having "off" as a event.

Thank you for your help!

This reply was modified 2 years, 3 months ago by demokedes.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

ScottPletcher wrote:

It doesn't matter if the 'on' is followed by 'on' or 'off', just add up all the 'on' times.

It actually does matter because two on times without an off time means that there is a missing off time and that you're accumulating the duration of the on time incorrectly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

demokedes Old Hand Points: 338 More actions · Answer 7

Jeff,

You're right this is not ok. Just this morning I alse remembered myself, I have some scenes implemented, which are turning off particular lights even if the light are in the off state already (if children fall asleep with light tuned on).

That is why I implemented another column "previousevent". From the smart home server I am sending value of previous event. In that way it is much easier. I am calculating only values for the records that are having previousevent = OFF & event =ON.

I also achieved to get duration in the proper way, using the script prepared by @pietlinden, pasted bellow:

  SELECT x.deviceID
, x.friendlyName
, x.timestamp
, x.PrevEventTime
, x.event
, x.previousevent
, Duration = CASE WHEN x.PrevEventTime IS NOT NULL AND previousevent = 'on' AND event = 'off' THEN 
DATEDIFF(second,x.PrevEventTime,x.timestamp) ELSE Null END
FROM
(SELECT da.deviceID
, da.friendlyName
, da.timestamp
, da.event
, da.previousevent
, PrevEventTime = LAG(da.timestamp,1) OVER (PARTITION BY da.DeviceID ORDER BY da.timestamp) 
FROM switches da) x

Now my only questions is: how to ALTER additional column "Duration" into my initial table and fill it with value Duration, calculated in above script.

Thank you very much. And sorry for really beginners questions, but I have to smart somewhere.

pietlinden SSC Guru Points: 63428 More actions · Answer 8

You don't really need to store the duration at all. Well, unless you really want to for some reason. (I guess it's okay, because once a duration is calculated, it's static.)

You could use a Common Table Expression to do it, which you use in an update query.

WITH cteDurations (EventID, EventDate, Duration)...

AS ()

UPDATE MyTable SET EventDuration = cteDurations.Duration

WHERE EventID = cteDurations.EventID;

ScottPletcher SSC Guru Points: 100947 More actions · Answer 9

Jeff Moden wrote:

ScottPletcher wrote:
It doesn't matter if the 'on' is followed by 'on' or 'off', just add up all the 'on' times.
It actually does matter because two on times without an off time means that there is a missing off time and that you're accumulating the duration of the on time incorrectly.

How would you be able to resolve that at calc time? Do you just have to skip any objects where the same state is returned back to back? And that happened a LOT in the sample data.

Typically the current state is just captured, without regard to the prior state. Because it adds a lot of complexity and overhead to have to compare to the previous state to know whether or not to capture the current state. Then, if for some reason the current state is unavailable, then the current state can't be captured? That's an unnecessary burden on the state-capture mechanism. I know, for example, at International Paper, the mill machines states were continually captured, regardless of previous state. The state changes were resolved later, where it didn't slow down or amplify the magnitude of the state capture process.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 10

demokedes wrote:

Jeff,
You're right this is not ok. Just this morning I alse remembered myself, I have some scenes implemented, which are turning off particular lights even if the light are in the off state already (if children fall asleep with light tuned on).
That is why I implemented another column "previousevent". From the smart home server I am sending value of previous event. In that way it is much easier. I am calculating only values for the records that are having previousevent = OFF & event =ON.
I also achieved to get duration in the proper way, using the script prepared by @pietlinden, pasted bellow:
  SELECT x.deviceID
, x.friendlyName
, x.timestamp
, x.PrevEventTime
, x.event
, x.previousevent
, Duration = CASE WHEN x.PrevEventTime IS NOT NULL AND previousevent = 'on' AND event = 'off' THEN 
DATEDIFF(second,x.PrevEventTime,x.timestamp) ELSE Null END
FROM
(SELECT da.deviceID
, da.friendlyName
, da.timestamp
, da.event
, da.previousevent
, PrevEventTime = LAG(da.timestamp,1) OVER (PARTITION BY da.DeviceID ORDER BY da.timestamp) 
FROM switches da) x
Now my only questions is: how to ALTER additional column "Duration" into my initial table and fill it with value Duration, calculated in above script.
Thank you very much. And sorry for really beginners questions, but I have to smart somewhere.

The coded approach looks correct, but it's bit confusing because your description doesn't match that. "I am calculating only values for the records that are having previousevent = OFF & event =ON."

Also, are you sure you want to force the state-capture to be absolutely perfect in order to capture total 'on' time? That is, are you sure that for statuses of 'on', 'on', 'on', 'off', you want to totally ignore the first two 'on's?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

demokedes Old Hand Points: 338 More actions · Answer 11

Pietlinden,

I am a bit confused with the code you wrote....I don't know what to modify in this query for me to work? Is it possible, be more precise, what exactly I have to change?

When I try to execute query you wrote, I get this error:

Screenshot 2022-01-26 203917

Thank you in advance?

Regards,

Rok

This reply was modified 2 years, 2 months ago by demokedes.

demokedes Old Hand Points: 338 More actions · Answer 12

I think I solved it!

As I brought today previous state, I also brough previosstate time into the table.

Now the table looks like this:

Screenshot 2022-01-26 220102

The only thing I had to add, was column Duration with Computed Column specification: (case when [event]='off' AND [previousevent]='on' then datediff(second,[previouseventtime],[timestamp]) else (0) end)

And it is working!

Thank you very much for all the ideas!