title: Get count of matching time ranges for every minute of the day in Postgres
body: <h1>Problem</h1>
  
  <p>I have a table of records each containing <code>id</code>, <code>in_datetime</code>, and <code>out_datetime</code>. A record is considered "open" during the time between the <code>in_datetime</code> and <code>out_datetime</code>. I want to know how many time records were "open" for each minute of the day (regardless of date). For example, for the last 90 days I want to know how many records were "open" at 3:14 am, then 3:15 am, then 3:16 am, then... If no records were "open" at 2:00 am the query should return 0 or null instead of excluding the row, thus 1440 rows should always be returned (the number of minutes in a day). Datetimes are stored in UTC and need to be cast to a time zone.</p>
  
  <h3>Simplified example graphic</h3>
  
  <pre><code>record_id | time_range
            | 0123456789 (these are minutes past midnight)
          1 | =========
          2 |      ===
          3 | =======
          4 |    ===
          5 | ==
  ______________________
  result      3323343210
  </code></pre>
  
  <h3>Desired output</h3>
  
  <pre><code>time  | count of open records at this time 
  00:00   120
  00:01   135
  00:02   132
  ...
  23:57   57
  23:58   62
  23:59   60
  </code></pre>
  
  <p>No more than 1440 records would ever be returned as there are only 1440 minutes in the day.</p>
  
  <h1>What I've tried</h1>
  
  <p><strong>1.)</strong> In a subquery, I currently generate a minutely series of times for the entire range of each time record. I then group those by time and get a count of the records per minute. 
  <a href="https://www.db-fiddle.com/f/aFyHLEapSBwwbuXvNSnhZK/0" rel="nofollow noreferrer">Here is a db-fiddle using my current query:</a></p>
  
  <pre><code>select
      trs.minutes,
      count(trs.minutes)
  from (
      select
          generate_series(
              DATE_TRUNC('minute', (time_records.in_datetime::timestamptz AT TIME ZONE 'America/Denver')),
              DATE_TRUNC('minute', (time_records.out_datetime::timestamptz AT TIME ZONE 'America/Denver')),
              interval  '1 min'
          )::time as minutes
      from
          time_records
  ) trs
  group by
      trs.minutes
  </code></pre>
  
  <p><strong>This works but is quite inefficient</strong> and takes several seconds to run due to the size of my table. Additionally, it excludes times when no records were open. I think somehow I could use window functions to count the number of overlapping time records for each minute of the day, but I don't quite understand how to do that.</p>
  
  <p><strong>2.)</strong> Modifying Gordon Linoff's query in his answer below, I came to this (<a href="https://www.db-fiddle.com/f/n1ibdtf5Ri6oAsiFyYQnaK/1" rel="nofollow noreferrer">db-fiddle link</a>):</p>
  
  <pre><code>with tr as (
      select 
          date_trunc('minute', (tr.in_datetime::timestamptz AT TIME ZONE 'America/Denver'))::time as m,
          1 as inc
      from
          time_records tr
  
      union all
  
      select
          (date_trunc('minute', (tr.out_datetime::timestamptz AT TIME ZONE 'America/Denver')) + interval '1 minute')::time as m,
          -1 as inc
      from
          time_records tr
  
      union all
  
      select
          minutes::time,
          0
      from
          generate_series(timestamp '2000-01-01 00:00', timestamp '2000-01-01 23:59', interval  '1 min') as minutes
  )
  select
      m,
      sum(inc) as changes_at_inc,
      sum(sum(inc)) over (order by m) as running_count
  from
      tr
  where
      m is not null
  group by 
      m
  order by
      m;
  </code></pre>
  
  <p>This runs reasonably quickly, but towards the end of the day (about 22:00 onwards in the linked example) the values turn negative for some reason. Additionally, this query doesn't seem to work correctly with records with time ranges that cross over midnight. It's a step in the right direction, but I unfortunately don't understand it enough to improve on it further.</p>