Fun with SYSDATE, TRUNC() and rounding......
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 May 2006 14:15:07 -0400
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
Other related posts:
