BD Oracle query by time segments

Important : Oracle is not the dia% of SQL that I use daily, so surely the solution I'm proposing to you may not be the best since is an adaptation of a way to resolve it in SQL Server .

This question has a problem similar to this other that has to do with the need to generate a sequence of periods to be measured in this case of 30 minutes. The first issue is to define the time window, the from / until we want to measure, with that window of time we will generate a table of Periodos with one record for every 30 minutes, there are 48 records if we choose to list a single day, 96 if there are two, etc.

We can do it like this:

CREATE GLOBAL TEMPORARY TABLE Periodos (
    PeriodoId          NUMBER,
    DesdeFechaHora     DATE,
    HastaFechaHora     DATE,
    Descripcion        VARCHAR(255)
);

INSERT INTO Periodos (PeriodoId,DesdeFechaHora,HastaFechaHora)
SELECT
  level,
  (to_date('2017-01-01','YYYY-MM-DD') + 1/48 * (level -1)),
  (to_date('2017-01-01','YYYY-MM-DD') + 1/48 * (level   ))
FROM dual
CONNECT BY LEVEL <= 48 * 2;


UPDATE  Periodos
    SET Descripcion = TO_CHAR(DesdeFechaHora,'YYYY-MM-DD') || ' ' || TO_CHAR(DesdeFechaHora, 'HH24:MI:SS') || '-' || TO_CHAR(HastaFechaHora, 'HH24:MI:SS');

What we do is generate the periods of two days LEVEL <= 48 * 2 from the 2017-01-01 . If we see a summary of the output, we can see how we are going to use this table:

PERIODOID   DESDEFECHAHORA          HASTAFECHAHORA          DESCRIPCION
1           2017-01-01T00:00:00Z    2017-01-01T00:30:00Z    2017-01-01 00:00:00-00:30:00
2           2017-01-01T00:30:00Z    2017-01-01T01:00:00Z    2017-01-01 00:30:00-01:00:00
3           2017-01-01T01:00:00Z    2017-01-01T01:30:00Z    2017-01-01 01:00:00-01:30:00

Now as proof of concept we put together an example table

CREATE GLOBAL TEMPORARY TABLE Ejemplo (
    Fecha              DATE
);

INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 09:05','YYYY-MM-DD HH24:MI'));
INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 09:15','YYYY-MM-DD HH24:MI'));
INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 09:31','YYYY-MM-DD HH24:MI'));
INSERT INTO Ejemplo (Fecha) VALUES (TO_DATE('2017-01-01 10:12','YYYY-MM-DD HH24:MI'));

And the final query by LEFT JOIN allows us to obtain the quantities of dates between periods and also to know which periods do not register movements

SELECT  P.Descripcion,
        COUNT(E.Fecha)
        FROM Periodos P
        LEFT JOIN Ejemplo E
            ON E.Fecha >= P.DesdeFechaHora
            AND E.Fecha < P.HastaFechaHora
        GROUP BY P.Descripcion, P.PeriodoId
        ORDER BY P.PeriodoId;

And an extract of the output:

DESCRIPCION                     COUNT(E.FECHA)
2017-01-01 08:30:00-09:00:00    0
2017-01-01 09:00:00-09:30:00    2
2017-01-01 09:30:00-10:00:00    1
2017-01-01 10:00:00-10:30:00    1
2017-01-01 10:30:00-11:00:00    0

I hope it serves you ..

I have implemented another valid solution, since I am using JPA with Hibernate I leave the Query as I do there in JAVA with JPA in case someone serves him.

 @Query(value="select to_char(trunc(fecha,'HH')+(30*round(to_char( trunc(fecha,'MI'),'MI')/30))/1440,'DD/MM/YYYY hh24:mi') AS fecha, SUM(total) AS total "
        + "from ConteoConsultasVWByFecha  "
        + "WHERE fecha BETWEEN TO_DATE(:fechaDesde, 'DD/MM/YYYY hh24:mi:ss') AND TO_DATE(:fechaHasta, 'DD/MM/YYYY hh24:mi:ss') "
        + "GROUP BY to_char(trunc(fecha,'HH')+(30*round(to_char( trunc(fecha,'MI'),'MI')/30))/1440,'DD/MM/YYYY hh24:mi') "
        + "ORDER BY fecha ASC"
    )
List<Map<String, Long>> findAllByHour(@Param("fechaDesde") String fechaDesde,@Param("fechaHasta")  String fechaHasta);

This brings me the fields I need associated with groups of frames at intervals of 30 minutes, for a range of dates (that's why the Between) should be noted that if there are a lot of records, a limiter should be placed.