postgres - SUM Time of this Interval and Group by 30 and 30 minutes
I've got a ton of questions here,


Are these all the same day?
What should happen if there is no overlap in time?


Let's also focus on this table from the times of 6:30-7:00

     date_start      |      date_end       | overlap  
---------------------+---------------------+----------
 2017-01-06 06:52:00 | 2017-01-06 06:52:00 | 
 2017-01-06 06:56:00 | 2017-01-06 07:25:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 07:05:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 10:29:00 | 00:04:00
 2017-01-06 06:57:00 | 2017-01-06 07:23:00 | 00:03:00


So how do you get this in your ideal result set?

06:30:00 - 07:00:00 |   00:12:36


Please, value our time... When you create sample data, it's important for you to be sure you're not misleading us. I'm sure it's an innocent mistake, but double check next time. Moreover, this should set you down the right path to get this job done..

SELECT
  tsrange,
  sum(
    UPPER(tsrange*tsrange(date_start,date_end)) -
    LOWER(tsrange*tsrange(date_start,date_end))
  )
FROM foo
JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange &amp;&amp; tsrange(date_start,date_end)
GROUP BY tsrange
ORDER BY tsrange
;


Let's break it down, here we generate the times in 30 minute intervals as a timestamp range..

JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange &amp;&amp; tsrange(date_start,date_end)


Then we join this set on an overlap test. The only other clever unintuitive thing is this,

sum(
  UPPER(tsrange*tsrange(date_start,date_end)) -
  LOWER(tsrange*tsrange(date_start,date_end))
)


In my opinion tsrange - tsrange should return an interval (or a cast should be provided). One isn't. So we have to do what's done above. This provides us the duration of the overlap that we previously detected with &amp;&amp;. We can sum(interval) and produce the result set you want..

                    tsrange                    |   sum    
-----------------------------------------------+----------
 ["2017-01-06 06:30:00","2017-01-06 07:00:00") | 00:15:00
 ["2017-01-06 07:00:00","2017-01-06 07:30:00") | 01:23:00
 ["2017-01-06 07:30:00","2017-01-06 08:00:00") | 00:30:00
 ["2017-01-06 08:00:00","2017-01-06 08:30:00") | 00:30:00
 ["2017-01-06 08:30:00","2017-01-06 09:00:00") | 00:30:00
 ["2017-01-06 09:00:00","2017-01-06 09:30:00") | 00:30:00
 ["2017-01-06 09:30:00","2017-01-06 10:00:00") | 00:30:00
 ["2017-01-06 10:00:00","2017-01-06 10:30:00") | 00:29:00
(8 rows)