Re: dbms_jobs once an hour at the bottom of the hour

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Tue, 22 Nov 2005 19:36:31 +0100
Message-ID: <43836583$0$20847$9b4e6d93@newsread2.arcor-online.net>

Michel Cadot schrieb:

> "Syltrem" <syltremzulu_at_videotron.ca> a écrit dans le message de news: 11o69aean6e1p06_at_corp.supernews.com...

> |
> | "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> | news:4382157f$0$4360$626a54ce_at_news.free.fr...
> | > | >
> | > | > dbms_job.submit (..., next_date=>trunc(sysdate,'HH')+3/24/2,
> | > | > interval=>'trunc(sysdate,''HH'')+3/24/2',...);
> | > | >
> | > | > Regards
> | > | > Michel Cadot
> | > | >
> | > | >
> | > |
> | > | How did you come up with this?
> | > | I see the trunc(sysdate,'HH') comes up with the current hour but the
> | > 3/24/2
> | > | is a mystery to me.
> | > |
> | > |
> | >
> | > Well, 1 is a day, 1/24 is one hour, 3/24/2 is 1.5 hour.
> | > So truncating to the hour and adding 1.5 hour give me
> | > 30 minutes after the next hour.
> | >
> |
> | Bonjour Michel
> |
> | Would you also have a clever way of getting the time that would represent
> | the next half hour?
> | I can't find a way...
> | 1:00, then 1:30, then 2:00, 2:30, etc for running a job every 30 minutes.
> |
> | Thanks
> | Syltrem
> |

> 
> trunc(sysdate,'HH')+decode(sign(to_number(to_char(sysdate,'MI'))-30),1,2,1)/48
> 
> Regards
> Michel Cadot
> 
> 

Slightly different
TRUNC(SYSDATE + (30 - TO_NUMBER(TO_CHAR(SYSDATE,'MI')))/1440,'MI') Both expressions would fail if the job was running less than 1 minute ( i.e. to_date('22.11.2005 19:30:15','dd.mm.yyyy hh24:mi:ss') will result in 19:30 instead of 20:00 )

SYSDATE + (30 - MOD(((SYSDATE - TRUNC(SYSDATE)) * 1440),30))/1440 ( if i not made some mistake , what is good possible ;-) ) should give then next half an hour for previous example correct.

Best regards

Maxim Received on Tue Nov 22 2005 - 12:36:31 CST