SQL: Split time interval into intersects of one hour

I am new to SQL, where I am trying to split time intervals into multiple rows of one hour, together with an indicator of how many minutes the interval overlaps each hour.

My data:

    validitybegin    |     validityend     |     ticketid     |
---------------------+---------------------+------------------+
 2017-08-31 12:22:04 | 2017-08-31 13:08:56 |       ID1        |
 2017-08-31 13:09:02 | 2017-08-31 16:46:51 |       ID2        |
 2017-08-31 13:09:10 | 2017-08-31 14:09:10 |       ID3        |

What I would like:

       start         |        end          |   overlap   |     ticketid     |
---------------------+---------------------+-------------+------------------+
 2017-08-31 12:00:00 | 2017-08-31 13:00:00 |     38      |       ID1        |
 2017-08-31 13:00:00 | 2017-08-31 14:00:00 |      9      |       ID1        |
 2017-08-31 13:00:00 | 2017-08-31 14:00:00 |     51      |       ID2        |
 2017-08-31 14:00:00 | 2017-08-31 15:00:00 |     60      |       ID2        |
 2017-08-31 15:00:00 | 2017-08-31 16:00:00 |     60      |       ID2        |
 2017-08-31 16:00:00 | 2017-08-31 17:00:00 |     47      |       ID2        |
 2017-08-31 13:00:00 | 2017-08-31 14:00:00 |     51      |       ID3        |
 2017-08-31 14:00:00 | 2017-08-31 15:00:00 |      9      |       ID3        |

What would be the easiest/fastest way to do this?

Answer

Try something like this:

select t.start, t.end, t.ticketid, 
       CASE WHEN EXTRACT(HOUR from t.validityend::timestamp - t.validitybegin::timestamp + interval '30 second') = 0 
       THEN EXTRACT(MINUTE from date_trunc('minute', t.validityend::timestamp - t.validitybegin::timestamp + interval '30 second')) ELSE
              CASE WHEN EXTRACT(HOUR from t.end - t.validitybegin::timestamp + interval '30 second')  = 0 
              THEN EXTRACT(MINUTE from date_trunc('minute', t.end - t.validitybegin::timestamp + interval '30 second')) ELSE
                 CASE WHEN  EXTRACT(HOUR from t.validityend::timestamp - t.start + interval '30 second') = 0 
                 THEN EXTRACT(MINUTE from date_trunc('minute', t.validityend::timestamp - t.start + interval '30 second')) ELSE 60 END
         END 
       END as overlap
from
(
  select i.*, generate_series as start, generate_series + interval '1 hour' as end
  from intervals i
  cross join generate_series
        ( date_trunc('hour', i.validitybegin::timestamp) 
        , date_trunc('hour',i.validityend::timestamp)
        , '1 hour'::interval)
) t

demo

Result for sample data

start                   end                     ticketid    overlap
-------------------------------------------------------------------
2017-08-31 12:00:00     2017-08-31 13:00:00     ID1         38
2017-08-31 13:00:00     2017-08-31 14:00:00     ID1         9
2017-08-31 13:00:00     2017-08-31 14:00:00     ID2         51
2017-08-31 14:00:00     2017-08-31 15:00:00     ID2         60
2017-08-31 15:00:00     2017-08-31 16:00:00     ID2         60
2017-08-31 16:00:00     2017-08-31 17:00:00     ID2         47
2017-08-31 13:00:00     2017-08-31 14:00:00     ID3         51
2017-08-31 14:00:00     2017-08-31 15:00:00     ID3         9