In Oracle 11gR1 how can i include timestamped values just right outside of an where between timestamp clause

Question

(Given this Database SQL Fiddle)

How can I select the previous VALUE right outside my where between clause in the query below?

If this is possible it would eliminate my other query needed to find this value in my VBA excel ADODB macro. As i currently loop though expanding my "WHERE BETWEEN TIMESTAMP and TIMESTAMP" search exponentially until the before time stamped value is found.

The Time Weighted Average (TWA) in the final select statement will not be accurate if i do not have a real VALUE at the beginning of the very first interval.(All other intervals in between are ok and the query runs fine). The data being pushed to my Oracle 11GR1 instance is random and this is not known at all as large gaps could exist. Thus a value will not be present for hours, days, months or sometimes even years.

 -- Lets Begin the Query
 WITH INPUTS AS ( 
    SELECT RECNM, 
          TO_TIMESTAMP_TZ ( '15-01-01 00:10:00 AMERICA/LOS_ANGELES','RR-MM-DD HH24:MI:SS TZR' ) AS START_TIME,
          TO_TIMESTAMP_TZ ( '15-01-01 00:30:59 AMERICA/LOS_ANGELES','RR-MM-DD HH24:MI:SS TZR' ) AS END_TIME
    FROM POINTS
  WHERE ACRONYM = 'WELL32-PSI'  
) ,
ALL_INTERVALS AS ( 
    SELECT RECNM,
         START_TIME + NUMTODSINTERVAL ( ( LEVEL-1 ) , 'MINUTE' ) AS TIME
    FROM INPUTS
    CONNECT BY
    LEVEL-1 <=
               EXTRACT ( DAY FROM END_TIME - START_TIME ) * 24 * 60 +
               EXTRACT ( HOUR FROM END_TIME - START_TIME ) * 60 +
               EXTRACT ( MINUTE 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,'MI' ) AS TIMESTAMP WITH TIME ZONE ) AS TIME,
    SUM ( VALUE * DURATION ) / SUM ( DURATION ) AS TWA,
    SUM ( DURATION ) AS TOTAL_DURATION 
FROM LEAD_LAG_TIMES
GROUP BY CAST ( TRUNC ( TIME,'MI' ) AS TIMESTAMP WITH TIME ZONE ) 
ORDER BY TIME ASC

Example

 2015-01-01 00:00:00 AMERICA/LOS_ANGELES     63.3
 2015-01-01 00:00:08 AMERICA/LOS_ANGELES     63.7
 2015-01-01 00:00:17 AMERICA/LOS_ANGELES     64.6
 2015-01-01 00:00:28 AMERICA/LOS_ANGELES     66.3
 2015-01-01 00:00:45 AMERICA/LOS_ANGELES     66.8
 2015-01-01 00:00:55 AMERICA/LOS_ANGELES     67.5
 2015-01-01 00:01:11 AMERICA/LOS_ANGELES     67.0
 2015-01-01 00:01:30 AMERICA/LOS_ANGELES     67.4
 2015-01-01 00:01:40 AMERICA/LOS_ANGELES     67.9
 2015-01-01 00:01:50 AMERICA/LOS_ANGELES     68.7
 2015-01-01 00:02:01 AMERICA/LOS_ANGELES     68.2
 2015-01-01 00:02:11 AMERICA/LOS_ANGELES     67.1
 2015-01-01 00:02:21 AMERICA/LOS_ANGELES     66.5
 2015-01-01 00:02:31 AMERICA/LOS_ANGELES     65.5
 2015-01-01 00:02:46 AMERICA/LOS_ANGELES     65.0
 2015-01-01 00:02:59 AMERICA/LOS_ANGELES     64.6
 2015-01-01 00:03:15 AMERICA/LOS_ANGELES     64.1
 2015-01-01 00:03:25 AMERICA/LOS_ANGELES     63.2
 2015-01-01 00:03:35 AMERICA/LOS_ANGELES     62.7
 2015-01-01 00:04:05 AMERICA/LOS_ANGELES     62.2
 2015-01-01 00:04:32 AMERICA/LOS_ANGELES     61.8
 2015-01-01 00:05:40 AMERICA/LOS_ANGELES     61.3
 2015-01-01 00:05:55 AMERICA/LOS_ANGELES     60.8-----Not Included in where between but this value is needed
 2015-01-01 00:10:20 AMERICA/LOS_ANGELES     60.3--------- Included in where between
 2015-01-01 00:10:38 AMERICA/LOS_ANGELES     60.9
 2015-01-01 00:10:48 AMERICA/LOS_ANGELES     61.3
 2015-01-01 00:10:58 AMERICA/LOS_ANGELES     61.8
 2015-01-01 00:11:27 AMERICA/LOS_ANGELES     62.3
 2015-01-01 00:13:54 AMERICA/LOS_ANGELES     61.8
 2015-01-01 00:14:10 AMERICA/LOS_ANGELES     61.4
 2015-01-01 00:14:41 AMERICA/LOS_ANGELES     60.9
 2015-01-01 00:15:18 AMERICA/LOS_ANGELES     61.4
 2015-01-01 00:15:51 AMERICA/LOS_ANGELES     60.9
 2015-01-01 00:16:19 AMERICA/LOS_ANGELES     60.4
 2015-01-01 00:16:32 AMERICA/LOS_ANGELES     59.9
 2015-01-01 00:17:04 AMERICA/LOS_ANGELES     59.4
 2015-01-01 00:17:27 AMERICA/LOS_ANGELES     59.9
 2015-01-01 00:17:37 AMERICA/LOS_ANGELES     59.4
 2015-01-01 00:17:58 AMERICA/LOS_ANGELES     59.0
 2015-01-01 00:18:22 AMERICA/LOS_ANGELES     59.4
 2015-01-01 00:18:50 AMERICA/LOS_ANGELES     59.9
 2015-01-01 00:19:00 AMERICA/LOS_ANGELES     60.3
 2015-01-01 00:19:25 AMERICA/LOS_ANGELES     60.8
 2015-01-01 00:19:34 AMERICA/LOS_ANGELES     61.4
 2015-01-01 00:19:45 AMERICA/LOS_ANGELES     62.1
 2015-01-01 00:19:55 AMERICA/LOS_ANGELES     62.5
 2015-01-01 00:20:30 AMERICA/LOS_ANGELES     63.0
 2015-01-01 00:20:51 AMERICA/LOS_ANGELES     63.5
 2015-01-01 00:21:03 AMERICA/LOS_ANGELES     63.9
 2015-01-01 00:22:04 AMERICA/LOS_ANGELES     64.4
 2015-01-01 00:22:28 AMERICA/LOS_ANGELES     64.8
 2015-01-01 00:23:17 AMERICA/LOS_ANGELES     64.4
 2015-01-01 00:23:27 AMERICA/LOS_ANGELES     63.9
 2015-01-01 00:24:31 AMERICA/LOS_ANGELES     63.4
 2015-01-01 00:26:06 AMERICA/LOS_ANGELES     63.0
 2015-01-01 00:27:20 AMERICA/LOS_ANGELES     62.5
 2015-01-01 00:27:30 AMERICA/LOS_ANGELES     61.9
 2015-01-01 00:28:08 AMERICA/LOS_ANGELES     62.4
 2015-01-01 00:28:37 AMERICA/LOS_ANGELES     62.0
 2015-01-01 00:29:21 AMERICA/LOS_ANGELES     62.5
 2015-01-01 00:29:38 AMERICA/LOS_ANGELES     62.9
 2015-01-01 00:31:27 AMERICA/LOS_ANGELES     62.5
 2015-01-01 00:32:01 AMERICA/LOS_ANGELES     62.0
 2015-01-01 00:32:25 AMERICA/LOS_ANGELES     62.5
 2015-01-01 00:35:07 AMERICA/LOS_ANGELES     62.9
 2015-01-01 00:35:56 AMERICA/LOS_ANGELES     62.5
 2015-01-01 00:36:06 AMERICA/LOS_ANGELES     62.0
 2015-01-01 00:36:59 AMERICA/LOS_ANGELES     61.5
 2015-01-01 00:39:31 AMERICA/LOS_ANGELES     62.0
 2015-01-01 00:40:12 AMERICA/LOS_ANGELES     61.5
 2015-01-01 00:40:22 AMERICA/LOS_ANGELES     60.9
 2015-01-01 00:40:35 AMERICA/LOS_ANGELES     60.5
 2015-01-01 00:40:55 AMERICA/LOS_ANGELES     60.0
 2015-01-01 00:41:22 AMERICA/LOS_ANGELES     60.5
 2015-01-01 00:41:46 AMERICA/LOS_ANGELES     60.1
 2015-01-01 00:42:31 AMERICA/LOS_ANGELES     60.6

Answer 1

Not quite sure if it does exactly what you need, but you can include the last record before and after you period by add two more queries to your ALL_TIMES CTE:

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 < 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 > I.END_TIME

The first one gets the time and value for the last row before your range; the second gets the same for the first row after the range.

From your original (well, tweaked slightly) query I get:

TIME                                          TWA TOTAL_DURATION
------------------------------------------ ------ --------------
01-JAN-15 00.10.00.000000000 EUROPE/LONDON   40.5             60
01-JAN-15 00.11.00.000000000 EUROPE/LONDON   62.1             60
...
01-JAN-15 00.29.00.000000000 EUROPE/LONDON   62.5             60
01-JAN-15 00.30.00.000000000 EUROPE/LONDON                      

With those extra unions I get:

TIME                                          TWA TOTAL_DURATION
------------------------------------------ ------ --------------
01-JAN-15 00.05.00.000000000 EUROPE/LONDON   60.8            245
01-JAN-15 00.10.00.000000000 EUROPE/LONDON   60.8             60
01-JAN-15 00.11.00.000000000 EUROPE/LONDON   62.1             60
...
01-JAN-15 00.29.00.000000000 EUROPE/LONDON   62.5             60
01-JAN-15 00.30.00.000000000 EUROPE/LONDON   62.9             87
01-JAN-15 00.31.00.000000000 EUROPE/LONDON                      

I'm seeing London times because of the CAST ( TRUNC ( TIME,'MI' ) AS TIMESTAMP WITH TIME ZONE ); the TRUNC makes it a DATE, with no time zone information obviously; the CAST then converts it to my session time zone. (Which is why the possibly-redundant CAST(... TO TIMEZONE) was also a problem for me). If you'll always run it in the target TZ then it may not matter, but otherwise you'll need to do some manipulation to keep sane values.