CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
Emac4444
Emac4444

Reputation: 13

Calculate Daily Outage in Postgresql

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

Upvotes: 0

Views: 170

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

You can do this using generate_series(). The following is the logic for getting the results each day:

select (case when date_trunc('day', g.dte) = date_trunc('day', o.start_time)
             then g.dte
             else date_trunc('day', g.dte)
        end),
       (case when date_trunc('day', g.dte) < date_trunc('day', o.end_time)
             then date_trunc('day', g.dte) + interval '1 day'
             else o.end_time
        end)
from outages o, lateral 
     generate_series(start_time, end_time, interval '1 day') g(dte)
where g.dte < o.end_time;

You can use a subquery to get the span on each day.

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247865

You can just calculate the difference and replace it with 24:00:00 whenever it is a whole day:

SELECT starttime,
       endtime,
       CASE endtime - starttime
          WHEN INTERVAL '1 day'
          THEN '24:00:00'
          WHEN INTERVAL '1 day 1 hour'
          THEN '25:00:00'
          ELSE CAST(endtime - starttime AS text)
       END AS outagetime
FROM outages;

Upvotes: 1

Related Questions