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

For some real fun try using systimestamp and then doing some timezone 
conversions, so somthing like an update_date field is always displayed in 
the users local time zone.
 I got quite a few grey hairs trying to get all that stuff configured 
correctly and then trying to get the correct conversion routines to run. 
Something so simple turned out to be so difficult (since the database and 
the application weren't configured correctly).
-----------------------------------------------------------------------------

                               Bill Ferguson
            U.S. Geological Survey - Minerals Information Team
                           PO Box 25046, MS-750
                           Denver Federal Center
                          Denver, Colorado 80225
           Voice (303)236-8747 ext. 321     Fax   (303)236-4208
      ~ Think on a grand scale, start to implement on a small scale ~



"Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
05/30/2006 12:15 PM
Please respond to
Mark.Bobak@xxxxxxxxxxxxxxx


To
<oracle-l@xxxxxxxxxxxxx>
cc

Subject
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/(:nearest_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/(:nearest_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/(:nearest_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/(:nearest_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/(:nearest_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/(:nearest_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/(:nearest_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/(:nearest_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: