SQL: Split time interval into intersects of one hour

Question

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 1

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

Answer 2

Both the above answers are wrong, the first one will fail on

start 2020-05-27 19:15:13.738000 end 2020-05-27 20:07:14.969000 ticketid 414

The second one will fail on many things. Here was my working solution:

WITH interal_periods AS (
    WITH intervals AS (
        ...
    )
    SELECT t.start_time,
           t.end_time,
           t.ticketid,
           t.validitybegin,
           t.validityend,
           LEAST(extract(EPOCH FROM t.end_time - validitybegin) / 60, 60) AS minutes_after_start_of_hour,
           CASE
               WHEN extract(EPOCH FROM t.end_time - validityend) / 60 > 0
                   THEN extract(EPOCH FROM t.end_time - validityend) / 60
               ELSE 0
               END                                                        AS minutes_before_end_of_hour
    FROM (
             SELECT i.*, generate_series AS start_time, generate_series + INTERVAL '1 hour' AS end_time
             FROM intervals i
                      CROSS JOIN generate_series
                 (date_trunc('hour', i.validitybegin::timestamp)
                 , date_trunc('hour', i.validityend::timestamp)
                 , '1 hour'::interval)
         ) t
)
SELECT start_time,
       end_time,
       ticketid,
       validitybegin,
       validityend,
       minutes_after_start_of_hour - minutes_before_end_of_hour as overlap
FROM interal_periods

Answer 3

Or like this:

WITH CTE AS 
(
SELECT *, generate_series ( date_trunc('hour', validitybegin::timestamp) 
        , date_trunc('hour', validityend::timestamp)
        , INTERVAL '1 hour') AS STRT, 
          generate_series ( date_trunc('hour', validitybegin::timestamp) 
        , date_trunc('hour', validityend::timestamp)
        , INTERVAL '1 hour') +  INTERVAL '1 hour' AS END
FROM my_data
),

CTE2 AS 
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY CTE.ticketid ORDER BY CTE.STRT DESC) AS RN, 
    COUNT(CTE.ticketid) OVER(PARTITION BY CTE.ticketid) AS CNT
FROM CTE
)

SELECT CTE2.STRT, CTE2.END, 
CASE WHEN CTE2.CNT>2 THEN CASE 
  WHEN CTE2.RN<>1 AND CTE2.RN<>CTE2.CNT THEN 60 
  WHEN CTE2.RN<>1 AND CTE2.RN=CTE2.CNT THEN ROUND((EXTRACT('EPOCH' FROM CTE2.END-CTE2.VALIDITYBEGIN)/60)::decimal, 0)
  ELSE ROUND((EXTRACT('EPOCH' FROM CTE2.VALIDITYEND-CTE2.STRT)/60)::decimal, 0) 
  END
ELSE CASE 
  WHEN CTE2.RN<>1 THEN ROUND((EXTRACT('EPOCH' FROM CTE2.END-CTE2.VALIDITYBEGIN)/60)::decimal, 0)
  ELSE ROUND((EXTRACT('EPOCH' FROM CTE2.VALIDITYEND-CTE2.STRT)/60)::decimal, 0)
  END
END AS OVERLAP, CTE2.ticketid
FROM CTE2
ORDER BY CTE2.ticketid, CTE2.STRT;