Sum up data for every 30 mins between two different datetimes

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

CodePudding user response：

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

SELECT 
  RDNG_DT, TAG, MIN(ST_TIME), MAX(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/48391

CodePudding user response：

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

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'), SUM(val)
  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');