Oracle: Get every half hour between two times

Question

I've got some data with two times defining a time range.

CREATE TABLE MY_TIME_TABLE
(
    MY_PK     NUMBER(10)  NOT NULL ENABLE,
    FROM_TIME DATE        NOT NULL ENABLE,
    TO_TIME   DATE        NOT NULL ENABLE
);

INSERT INTO MY_TIME_TABLE(MY_PK,FROM_TIME,TO_TIME)
VALUES(1,TO_DATE('2014-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-01-01 13:00:00', 'YYYY-MM-DD HH24:MI:SS');

INSERT INTO MY_TIME_TABLE(MY_PK,FROM_TIME,TO_TIME)
VALUES(2,TO_DATE('2014-01-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-01-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS');

INSERT INTO MY_TIME_TABLEMY_PK,(FROM_TIME,TO_TIME)
VALUES(3,TO_DATE('2014-01-03 00:30:00', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2014-01-03 03:30:00', 'YYYY-MM-DD HH24:MI:SS');

What I would like to do is create a query that would return all of the half hour blocks in between each of the two times. So it would return something like the following:

1, 2014-01-01 09:00:00
1, 2014-01-01 09:30:00
1, 2014-01-01 10:00:00
1, 2014-01-01 10:30:00
1, 2014-01-01 11:00:00
1, 2014-01-01 11:30:00
1, 2014-01-01 12:00:00
1, 2014-01-01 12:30:00
2, 2014-01-02 14:00:00
2, 2014-01-02 14:30:00
3, 2014-01-03 00:30:00
3, 2014-01-03 01:00:00
3, 2014-01-03 01:30:00
3, 2014-01-03 02:00:00
3, 2014-01-03 02:30:00
3, 2014-01-03 03:00:00

The data is guaranteed to start and end on the hour or half hour, so I don't have to worry about partial matches.

I normally try to show what I've done on my own to solve my problem, but in this case I don't even have the faintest clue where to start.

Answer 1

You can do it using a Hierarchical query or a CTE.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE MY_TIME_TABLE ( MY_PK, FROM_TIME, TO_TIME ) AS
SELECT  1, TO_DATE('2014-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2014-01-01 13:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
UNION ALL
SELECT  2, TO_DATE('2014-01-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2014-01-02 15:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
UNION ALL
SELECT  3, TO_DATE('2014-01-03 00:30:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2014-01-03 03:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Hierarchical Query:

SELECT MY_PK, FROM_TIME + (LEVEL-1) / 48
FROM MY_TIME_TABLE
CONNECT BY LEVEL <= (TO_TIME - FROM_TIME) * 48
          AND PRIOR MY_PK = MY_PK
          AND PRIOR dbms_random.value IS NOT NULL

Results:

| MY_PK |         FROM_TIME+(LEVEL-1)/48 |
|-------|--------------------------------|
|     1 | January, 01 2014 09:00:00+0000 |
|     1 | January, 01 2014 09:30:00+0000 |
|     1 | January, 01 2014 10:00:00+0000 |
|     1 | January, 01 2014 10:30:00+0000 |
|     1 | January, 01 2014 11:00:00+0000 |
|     1 | January, 01 2014 11:30:00+0000 |
|     1 | January, 01 2014 12:00:00+0000 |
|     1 | January, 01 2014 12:30:00+0000 |
|     2 | January, 02 2014 14:00:00+0000 |
|     2 | January, 02 2014 14:30:00+0000 |
|     3 | January, 03 2014 00:30:00+0000 |
|     3 | January, 03 2014 01:00:00+0000 |
|     3 | January, 03 2014 01:30:00+0000 |
|     3 | January, 03 2014 02:00:00+0000 |
|     3 | January, 03 2014 02:30:00+0000 |
|     3 | January, 03 2014 03:00:00+0000 |