SQL query that converts schedule data stored with activity start and end times into counts of minutes in each half hour interval

  Kiến thức lập trình

I have a table that stores data like this:

As you can see, the 10:00-19:00 shift is categorized as ‘Open Time’, with various other activities sprinkled in throughout the day.

I need to build a query that puts the number of minutes for each activity code into half hour interval buckets like this (The ‘Period’ column is the start time of the half hour interval.):

How would you write the query using PostgreSQL?

So far I’ve only built the query that gets the first table and I’m not sure how to proceed.