title: In Oracle 11g how do you time weight average data hourly between two dates?
body: <p>I have replaced my original question with this final answer. With the help from Mr. MTO and Mr. Ponder Stibbons and four months of playing around with my oracle 11G instance, I finally have what you see here. This query is Designed for SCADA systems primarily and will do the following...</p>
  
  <p>This query will perform a Time Weighted Average hourly between two date times as TWA, Minimum and Maximum Values during that Interval as Vmin and Vmax. It will also return Time of Minimum and Time of Maximum as Hmin and Hmax. (These are the date times of the minimum value occurrence and maximum value occurrence ).Starting and Ending Interval values as VSTART and VEND. This query will not fail on daylight savings in March or August. (This is why I am using TO_TIMESTAMP_TZ)</p>
  
  <p>Note: this query is setup for 1 hour intervals and any desired interval is possible by replacing and adding just a few items. So Enjoy!!!</p>
  
  <p>This Query works in my Oracle 11g instance and after writing this post i copy-ed the exact text below and pasted into my SQL Developer. So it works!!
  I am having trouble running this in sqlfiddle but soon i will figure this out and have a running test for you. </p>
  
  <p><a href="http://www.sqlfiddle.com/#!4/b2838/23" rel="nofollow noreferrer">SQL Fiddle</a></p>
  
  <pre><code> -- Lets Begin the Query
   WITH INPUTS AS ( 
      SELECT RECNM, 
            TO_TIMESTAMP_TZ ( '01-JAN-15 00:00:00 AMERICA/LOS_ANGELES','DD-MON-RR HH24:MI:SS TZR' ) AS START_TIME,
            TO_TIMESTAMP_TZ ( '06-NOV-15 23:59:59 AMERICA/LOS_ANGELES','DD-MON-RR HH24:MI:SS TZR' ) AS END_TIME
      FROM POINTS
    WHERE ACRONYM = 'WELL32-PSI'  
  ) ,
  ALL_INTERVALS AS ( 
      SELECT RECNM,
           START_TIME + NUMTODSINTERVAL ( ( LEVEL-1 ) , 'HOUR' ) AS TIME
      FROM INPUTS
      CONNECT BY
      LEVEL-1 &lt;=
                 EXTRACT ( DAY FROM END_TIME - START_TIME ) * 24 +
                 EXTRACT ( HOUR FROM END_TIME - START_TIME ) 
  ) ,
  ALL_TIMES AS ( 
      SELECT 
         TIME, 
         VALUE, 
         1 AS HAS_VALUE
      FROM HST H
      INNER JOIN INPUTS I
          ON ( H.RECNM = I.RECNM
          AND H.TIME BETWEEN CAST ( I.START_TIME AS TIMESTAMP ) 
          AND  CAST ( I.END_TIME AS TIMESTAMP ) ) 
      UNION ALL
      SELECT 
         TIME, 
         NULL, 
         0
      FROM ALL_INTERVALS
      ORDER BY TIME,1, 2 NULLS FIRST 
  ) ,
  LEAD_LAG_TIMES AS ( 
      SELECT 
           TIME,
           LAST_VALUE ( VALUE IGNORE NULLS ) OVER ( ORDER BY TIME ASC, VALUE ASC ) AS VALUE,
           24 * 60 * 60 * EXTRACT ( DAY FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
                60 * 60 * EXTRACT ( HOUR FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
                     60 * EXTRACT ( MINUTE FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) + 
                          EXTRACT ( SECOND FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) AS DURATION
      FROM ALL_TIMES 
  ) 
  SELECT CAST ( TRUNC ( TIME,'HH24' ) AS TIMESTAMP WITH TIME ZONE ) AS TIME,
      SUM ( VALUE * DURATION ) / SUM ( DURATION ) AS TWA,
      MIN ( VALUE ) AS VMIN, 
      MAX ( TIME ) KEEP ( DENSE_RANK LAST ORDER BY VALUE DESC ) AS TMIN,
      MAX ( VALUE ) AS VMAX, 
      MAX ( TIME ) KEEP ( DENSE_RANK LAST ORDER BY VALUE ASC ) AS TMAX,
      SUM ( VALUE ) AS TOTAL,
      MAX ( VALUE ) KEEP (DENSE_RANK FIRST ORDER BY TIME ASC) as VSTART,
      MAX ( VALUE ) KEEP (DENSE_RANK LAST ORDER BY TIME ASC) as VEND,
      SUM ( DURATION ) AS TOTAL_DURATION 
  FROM LEAD_LAG_TIMES
  GROUP BY CAST ( TRUNC ( TIME,'HH24' ) AS TIMESTAMP WITH TIME ZONE ) 
  ORDER BY TIME ASC
  </code></pre>
  
  <p>EDIT: You can Include this in the final select statement for a 1 hour Rolling Average that is Time Weighted! I find this very useful in the waste water industry as state regulations/reporting require 24 hour rolling averages and 72 minute rolling averages. If you need a 24 rolling average change ROWS 1 PROCECDING to ROWS 24 PROCEDING</p>
  
  <pre><code>ROUND( AVG ( SUM ( value * DURATION ) / sum ( DURATION ) ) OVER (ORDER BY CAST ( TRUNC ( TIME,'hh24' ) AS TIMESTAMP WITH TIME ZONE ), CAST ( TRUNC ( TIME,'hh24' ) AS TIMESTAMP WITH TIME ZONE ) ROWS 1 PRECEDING),2) AS ROLLING_1H_VAVG,
  </code></pre>
  
  <p>Standard deviation is fun, so add this as well.</p>
  
  <pre><code>ROUND( STDDEV ( VALUE ) , 2 ) as VDEV,
  </code></pre>
  
  <p>If you need the value prior to your start time and after your stop time you can place this with the other union all's.</p>
  
  <pre><code>UNION ALL
  SELECT
     MAX(H.TIME) KEEP (DENSE_RANK FIRST ORDER BY H.TIME DESC) AS TIME, 
     MAX(H.VALUE) KEEP (DENSE_RANK FIRST ORDER BY H.TIME DESC),
     1
  FROM INPUTS I
  INNER JOIN HST H
      ON H.TIME &lt; I.START_TIME
  UNION ALL
  SELECT
     MIN(H.TIME) KEEP (DENSE_RANK FIRST ORDER BY H.TIME) AS TIME, 
     MIN(H.VALUE) KEEP (DENSE_RANK FIRST ORDER BY H.TIME),
     1
  FROM INPUTS I
  INNER JOIN HST H
      ON H.TIME &gt; I.END_TIME
  </code></pre>