5/19/2023 0 Comments Left join postgresql![]() 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 How to Make a Website from Scratch via Githubĭev Blog Tools :: A Quick Tour of My Setup ![]() PSA: Vim Modulo '%' Returns Negative Numbers! Vim Toggle Movement: I Just Want to Go Home
0 Comments
Leave a Reply. |