I have a table of time periods with an active incident in the format:

I now need to transform this into a table of dates with the total minutes an incident was active during each date like:

I am able to do this easily via Python/JavaScript like (in pseudo-code, very naively):

dates = [dates between start_date, end_date]

for (date in dates):
if (impact_periods.filter(start_date <= date && end_date >= date).length > 0):
outage_mins = 1440
else if (impact_periods.filter(start_date >= date && end_date <= date).length > 0):
outage_mins = sum(impact_periods.filter(start_date >= date && end_date <= date).outage_mins)
etc

Now I'd like to do this with a SQL query, but I'm not sure how. Obviously, I'll start by creating a date table between the dates I'm interested in:

SELECT
    dd day_start,
    dd::date   1 - interval '1 sec' AS day_end
  FROM
    generate_series (
      'date_start' :: timestamp,
      'date_end' :: timestamp,
      '1 day' :: interval
    ) dd

But now I'm not sure how to sum the impact mins on each day, taking into account that some incidents may start before the day and end during, or start during the day and end after.

Can someone point me in the right direction of how to solve this? Any help is greatly appreciated!

CodePudding user response：

You can try this solution which A/ generates a set of timestamp ranges which correspond to the calendar days B/ select the dates which intersect with the incident time ranges C/ calculate the duration in minutes of the date / incident time intersection :

SELECT lower(r.date_interval) AS Date
     , floor(EXTRACT(EPOCH FROM least(upper(r.date_interval), Incident_End) - greatest(lower(r.date_interval), Incident_Start))/60) AS Incident_Minutes
  FROM your_table AS t
 INNER JOIN 
     ( SELECT tsrange(t, t   interval '1 day') AS date_interval
         FROM generate_series('1/1/2022' :: timestamp, '1/12/2022' :: timestamp, '1 day' :: interval) AS t
     ) AS r
    ON r.date_interval && tsrange(Incident_Start, Incident_End)

see the result in dbfiddle

CodePudding user response：

You can cross join a datetime series of the dates between the start and end of the incident:

select d::date, extract(epoch from ((case when i.incident_end::date = d::date then i.incident_end else d::date   interval '1 day' end) - (case when d::date = i.incident_start::date then i.incident_start else d::date end)))/60 from incidents i 
cross join generate_series(i.incident_start, i.incident_end, interval '1 day') d

See fiddle