计算Postgresql中的每日停机时间(Calculate Daily Outage in Postgresql)

计算Postgresql中的每日停机时间(Calculate Daily Outage in Postgresql)

我有例如停电开始日期时间：'2017-09-09 06:56:22'和结束日期时间：'2017-09-13 14:22:45'。 现在我想得到停电持续时间每日，如果整个停电然后给我'24：00：00'但是如果有一天的部分时间停电那么我们做减法ex：startTime - DayEndTime（StartTime）那个是开始日结束时的00:00:00。 因此，如果全天中断，那么每天都将计算每天24:00:00。

我如何在postgresql中解决这个问题？ 请帮忙

见下表

StartTime EndTime OutageTime 2017-09-09 6:56:32 2017-09-10 0:00:00 17:03:28 2017-09-10 0:00:00 2017-09-11 0:00:00 24:00:00 2017-09-11 0:00:00 2017-09-12 0:00:00 24:00:00 2017-09-12 0:00:00 2017-09-13 0:00:00 24:00:00 2017-09-13 0:00:00 2017-09-13 14:22:45 14:22:45

I have for example outage Start DateTime: '2017-09-09 06:56:22' and End DateTime: '2017-09-13 14:22:45'. Now I want to get the outage duration Daily, if the whole there was outage then give me '24:00:00' but if there was outage for just part of the day then we do subtraction ex: startTime - DayEndTime(StartTime) that is 00:00:00 the end of that start day. So each day will be calculated for if there is outage the whole day then its 24:00:00.

How do i solve this in postgresql? Please help

see table below

StartTime EndTime OutageTime 2017-09-09 6:56:32 2017-09-10 0:00:00 17:03:28 2017-09-10 0:00:00 2017-09-11 0:00:00 24:00:00 2017-09-11 0:00:00 2017-09-12 0:00:00 24:00:00 2017-09-12 0:00:00 2017-09-13 0:00:00 24:00:00 2017-09-13 0:00:00 2017-09-13 14:22:45 14:22:45

最满意答案

with t (startTime, endTime) as (values ('2017-09-09 6:56:32'::timestamptz,'2017-09-10 0:00:00'::timestamptz), ('2017-09-10 0:00:00','2017-09-11 0:00:00'), ('2017-09-11 0:00:00','2017-09-12 0:00:00'), ('2017-09-12 0:00:00','2017-09-13 0:00:00'), ('2017-09-13 0:00:00','2017-09-13 14:22:45')) select startTime, endTime, upper(i) - lower(i) as a from ( select tstzrange(startTime, endTime) as tstzr, startTime, endTime from t ) t inner join ( select tstzrange(d, d + interval '1 day') as d from generate_series ( (select min(startTime)::date from t), (select max(endTime) from t), '1 day' ) gs (d) ) gs on d && tstzr cross join lateral ( select tstzr * d as i ) cjl ; starttime | endtime | a ------------------------+------------------------+---------- 2017-09-09 06:56:32-03 | 2017-09-10 00:00:00-03 | 17:03:28 2017-09-10 00:00:00-03 | 2017-09-11 00:00:00-03 | 1 day 2017-09-11 00:00:00-03 | 2017-09-12 00:00:00-03 | 1 day 2017-09-12 00:00:00-03 | 2017-09-13 00:00:00-03 | 1 day 2017-09-13 00:00:00-03 | 2017-09-13 14:22:45-03 | 14:22:45 with t (startTime, endTime) as (values ('2017-09-09 6:56:32'::timestamptz,'2017-09-10 0:00:00'::timestamptz), ('2017-09-10 0:00:00','2017-09-11 0:00:00'), ('2017-09-11 0:00:00','2017-09-12 0:00:00'), ('2017-09-12 0:00:00','2017-09-13 0:00:00'), ('2017-09-13 0:00:00','2017-09-13 14:22:45')) select startTime, endTime, upper(i) - lower(i) as a from ( select tstzrange(startTime, endTime) as tstzr, startTime, endTime from t ) t inner join ( select tstzrange(d, d + interval '1 day') as d from generate_series ( (select min(startTime)::date from t), (select max(endTime) from t), '1 day' ) gs (d) ) gs on d && tstzr cross join lateral ( select tstzr * d as i ) cjl ; starttime | endtime | a ------------------------+------------------------+---------- 2017-09-09 06:56:32-03 | 2017-09-10 00:00:00-03 | 17:03:28 2017-09-10 00:00:00-03 | 2017-09-11 00:00:00-03 | 1 day 2017-09-11 00:00:00-03 | 2017-09-12 00:00:00-03 | 1 day 2017-09-12 00:00:00-03 | 2017-09-13 00:00:00-03 | 1 day 2017-09-13 00:00:00-03 | 2017-09-13 14:22:45-03 | 14:22:45