![]() This gives us the same results but without the deep nesting. start_ts )) AS cal_seconds ) t1 ON true JOIN LATERAL ( - calculate uptime seconds for the time slice SELECT coalesce ( extract ( epoch FROM age ( last_ts, first_ts )), 0 ) AS up_seconds ) t2 ON true start_ts ) AS cal_hour, extract ( epoch from age ( cal. start_ts ):: date AS cal_date, extract ( hour from cal. ![]() end_ts ) AS last_ts, date_trunc ( 'day', cal. start_ts ) END AS first_ts, least ( cal. start_ts IS NOT NULL THEN greatest ( uptime. end_ts, current_timestamp ) JOIN LATERAL ( SELECT - will use `first_ts` and `last_ts` to calculate uptime duration CASE WHEN uptime. up_seconds FROM ( - build virtual table of all hours between - a date range SELECT start_ts, start_ts + interval '1 hour' AS end_ts FROM generate_series ( '' :: date, '' :: timestamp - interval '1 hour', interval '1 hour' ) AS t ( start_ts ) ) AS cal LEFT JOIN ( - build virtual table of uptimes SELECT * FROM ( VALUES ( ' 01:15:00-06' :: timestamp, ' 02:15:00-06' :: timestamp ), ( ' 08:00:00-06', ' 20:00:00-06' ), ( ' 19:00:00-06', null ) ) AS t ( start_ts, end_ts ) ) AS uptime ON cal. start_ts )) AS cal_seconds FROM ( - build virtual table of all hours between - a date range SELECT start_ts, start_ts + interval '1 hour' AS end_ts FROM generate_series ( '' :: date, '' :: timestamp - interval '1 hour', interval '1 hour' ) AS t ( start_ts ) ) AS cal LEFT JOIN ( - build virtual table of uptimes SELECT * FROM ( VALUES ( ' 01:15:00-06' :: timestamp, ' 02:15:00-06' :: timestamp ), ( ' 08:00:00-06', ' 20:00:00-06' ), ( ' 19:00:00-06', null ) ) AS t ( start_ts, end_ts ) ) AS uptime ON cal. SELECT - calculate uptime seconds coalesce ( extract ( epoch FROM age ( last_ts, first_ts )), 0 ) AS up_seconds, * FROM ( SELECT - will use `first_ts` and `last_ts` to calculate uptime duration CASE WHEN uptime. Let's add some duration times to the result set. Views, but who has time to lookup that syntax and get their DBA's permission to run the DDL?!? end_ts, current_timestamp )įirst_ts | last_ts | cal_date | cal_hour | cal_seconds ![]() SELECT - will use `first_ts` and `last_ts` to calculate uptime duration CASE WHEN uptime. Let's add some times and sensible column names and replace the * If we try without the LEFT clause, we'll only see 20 rows containing the up slices. Ruby Abuse: How Not to Write Ruby, But Still Have Fun PSA: Vim Modulo '%' Returns Negative Numbers!ĭev Blog Tools :: A Quick Tour of My Setup ![]() Vim Toggle Movement: I Just Want to Go Home ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |