Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Fun with SYSDATE, TRUNC() and rounding......

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

From: William B Ferguson <wbfergus_at_usgs.gov>
Date: Tue, 30 May 2006 13:04:01 -0600
Message-ID: <OF60C4EC7C.71081C0E-ON8725717E.0068524B-8725717E.006902CA@usgs.gov>


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_at_il.proquest.com> Sent by: oracle-l-bounce_at_freelists.org
05/30/2006 12:15 PM
Please respond to
Mark.Bobak_at_il.proquest.com

To
<oracle-l_at_freelists.org>
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

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 30 2006 - 14:04:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US