All geek questions in one place

Oracle: get every half hour between two

I have some data that defines the time range twice.

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 will return all half an hour in each of two times. Thus, it will 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

Data is guaranteed to start and end in an hour or half an hour, so I don’t have to worry about partial matches.

Usually I try to show what I did myself to solve my problem, but in this case I don’t even have a clue where to start.

+4
source share
3 answers

You can do this using a Hierarchical query or CTE.

SQL Fiddle

Oracle 11g R2:

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;

:

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

:

| 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 |
+4

11gR2, ( CTE ):

with r (my_pk, from_time, to_time) as (
  select my_pk, from_time, to_time
  from my_time_table
  union all
  select my_pk, from_time + interval '30' minute, to_time
  from r
  where from_time + interval '30' minute < to_time
)
select my_pk, to_char(from_time, 'YYYY-MM-DD HH24:MI:SS') as from_time
from r
order by my_pk, from_time;

     MY_PK FROM_TIME         
---------- -------------------
         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

PK, 30- , . CTE ; .

, , , . between :

with r (my_pk, from_time, to_time, max_time) as (
  select my_pk, from_time,
    from_time + interval '30' minute - interval '1' second, to_time
  from my_time_table
  union all
  select my_pk, from_time + interval '30' minute,
    to_time + interval '30' minute, max_time
  from r
  where from_time + interval '30' minute < max_time
)
select my_pk, to_char(from_time, 'YYYY-MM-DD HH24:MI:SS') as from_time,
  to_char(to_time, 'YYYY-MM-DD HH24:MI:SS') as to_time
from r
order by my_pk, from_time;

    MY_PK FROM_TIME           TO_TIME           
---------- ------------------- -------------------
         1 2014-01-01 09:00:00 2014-01-01 09:29:59 
         1 2014-01-01 09:30:00 2014-01-01 09:59:59 
         1 2014-01-01 10:00:00 2014-01-01 10:29:59 
         1 2014-01-01 10:30:00 2014-01-01 10:59:59 
         1 2014-01-01 11:00:00 2014-01-01 11:29:59 
         1 2014-01-01 11:30:00 2014-01-01 11:59:59 
         1 2014-01-01 12:00:00 2014-01-01 12:29:59 
         1 2014-01-01 12:30:00 2014-01-01 12:59:59 
         2 2014-01-02 14:00:00 2014-01-02 14:29:59 
         2 2014-01-02 14:30:00 2014-01-02 14:59:59 
         3 2014-01-03 00:30:00 2014-01-03 00:59:59 
         3 2014-01-03 01:00:00 2014-01-03 01:29:59 
         3 2014-01-03 01:30:00 2014-01-03 01:59:59 
         3 2014-01-03 02:00:00 2014-01-03 02:29:59 
         3 2014-01-03 02:30:00 2014-01-03 02:59:59 
         3 2014-01-03 03:00:00 2014-01-03 03:29:59
+3
select my_pk, from_time + (half_hour_increment-1) * interval '30' minute new_time
from
(
    select my_pk, from_time, to_time, (to_time-from_time)*24*2 half_hours
    from my_time_table
) my_time_table
join
(
    select level half_hour_increment
    from
    (
        select max(to_time-from_time)*24*2 max_half_hours
        from my_time_table
    )
    connect by level <= max_half_hours
) row_generator
    on half_hours >= half_hour_increment
order by my_pk, half_hour_increment;
+2
source

Source: https://habr.com/ru/post/1527771/

More articles:


All Articles