Question 114128 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to transform a list of time periods into a list of dates in SQL

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

Incident_Start Incident_End
1/1/2022 01:05 1/1/2022 03:00
1/2/2022 05:00 1/5/2022 12:34
2/5/2022 13:00 2/6/2022 16:22
… …

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

Date Incident Minutes
1/1/2022 115
1/2/2022 1140
1/3/2022 1440
1/4/2022 1440
1/5/2022 754
1/6/2022 0
… …

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!

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search