SQL Extension 2.0: Cron Schedule Spanning Midnight

[p_devulapalli]

I'm configuring a SQL Extension  and need to schedule data collection every hour from 5:00 PM to 8:00 AM daily.

I've tried using a Cron expression as of below but the extension keeps throwing an error "Schedule is not a valid quartz Cron expression"

0 17-23,0-8 * * ?

This does shows as valid when I verify using https://www.javainuse.com/cron

Just wondering if the extension supports combining ranges and lists in the same field (17-23,0-8) or do I need to create two different schedules to achieve this?

0 17-23 ? * *

0 0-8 ? * *

Any advice please?

[p_devulapalli · Accepted solution]

If anyone interested , managed to find a workaround to this issue , here is what worked for me

The schedule parameter needs to align with the regex pattern defined in the schema and the regex pattern does not seem to support  multiple ranges like "0 17-23,0-8 * * ?" which span overnight 

workaround is to use it as a variable , and define the cron schedule as a variable . This removes the limitations that is caused by the regex in schema 

1) Declare the variable in vars

vars:
  - id: "businessHoursSchedule"
    displayName: "Business Hours Schedule"
    description: "Cron Schedule for Business Hours"
    type: "text"
    defaultValue: "0/10 8-16 ? * *"
  - id: "AfterHoursSchedule"
    displayName: "After Hours Schedule"
    description: "Cron Schedule for After Hours"
    defaultValue: "0 17-23,0-8 ? * *"
    type: "text"​

2) Reference the variable in the schedule field

sqlOracle:
  - group: "ABC"
    subgroups:
      - subgroup: "AfterHours"
        schedule: "var:AfterHoursSchedule"

....................
      - subgroup: "BusinessHours"
        schedule: "var:BusinessHoursSchedule"

When the extension is activated, users can provide their own cron expression in the schedule field via the extension configuration UI or it defaults to using what's defined in the extension yaml

Hope this helps