The next half hour

I have a job in Oracle that runs every 30 minutes (during work hours), and wanted to schedule the first run at the next “xx:30” mark on the clock. Nothing all that exciting, but took me a few minutes to figure out the logic and I thought I would share.

Line of interest from my call to the scheduler:

 start_date      =>   CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE )
 

Basic demonstration of the calculation:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";
  
Session altered.
  
SQL> SELECT SYSDATE FROM DUAL;
  
SYSDATE
-------------------
04/02/2014 12:11 PM
  
SQL> SELECT CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE ) FROM DUAL;
  
CEIL((SYSDATE-TRUNC
-------------------
04/02/2014 12:30 PM
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: