Fun with SYSDATE, TRUNC() and rounding......

So, this was a fun little exercise.....

Everyone knows that trunc(sysdate) will return the current date, rounded to midnight, that is, currently, for me, it would return '2006-05-30 00:00:00' (assuming your nls_date_format is set appropriately).

Most folks know that to round to the nearest minute, hour, etc, you can do something like trunc(sysdate,'HH') or trunc(sysdate,'MI'), etc.

But, I had a DBMS_JOB that I wanted to run every 15 minutes. So, how do you write an expression based on SYSDATE that will round to the next 15 minute interval. That is, for 0 to 14 minutes after the hour, it goes to 15 after, for 15 to 29, it goes to 30, for 30 to 44, it goes to 45, and for 45 to 59, it returns 00 of the next hour?

And, after I poked at it a bit, it didn't take long to figure out the expression. But, then I thought, "I've been here before....what happens next time I want something that works for 5 minutes, or 10 minutes, etc...is there a general solution, for any arbitrary number?"

And, I poked some more, and this is what I came up with:

SQL> variable nearest_min number
SQL> exec :nearest_min := 15

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:08:05 2006-05-30 14:15:00

SQL> exec :nearest_min := 10

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:08:21 2006-05-30 14:10:00

SQL> exec :nearest_min := 60

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:08:33 2006-05-30 15:00:00

SQL> exec :nearest_min := 120

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:08:48 2006-05-30 16:00:00

SQL> exec :nearest_min := 12

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:09:06 2006-05-30 14:12:00

SQL> exec :nearest_min := 5

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:09:29 2006-05-30 14:10:00

SQL> exec :nearest_min := 1

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:09:38 2006-05-30 14:10:00

SQL> exec :nearest_min := 15

PL/SQL procedure successfully completed.

SQL> select
sysdate,trunc(sysdate,'HH')+(trunc((sysdate-trunc(sysdate,'HH'))*24/(:ne arest_min/60))+1)/(60/:nearest_min)/24 from dual;

SYSDATE TRUNC(SYSDATE,'HH')
------------------- -------------------

2006-05-30 14:09:50 2006-05-30 14:15:00

So, that's all....just thought others may find it useful.....

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 30 2006 - 13:15:07 CDT