Skip to content

Instantly share code, notes, and snippets.

@3YH

3YH/totaltime_upndown

Created April 26, 2020 13:51
Show Gist options
  • Save 3YH/301ced5cd0035e6e9eb3a3fb57aea22f to your computer and use it in GitHub Desktop.
Save 3YH/301ced5cd0035e6e9eb3a3fb57aea22f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
totaltime_upndown
with t1 (id, stat, ts, ndx) as (
select monitor_id, monitor_status, performed_at, row_number() over(order by performed_at)
from uptime_checks
),
t2 (id, stat, startts, endts) as (
SELECT t1.id, t1.stat, t1.ts, coalesce(t2.ts, now())
from t1
left join t1 t2
on t2.ndx = t1.ndx + 1
),
t3 (stat, totaltime) as (
select stat, sum(timestampdiff(second, startts, endts))
from t2
group by stat
)
select stat, totaltime
from t3;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment