Solved

Database Task Custom Schedule

  • 28 October 2019
  • 3 replies
  • 910 views

Userlevel 5
Badge +10

Good afternoon,

Does anyone know what expression should be used in the Custom option for scheduling tasks, when you want a task to execute for example every 30 minutes Monday-Friday (Not Sat/Sunday)?

Essentially need to combine the weekly and interval options!

Any working example would be appreciated 

Regards

Shaun

icon

Best answer by durette 28 October 2019, 18:43

View original

3 replies

Userlevel 7
Badge +18

The expression should return the date of the NEXT execution. The base unit is one day.

Try an expression like this. (This assumes your week starts on Sunday.)

CASE WHEN TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN TRUNC(SYSDATE + 1, 'DY') + 1 ELSE SYSDATE + 30 / 24 / 60 END

 

Edit: I see you’re in the UK. This might work if your week starts on Monday. I haven't tested it.

CASE WHEN TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN TRUNC(SYSDATE + 2, 'DY') ELSE SYSDATE + 30 / 24 / 60 END

Userlevel 7
Badge +19

This should also work: 

 

FREQ=MINUTELY; INTERVAL=30; BYDAY=MON,TUE,WED,THU,FRI

 

Minute calculation will start at 12.00 AM and run on the next 30 minute. So the first run can be executed sooner than 30 minutes but after 2nd it will be 30 minutes.

 

Check oracle doc as well - https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN10040

Userlevel 7
Badge +18
FREQ=MINUTELY; INTERVAL=30; BYDAY=MON,TUE,WED,THU,FRI

 

This “FREQ=” syntax is all I use now. Thanks.

 

I’ve since used this syntax to automate changes to our Site offsets, since it lets us do things like “the last Sunday in the month” with ease.

 

US Daylight Saving Begins:
FREQ=MONTHLY; BYMONTH=MAR; BYDAY=2SUN;

British Summer Time and Central European Summer Time Begin:
FREQ=MONTHLY; BYMONTH=MAR; BYDAY=-1SUN;

British Summer Time and Central European Summer Time End:
FREQ=MONTHLY; BYMONTH=OCT; BYDAY=-1SUN;

US Daylight Saving Ends:
FREQ=MONTHLY; BYMONTH=NOV; BYDAY=1SUN;
 

Reply