How to calculate delta in timestamp

  • 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? Screenshot 2022-01-25 212210

  • 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.

  • 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.

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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.

  • 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

  • This was removed by the editor as SPAM

  • 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 3 months, 3 weeks ago by  demokedes.
  • 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)
    Intro to Tally Tables and Functions

  • 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.

     

  • 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;

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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 3 months, 3 weeks ago by  demokedes.
  • 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!

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

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