Guest User

CalcSwitchOnDuration

a guest
Jul 11th, 2018
146
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # VIEW: vswitcheventsdist - PASS 1 - retrieves only distinct on>off & off>on events
  2. SELECT a.Id,
  3.        a.FeedId,
  4.        a.DateTime,
  5.        a.Value
  6. FROM
  7.   (SELECT * ,
  8.           LAG(Id) OVER (
  9.                         ORDER BY datetime) AS prev
  10.    FROM feedsdata
  11.    WHERE feedid = 27
  12.      AND (Value = 'on'
  13.           OR Value = 'off')
  14.    ORDER BY datetime) a
  15. INNER JOIN feedsdata fd ON a.prev = fd.Id
  16. WHERE a.Value <> fd.Value
  17.  
  18. ####################################################3
  19.  
  20. # PASS 2 - calculate duration
  21. SELECT dtStart,
  22.        dtEnd,
  23.        duration
  24. FROM
  25.   (SELECT a.datetime AS dtEnd,
  26.           b.datetime AS dtStart,
  27.           TIMESTAMPDIFF(MINUTE, b.datetime, a.datetime) AS duration
  28.    FROM
  29.      (SELECT * ,
  30.              LAG(Id) OVER (
  31.                            ORDER BY datetime) AS prev
  32.       FROM vswitcheventsdist) a
  33.    INNER JOIN vswitcheventsdist b ON a.prev = b.Id
  34.    WHERE a.Value = 'off'
  35.      AND b.Value = 'on' ) c
  36.  
  37. #######################
  38.  
  39. # Result
  40.  
  41. dtStart               | dtEnd                 | Duration  
  42. "2018-07-10 04:30:16"   "2018-07-10 04:54:12"   23
  43. "2018-07-10 13:47:27"   "2018-07-10 14:34:48"   47
  44. "2018-07-10 15:48:52"   "2018-07-10 15:49:08"   0
  45. "2018-07-10 17:00:14"   "2018-07-10 17:14:15"   14
RAW Paste Data