Sum up data for every 30 mins between two different datetimes - Oracle SQL query

Question

As, I was unable to generate the query to get the resultset of every 30 mins of data between the two dates

There is a requirement where I need to sum the Interval values between two date times. Below is the value which is available in the table

**RDNG_DT       TAG ST_TIME     END_TM      VALUE**
10-Jan-23            ALB    10-Jan-23   10-Jan-23   2
10-Jan-23            ALB    10-Jan-23   10-Jan-23   4
10-Jan-23            ALB    10-Jan-23   10-Jan-23   6
10-Jan-23            ALB    10-Jan-23   10-Jan-23   8
10-Jan-23            ALB    10-Jan-23   10-Jan-23   2
10-Jan-23            ALB    10-Jan-23   10-Jan-23   2
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3

On the table there are two colors mentioned, the first five rows for 30 mins and the next set of records for next 30 mins. Start_Time and End_Time datatype resembles as "Date".

For the clarification, I used the to_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI') AS START_TIME,to_CHAR(END_TIME, 'DD-MON-YYYY HH24:MI') AS START_TIME

RDNG_DT     TAG         ST_TIME                     END_TM                  VALUE
=======================================================================================
10-Jan-23   ALB     10-JAN-23 12.00.00.000000000 AM  10-JAN-23 12.05.00.000000000 AM    2
10-Jan-23   ALB     10-JAN-23 12.05.00.000000000 AM  10-JAN-23 12.10.00.000000000 AM    4
10-Jan-23   ALB     10-JAN-23 12.10.00.000000000 AM  10-JAN-23 12.15.00.000000000 AM    6
10-Jan-23   ALB     10-JAN-23 12.15.00.000000000 AM  10-JAN-23 12.20.00.000000000 AM    8
10-Jan-23   ALB     10-JAN-23 12.20.00.000000000 AM  10-JAN-23 12.25.00.000000000 AM    2
10-Jan-23   ALB     10-JAN-23 12.25.00.000000000 AM  10-JAN-23 12.30.00.000000000 AM    2
10-Jan-23   ALB     10-JAN-23 12.30.00.000000000 AM  10-JAN-23 12.35.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.35.00.000000000 AM  10-JAN-23 12.40.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.40.00.000000000 AM  10-JAN-23 12.45.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.45.00.000000000 AM  10-JAN-23 12.50.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.50.00.000000000 AM  10-JAN-23 12.55.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.55.00.000000000 AM  10-JAN-23 01.00.00.000000000 AM    3

I need the resultset as below for every 30 mins, sum up the Interval values

RDNG_DT     TAG       ST_TIME                 END_TM         VALUE
=======================================================================================
10-Jan-23   ALB     10-JAN-23 12.00.00.000000000 AM   10-JAN-23 12.30.00.000000000 AM    24
10-Jan-23   ALB     10-JAN-23 12.30.00.000000000 AM   10-JAN-23 01.00.00.000000000 AM    18

I tried with the multiple query options but unable to get the result

Below is the basic query which I have tried.Im new to Oracle, required query. Any help please

SELECT
RDNG_DT,
TAG,
RDNG,
START_TIME + interval '30' minute AS START_TIME1
FROM (
SELECT 
RDNG_DT,
TAG,
value
to_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI') AS START_TIME,
to_CHAR(END_TIME, 'DD-MON-YYYY HH24:MI') AS END_TIME

FROM reading 
where tag = 'ALB' 
AND RDNG_DT = '10-JAN-23'
 ) X

Answer 1

Thanks for your valuable suggestion. But as per the requirement I need to display the start_time . For example

RDG_DATE TAG        ST_TIME                 END_TIME            VALUE
10-Jan-23 ALB   10-JAN-23 12.00.00 AM    10-JAN-23 12.30.00 AM  0.02664
10-Jan-23 ALB   10-JAN-23 12.30.00 AM    10-JAN-23 01.00.00.AM  -1.7418

Answer 2

You could try to extract hour and minute from column START_TIME, then apply group on these extracted values.

SELECT 
  RDNG_DT, 
  TAG, 
  TO_CHAR(MIN(ST_TIME), 'DD-MON-YY HH.MI.SS.FF9 AM') AS ST_TIME, 
  TO_CHAR(MAX(END_TM), 'DD-MON-YY HH.MI.SS.FF9 AM') AS END_TM,
  SUM(VALUE) AS VALUE
FROM
  READING
WHERE
  TAG = 'ALB' AND RDNG_DT = '10-JAN-23'
GROUP BY 
  RDNG_DT, TAG, 
  FLOOR((EXTRACT(HOUR FROM ST_TIME) * 60 + EXTRACT(MINUTE FROM ST_TIME))/30) 

Demo: http://sqlfiddle.com/#!4/42960/49059

Answer 3

With this test data, the end date doesn't matter. Each interval is 5 mins so just the start data is enough. Truncate the start data to 30 minute intervals and sum up the VALUE. If you want to display any other values, make sure to use an aggregate function (MIN,MAX,SUM,...)

WITH test_data 
(RDNG_DT,TAG,ST_TIME,END_TM,VAL)
AS
(
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.00.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.05.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    2 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.05.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.10.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    4 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.10.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.15.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    6 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.15.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.20.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    8 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.20.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.25.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    2 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.25.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.30.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    2 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.30.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.35.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    3 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.35.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.40.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    3 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.40.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.45.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    3 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.45.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.50.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    3 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.50.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 12.55.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    3 FROM DUAL UNION ALL
SELECT TO_DATE('10-Jan-23','DD-MON-YY'),'ALB', TO_TIMESTAMP('10-JAN-23 12.55.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'), TO_TIMESTAMP('10-JAN-23 01.00.00.000000000 AM','DD-MON-YY HH.MI.SS.FF9 AM'),    3 FROM DUAL 
)
SELECT 
  --TO_CHAR(st_time,'DD-MON-YY HH.') || to_char(floor(to_number(to_char(st_time, 'mi'))/30)*30, 'fm00'), 
  MIN(RDNG_DT) as rdng_dt,
  SUM(val) AS value, 
  MIN(TAG) AS tag,
  TO_CHAR( MIN(ST_TIME), 'DD-MON-YY HH.MI.SS.FF9 AM') as st_time, 
  TO_CHAR( MAX(END_TM), 'DD-MON-YY HH.MI.SS.FF9 AM') as end_tm
  FROM
    test_Data
  GROUP BY TO_CHAR(st_time,'DD-MON-YY HH.') || to_char(floor(to_number(to_char(st_time, 'mi'))/30)*30, 'fm00');