Retrieve Records based on a Completed Period of Time (eg. Every 15 Minutes)

When you need to run a batch of transaction in a controlled manner and know when they are processed, this approach may be helpful.  The idea is to retrieve all the records that fall under a complete period of time, for example, if we select a period of 5 minutes, the query should retrieve records created within a full 5 minutes period such as: from 10:00:00-10:04:59, from 10:05:00-10:09:59 and so on… no matter at what time we execute the retrieval process.

So if you need to run a process every 15 time to check for transactions, the query should retrieve transactions done, no matter the hour, every completed block of 15 minutes such as 

                                  xx:00:00 till xx:14:59
                                  xx:15:00 till xx:29:59
                                  xx:30:00 till xx:44:59 
                                  xx:45:00 till xx:59:59. 

where xx is any hour.

No matter at what time the process is executed, the query will retrieve the previously completed period for example, if the current time is 13:42:15 the query will retrieve all the transactions from 13:15:00 till 13:29:59 since it is the previous 15 minute period that was completed.

Note that the maximum waiting time will be the period duration (in the previous example 15 minutes) but your process does not need to run every minute or so and does not need to be pulling.

For example, the following is the query format:

 select *  
 from <tables>  
 where <time stamp> between (SYSDATE-15/1440)-((TO_CHAR(((SYSDATE-15/1440)),'MI')*60 + TO_CHAR(((SYSDATE -15/1440)),'SS'))/86400) + (15*(CASE WHEN (TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)<=0 THEN 3 ELSE TO_NUMBER((TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)) END))/1440   
 AND ((SYSDATE)-((TO_CHAR(((SYSDATE)),'MI')*60 + TO_CHAR(((SYSDATE)),'SS'))/86400) + (15*(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')))/1440)-1/86400   

where <tables> are your tables and the <time_stamp> is the record time that is going to be compared to.

If we execute the query against the current time we have:

 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_time,  
 to_char((SYSDATE-15/1440)-((TO_CHAR(((SYSDATE-15/1440)),'MI')*60 + TO_CHAR(((SYSDATE -15/1440)),'SS'))/86400) + (15*(CASE WHEN (TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)<=0 THEN 3 ELSE TO_NUMBER((TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)) END))/1440,'yyyy-mm-dd hh24:mi:ss') period_start,  
 to_char(((SYSDATE)-((TO_CHAR(((SYSDATE)),'MI')*60 + TO_CHAR(((SYSDATE)),'SS'))/86400) + (15*(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')))/1440)-1/86400,'yyyy-mm-dd hh24:mi:ss') period_end  
 from dual  

and the output is:

as you can see, even though the current time is 11:02:31, the period start is the start of the last completed 15 minute period, 10:45:00 and the period end is the end time of the last completed period, 10:59:59.

Lets analyze the first part of the query which obtains the start time of the period:

1:  (SYSDATE-15/1440)  
2:    -   
3:  ((TO_CHAR(((SYSDATE-15/1440)),'MI')*60 + TO_CHAR(((SYSDATE -15/1440)),'SS'))/86400)   
4:    +   
5:  (15* (   
6:      CASE WHEN (  
7:                           TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1)<=0 THEN 3   
8:                 ELSE      TO_NUMBER((TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')-1))   
9:            END  
10:       )  
11:  )/1440  

Line 1:	The current time minus 15 minutes. As you know, when you rest units to sysdate it will deduct days, so what we need to deduct is a fraction of a day, for example, for one second is 1/86400 where 86400 is the number of seconds in a day so to deduct 15 minutes we can do 15*60 / (1440 * 60) where 1440 is the number of minutes in an hour. We can eliminate the 60 seconds and the final number is 15/1440.
Line 3:	We obtains the total number of seconds within the current hour. For example, if the time was 13:32:12, we obtain the 32 minutes and the 12 seconds so we can deduct them from the time obtained in line 1
Lines 3-11:	We obtains the period number, being 0 the first quarter, 1 the second, 2 the third and 3 the fourth
Line 7 and 8:	Obtain the number of period in the current minute. If the division by 15 results in 2 it means we are in the second period, etc..
Lines 6-9:	The case verifies that we don't fall between hours, so in case we are in the first minutes of the hour we will use the last quarter from the previous hour.
Lines 1-11:	Obtain the start of the period.

The second part, the end period is:

1:  (     (SYSDATE)  
2:            -  
3:       ((TO_CHAR(((SYSDATE)),'MI')*60 + TO_CHAR(((SYSDATE)),'SS'))/86400)   
4:            +   
5:       (15*(TRUNC(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/15,'0')))/1440  
6:  )-1/86400   

Line 1:	The current time minus 15 minutes. As you know, when you rest units to sysdate it will deduct days, so what we need to deduct is a fraction of a day, for example, for one second is 1/86400 where 86400 is the number of seconds in a day so to deduct 15 minutes we can do 15*60 / (1440 * 60) where 1440 is the number of minutes in an hour. We can eliminate the 60 seconds and the final number is 15/1440.
Lines 1-6:	Computes the end time of the period
Line 3:	Obtains the total number of seconds within the current hour so if the time is 10:42:15 it will obtain the number of seconds that 42:15 represent
Line 5:	Obtains the number of seconds based on the period number. If the division by 15 results in 2 it means we are in the second period and the total number of minutes is 30
Lines 1-6:	We deduct one second so that the end period will be the minute and 59 seconds.