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

Is your third extract supposed to be MINUTE, not SECOND? With that changed your query gets 21 rows, each with TWA null and DURATION 60. What do you want to get instead; the same but two extra rows, one before and one after your input period? — Alex Poole, Nov 11 '15 at 18:22
I fixed the query, Thank you for pointing that out! I still want 21 rows. as there is 21 minutes, I need ALL_TIMES to include the time/value before and after my input period if at all possible. — ID10T_ERROR, Nov 11 '15 at 18:38
OK, figured out why I was getting everything null; the `cast ... to timestamp` is losing time zone, so outside the LA TZR will get different results; but if it's supposed to be `with time zone` then it's redundant... Is the row before supposed to be 00:05 based on the HST data, or 00:09 based on the time period? — Alex Poole, Nov 11 '15 at 18:40
Thank you for your Input on the redundancy! I use Timestamp TZR because if i do not this will fail on daylight savings days. Yes the HST data is supposed to be 00:05:55. But if there is a way to copy the VALUE at 00:05:55 and place it in for 00:10:00 if 00:10:00 was null, that would be the best. It just might happen a value will be recorded at 00:10:00 where I would not have the need to grab the previous value. — ID10T_ERROR, Nov 11 '15 at 18:51
I should have written the question better now that i re-read it several times. If 00:10:00 has a null value, can i fill it with 00:05:55 value? then if 00:39:59 is null it doesn't matter. the query will fill that in for me on the LAST VALUE Clause. — ID10T_ERROR, Nov 11 '15 at 19:10

Alex Poole · Accepted Answer · 2015-11-11T19:11:21.697

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.

If you only want to use the extra value when there is nothing in the first/last period it's more complicated but at least makes that information available I suppose. You'd need to do more work with lead/lag, perhaps. I'm not sure what logic you'd be using then though. — Alex Poole, Nov 11 '15 at 19:30
I think this will work but the query will take a very long time as this data goes back 24 years. Is there a quick trick to place 00:05:55 Value in for 00:10:00 value if 00:10:00 was null? I seen (+) operator being used but i am reading up on it. — ID10T_ERROR, Nov 11 '15 at 19:33
The plus operator is for an outer join; don't think that's relevant. The keep dense rank should use your time index I think? — Alex Poole, Nov 11 '15 at 19:36
So your existing join between input and HST is alrady doing a full table scan? — Alex Poole, Nov 11 '15 at 20:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94860/discussion-between-id10t-error-and-alex-poole). — ID10T_ERROR, Nov 11 '15 at 21:40

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

1 Answers1