Dynamic time sequence query

Dusan Djuric

Suppose you need to see a full (10 minutes, half-hour, one hour, etc) time map for a given date with user_objects changes counter. And you need to do this in one SELECT statement, with no additional tables, views, packages, procedures, or functions. Is it possible? Yes, it is! Here's how:

  
------------------------------------------------------------------------------- 
-- @DYNAMIC_TIME_QUERY.SQL 
------------------------------------------------------------------------------- 
-- Author:  Dusan Djuric 
-- mail:    dusan.djuric@iii.hr 
-- http:    www.CroGuide.com 
-- 
-- Date:    24.01.2002 
-- 
-- Usage:   
--          1. Start SQL*Plus at directory where you put this script 
--          2. Connect to your schema 
--          2. At SQL prompt type "@DYNAMIC_TIME_QUERY.SQL" 
-- 
-- 
-- Purpose: Time-sequences query with no additional packages, procedures, 
-- or functions. 
-- 
-- Tested environment: 
--                    SQL*Plus: Release 8.1.7.0.0 
--                    Oracle8i  Release 8.1.7.2.1 
--                    Windows 98 SE client 
-- 
-- Explanations: 
-- 
--          This trick is based on these facts: 
--          1. One day has 86400 seconds so we need 1 to 5 figures 
--             number to generate all posible seconds of 1 day 
--          2. SELECT statement gives all row combinations
--             if tables are not joined in WHERE clause. 
--          3. Powerful MOD function (read Oracle reference) 
--             enables any wished increment of base number (time sequence 
--             in seconds) 
--          
--          I use the european DATE format. I didn't try any other. 
-- 
------------------------------------------------------------------------------- 

set linesize 300 
set pagesize 1000 
set long 2000 
set longchunksize 2000 
set numwidth 20 
set arraysize 10 
set maxdata 60000 

-- This is for proper date output format 
alter session set NLS_DATE_FORMAT = 'DD.MM.RRRR HH24:MI:SS'; 

-- VARIABLES 
VARIABLE START_TIME    CHAR(8); 
VARIABLE END_TIME      CHAR(8); 
VARIABLE TIME_FORMAT   VARCHAR2(10); 
VARIABLE TIME_SEQUENCE NUMBER; 
VARIABLE ENY_DATE      CHAR(10); 
VARIABLE DFORMAT       VARCHAR2(23); 

------------------------------------------------------------------------------- 
-- EXAMPLE 1 ---------------- SIMPLE TIME SEQUENCE QUERY ---------------------- 
------------------------------------------------------------------------------- 
--          This example gives 13 seconds time intervals between 15:53:13 
--          and 16:01:25 on 15.01.2001 
-- 
--          Be aware of :END_TIME := '16:01:25' setter. 
--          This interval is not in query output because the last 13 
--          seconds interval is bigger than :END_TIME limiter. 
-- 
--          If you set :TIME_FORMAT := 'HH24:MI' instead of 'HH24:MI:SS' 
--          you'll get just 1 minute intervals 
------------------------------------------------------------------------------- 

PROMPT -- EXAMPLE 1 ---------------- SIMPLE TIME SEQUENCE QUERY --------------- 

-- SETING VARIABLES 
BEGIN 
  :START_TIME    := '15:53:13';   -- Time format HH24:MI:SS 
  :END_TIME      := '16:01:25';   -- Time format HH24:MI:SS 
  :TIME_FORMAT   := 'HH24:MI:SS'; -- Time string formats HH24 , HH24:MI 
                                  -- or HH24:MI:SS for output 
  :TIME_SEQUENCE := 13;           -- always in seconds, e.g. 60*10 is 
                                  -- 10 minutes interval sequence 
  :ENY_DATE      := '15.01.2001'; -- eny date as CHAR in DD.MM.RRRR format 
END; 
/ 

-- COLUMN FORMATING 
column TSTRING format a10 heading 'TIME|(varchar2)' 
column TDATE   format a21 heading 'DTIME|(date)' 

SELECT UNIQUE 
--Time representation (string and date data type) 
  TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT) TSTRING, 
  TO_DATE(:ENY_DATE||' '||TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT),'DD.MM.RRRR '||:TIME_FORMAT) TDATE

FROM 
  ( 
    SELECT 
--Building numbers by length restriction 
      TO_NUMBER(DECODE(LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))), 

                       1,N1.N, 
                       2,N1.N||N2.N, 
                       3,N1.N||N2.N||N3.N, 
                       4,N1.N||N2.N||N3.N||N4.N, 
                       5,N1.N||N2.N||N3.N||N4.N||N5.N 
                      ) 
               ) SEC 
    FROM 
--5 number combinations 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >=

Requires Free Membership to View

By submitting you agree to receive email communications from
TechTarget and its partners. Privacy Policy Terms of Use.

 1 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 

      ) N1, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 

      ) N2, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 

      ) N3, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 

      ) N4, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 

      ) N5 
  ) INTERVAL 
WHERE 
-- No joins for N1-N5 subquerys to get 5 number combinations 
-- Min/Max number restriction 
  INTERVAL.SEC <= 
    ( 
      (TO_NUMBER(SUBSTR(:END_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:END_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:END_TIME,7,2))) 

      - 
      (TO_NUMBER(SUBSTR(:START_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:START_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:START_TIME,7,2))) 

    ) 
  AND 
-- See MOD function reference 
  MOD((INTERVAL.SEC + :TIME_SEQUENCE),:TIME_SEQUENCE) = 0 
ORDER BY TDATE; 

PROMPT -- END / EXAMPLE 1 ---------- SIMPLE TIME SEQUENCE QUERY --------------- 

PROMPT 

------------------------------------------------------------------------------- 
-- EXAMPLE 2 ------------ CHANGED USER OBJECTS half-hour TIME MAP ------------- 
------------------------------------------------------------------------------- 
--          This example count changed objects on half-hour time intervals 
--          between 00:00:00 and 23:59:59 on user shema objects last 
--          date changes 
------------------------------------------------------------------------------- 

PROMPT 

PROMPT 

PROMPT -- EXAMPLE 2 ---------- CHANGED USER OBJECTS half-hour TIME MAP -------- 

-- SETING VARIABLES 
BEGIN 
  :START_TIME    := '00:00:00'; 
  :END_TIME      := '23:59:59'; 
  :TIME_FORMAT   := 'HH24:MI:SS'; 
  :TIME_SEQUENCE := 60*30; -- HALF HOUR 
  -- user_object changes last date 
  SELECT TO_CHAR(MAX(LAST_DDL_TIME),'DD.MM.RRRR') 
  INTO   :ENY_DATE 
  FROM   USER_OBJECTS; 
END; 
/ 

-- COLUMN FORMATING 
column TSTRING format a10 heading 'TIME|(varchar2)' 
column TDATE   format a21 heading 'DTIME|(date)' 
column ALTERED format a7 heading 'ALTERED|' 

SELECT 
  TSEQ.TSTRING TSTRING, 
  TSEQ.TDATE TDATE, 
  DECODE(COUNT(O.OBJECT_ID),0,NULL,COUNT(O.OBJECT_ID)) ALTERED 
FROM 
( 
SELECT UNIQUE 
--Time representation (string and date data type) 
  TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT) TSTRING, 
  TO_DATE(:ENY_DATE||' '||TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT),'DD.MM.RRRR '||:TIME_FORMAT) TDATE

FROM 
  ( 
    SELECT 
--Building numbers by length restriction 
      TO_NUMBER(DECODE(LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))), 

                       1,N1.N, 
                       2,N1.N||N2.N, 
                       3,N1.N||N2.N||N3.N, 
                       4,N1.N||N2.N||N3.N||N4.N, 
                       5,N1.N||N2.N||N3.N||N4.N||N5.N 
                      ) 
               ) SEC 
    FROM 
--5 number combinations 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 

      ) N1, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 

      ) N2, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 

      ) N3, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 

      ) N4, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 

      ) N5 
  ) INTERVAL 
WHERE 
-- No joins for N1-N5 subquerys to get 5 number combinations 
-- Min/Max number restriction 
  INTERVAL.SEC <= 
    ( 
      (TO_NUMBER(SUBSTR(:END_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:END_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:END_TIME,7,2))) 

      - 
      (TO_NUMBER(SUBSTR(:START_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:START_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:START_TIME,7,2))) 

    ) 
  AND 
-- See MOD function reference 
  MOD((INTERVAL.SEC + :TIME_SEQUENCE),:TIME_SEQUENCE) = 0 
) TSEQ, 
  USER_OBJECTS O 
WHERE 
  TRUNC(O.LAST_DDL_TIME(+),'MI') BETWEEN TSEQ.TDATE-(:TIME_SEQUENCE/(24*60*60)) AND TSEQ.TDATE 
GROUP BY 
  TSEQ.TSTRING, 
  TSEQ.TDATE; 

PROMPT -- END / EXAMPLE 2 ------ CHANGED USER OBJECTS half-hour TIME MAP ------

PROMPT 


 



	More News and Tutorials
	
		
			
			
					
						Articles
	
		Time conversions
		Finding precise difference in time and date values
		How to convert milliseconds to the proper date format
		Date column as primary key
		Query for multiple Oracle processes and transactions
		

				
				
					
						

			
				Sub-query speed, part 1
				Sub-query speed, part 2
				How to use the TIME column to check Oracle database backup mode
				Add a specified time to variable and delay current job until that time
				Find out when the database was started
				
		
				
				
		
	
	
			
					
						
							
								Related glossary terms
								Terms for Whatis.com - the technology online dictionary
								
								stored procedure
								The Open Group
								dynamic SQL (Dynamic Structured Query Language)
								autonomous transaction
								CFML (ColdFusion Markup Language)
								Java Database Connectivity (JDBC)
								Query by Example (QBE)
								Open Database Connectivity (ODBC)
								foreign key (foreign keyword)
								
							
						
					
				
			
	


This was first published in April 2002

Join the conversationComment

Comments

Results

Contribute to the conversation

All fields are required. Comments will appear at the bottom of the article.

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Latest Headlines

Featured

E-Books

E-Zines

E-Handbooks

Research

Topics

Hot Topics

Advice & Tutorials

Technology Dictionary

Tips

Answers

Ask a Question

Research Library

Product Demos

Resource Centers

Blogs

Dynamic time sequence query

Requires Free Membership to View

More News and Tutorials

Articles

Related glossary terms

Join the conversationComment

Share

Comments

Results

Contribute to the conversation

You May Also Be Interested In...

More Background

More Details

More from Related TechTarget Sites