Thanks for the question, Dale.
Asked: February 07, 2005 - 9:53 am UTC
Last updated: February 08, 2005 - 11:25 am UTC
Version: 9.2.0
Viewed 1000+ times
You Asked
Tom,
This is not really a question, but a request to add this information to a thread that discusses different NEXT_DATE expressions for DBMS_JOB. I could not find an example of how to schedule a job every five minutes on five minute boundaries such as 1:00, 1:05, 1:10, 1:15, etc. The users understand that the jobs will not run right away, but in practice the jobs always start within a few seconds of the boundary.
Scheduling a next_date to run in five minutes is easy. The expression for running on a five minute boundary is a little more difficult because the TRUNC function does not have a rounding option to the prior five minute boundary.
Here's a solution I came up with. Perhaps your readership may be able to find a more elegant expression.
Thanks,
Dale
SELECT
SYSDATE CURRENT_DATE,
trunc(sysdate, 'HH24') +
(to_number(trunc(sysdate,'MI') -
trunc(sysdate, 'HH24'))*60*24 +
5 - mod(to_number(trunc(sysdate,'MI') -
trunc(sysdate, 'HH24'))*60*24,5))/1440 NEXT_DATE
FROM DUAL;
Current Date Next Date
02/07/2005 9:43:25 AM 02/07/2005 9:45:00 AM
and Tom said...
see line 2, more terse and I think "more accurate" as it goes up to the next 5 minute increment when you are on the 5 minute increment (15:55 -> 16:00, not to 15:55 again)
ops$tkyte@ORA9IR2> select dt,
2 trunc(dt,'hh')+(trunc(to_char(dt,'mi')/5)+1)*5*(1/24/60),
3 trunc(dt, 'HH24') + (to_number(trunc(dt,'MI') - trunc(dt, 'HH24'))*60*24 +
4 5 - mod(to_number(trunc(dt,'MI') - trunc(dt, 'HH24'))*60*24,5))/1440
5 from (select sysdate+1/24/60*rownum dt from all_users )
6 /
DT TRUNC(DT,'HH')+(TRUN TRUNC(DT,'HH24')+(TO
-------------------- -------------------- --------------------
07-feb-2005 15:50:00 07-feb-2005 15:55:00 07-feb-2005 15:50:00
07-feb-2005 15:51:00 07-feb-2005 15:55:00 07-feb-2005 15:55:00
07-feb-2005 15:52:00 07-feb-2005 15:55:00 07-feb-2005 15:55:00
07-feb-2005 15:53:00 07-feb-2005 15:55:00 07-feb-2005 15:55:00
07-feb-2005 15:54:00 07-feb-2005 15:55:00 07-feb-2005 15:55:00
07-feb-2005 15:55:00 07-feb-2005 16:00:00 07-feb-2005 15:55:00
07-feb-2005 15:56:00 07-feb-2005 16:00:00 07-feb-2005 16:00:00
07-feb-2005 15:57:00 07-feb-2005 16:00:00 07-feb-2005 16:00:00
07-feb-2005 15:58:00 07-feb-2005 16:00:00 07-feb-2005 16:00:00
07-feb-2005 15:59:00 07-feb-2005 16:00:00 07-feb-2005 16:00:00
07-feb-2005 16:00:00 07-feb-2005 16:05:00 07-feb-2005 16:05:00
07-feb-2005 16:01:00 07-feb-2005 16:05:00 07-feb-2005 16:05:00
07-feb-2005 16:02:00 07-feb-2005 16:05:00 07-feb-2005 16:05:00
07-feb-2005 16:03:00 07-feb-2005 16:05:00 07-feb-2005 16:05:00
07-feb-2005 16:04:00 07-feb-2005 16:05:00 07-feb-2005 16:05:00
07-feb-2005 16:05:00 07-feb-2005 16:10:00 07-feb-2005 16:05:00
07-feb-2005 16:06:00 07-feb-2005 16:10:00 07-feb-2005 16:10:00
07-feb-2005 16:07:00 07-feb-2005 16:10:00 07-feb-2005 16:10:00
07-feb-2005 16:08:00 07-feb-2005 16:10:00 07-feb-2005 16:10:00
07-feb-2005 16:09:00 07-feb-2005 16:10:00 07-feb-2005 16:10:00
07-feb-2005 16:10:00 07-feb-2005 16:15:00 07-feb-2005 16:10:00
07-feb-2005 16:11:00 07-feb-2005 16:15:00 07-feb-2005 16:15:00
07-feb-2005 16:12:00 07-feb-2005 16:15:00 07-feb-2005 16:15:00
07-feb-2005 16:13:00 07-feb-2005 16:15:00 07-feb-2005 16:15:00
07-feb-2005 16:14:00 07-feb-2005 16:15:00 07-feb-2005 16:15:00
07-feb-2005 16:15:00 07-feb-2005 16:20:00 07-feb-2005 16:20:00
26 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment