Design option for repetitive tasks

Question

I am writing a small application that handles tasks for people. It’s very simple, but the area I’m stuck with, as far as the design of the table is concerned, is a case of a repetitive task that can be disabled either daily or weekly or monthly. If weekly, then on a specific day, weekly. A month is a specific day.

I have a task table and recurring_type_id, and I'm going to handle repetitive tasks in code, but is this an ideal way? Another way is to insert all tasks when creating a task - for each event. But this also does not seem to be right.

Can anyone advise on design and how to handle it in a convenient and efficient way?

I am using SQL Server 2008 R2

Answer 1

I would create a task table to enter my tasks.

taskTable |taskID |Freq |runAt | ------------------------------- |1 |daily |1 | |2 |daily |0 | |3 |weekly |5 | |4 |weekly |1 | |5 |monthly|15 | |6 |monthly|1 | |7 |once |2013-7-4 | 

runAt for daily newspapers is never considered, so it doesn't matter what value is entered.

runAt for weekly items is the day of the week on which the task should run.

runAt for mothly is the day of the month on which the task should be executed (the tasks of the end of the month, which I usually start on the first, as it saves the hassle of which day the month ends, although you can use this to understand that out of

 lastDayOfMonth = datePart(d,dateadd(s,-1,dateadd(mm, datediff(m,0,getdate())+1,0))) 

runAt for once is the day the task should run.

Then I will create a task for daily work to see what needs to be launched.

 select taskID from taskTable where (freq = 'once' and runAt = convert(varchar(10),getDate(),21)) or freq = 'daily' or (freq = 'weekly' and runAt = datePart(dw,getDate())) or (freq = 'monthly' and runAt = datePart(d,getDate()) 

This request gives me all the taskID for any tasks that I need to complete.

Not sure if this is what you were looking for, but hopefully you will find something useful in it.

Answer 2

I will try to use something a tried and tested solution. Unix Cron Table Solution. This solution is also used by many other tools such as Hudson / Jenkins.

From Wikipedia.

 * * * * * command to be executed
 ┬ ┬ ┬ ┬ ┬
 │ │ │ │ │
 │ │ │ │ │
 │ │ │ │ └───── day of week (0 - 7) (0 or 7 are Sunday, or use names)
 │ │ │ └────────── month (1 - 12)
 │ │ └─────────────── day of month (1 - 31)
 │ └──────────────────── hour (0 - 23)
 └───────────────────────── min (0 - 59)

In addition, it allows you to use shortcuts for recordings.

 Entry Description Equivalent To
 @yearly Run once a year at midnight in the morning of January 1 0 0 1 1 *
 @annually   
 @monthly Run once a month at midnight in the morning of the first of the month 0 0 1 * *
 @weekly Run once a week at midnight in the morning of Sunday 0 0 * * 0
 @daily Run once a day at midnight 0 0 * * *
 @hourly Run once an hour at the beginning of the hour 0 * * * *

From here we get the following table design:

 taskID cronExpression preDefinedDefinition
  1 30 * * * * null
  2 0 * * * * @hourly
  3 ... ....

@ Timothy Britt noted that this decision does not take into account one-time discounts. It's true. Linux and / or Unix also have a command named at . It seems that it stores its entries in a separate file, and a separate daemon controls this file. If we want to include one-time jobs in this table. We can add an additional logical column to OneTimeOnly. Or another table that includes only one-time jobs.

Answer 3

I am doing very similar:

  _id interval_type time day next 

The day field represents a day in an interval that is zero for a one-time and daily. The next field in the table is used to identify when the task should be next. This is necessary for those cases when the device is turned off, when the task should be performed. You just need to decide how to handle one or more missed intervals.

Answer 4

I need to use a date for a link, like a table like

 taskid date_reference Freq distance ----------- -------------- --------------- ----------- 1 2011-01-01 daily NULL 2 2011-01-17 monthly NULL 3 2013-01-17 weekly NULL 4 2013-01-24 once NULL 5 2011-01-01 monthly NULL 6 2011-01-01 monthly NULL 7 2011-01-01 before_eomonth 5 8 2013-01-01 loop_day 4 9 2013-01-01 loop_month 4 

so you can check the period in different ways ...

traditional weekly, daily, monthly and once. as a tuyere solution.

but you will have several different ways, for example

every 4 days

every 4 months

or 5 days before the end of the month

or end of the month

note the inclusion of another date at the end of the task cycle ...

but the work never ends ... so it will never be used ..;)

Answer 5

I would suggest using the industry standard iCalendar format ( http://www.rfc-editor.org/rfc/rfc5545.txt ) instead of coming up with a relational design for this. Here you can see some examples. Since your repetition patterns look pretty simple, creating iCalendar expressions for them should be a fairly simple task.

Here's a useful syntax validator: http://severinghaus.org/projects/icv/

Answer 6

It's not clear what your environment is, but an open source task scheduler can solve this problem.

http://en.wikipedia.org/wiki/Open_Source_Job_Scheduler

Answer 7

I accepted the answers given here and came up with the following database structure:

 Column Data Type id int task_name nvarchar(50) frequency_type nvarchar(10) runat_day int runat_month int runat_year int 

The data is as follows:

 id task_name frequency_type runat_day runat_month runat_year 1 Do this Once once 16 2 2018 2 Do this Monthly monthly 31 0 0 3 Do this Yearly yearly 28 2 0 4 Do this Daily daily 0 0 0 5 Do this Weekly weekly 6 0 0 

The query for data output is as follows:

 DECLARE @chosen_date datetime = '2018-02-28' DECLARE @lastDayOfMonth int = datePart(d,dateadd(s,-1,dateadd(mm, datediff(m,0,getdate())+1,0))) select task_name from scheduled_tasks where (frequency_type = 'once' and runat_day = DATEPART(DAY, @chosen_date) and runat_month = DATEPART(MONTH, @chosen_date) and runat_year = DATEPART(YEAR, @chosen_date)) or frequency_type = 'daily' or (frequency_type = 'weekly' and runat_day = DATEPART(WEEKDAY,@chosen_date)) or (frequency_type = 'monthly' and runat_day = DATEPART(DAY, @chosen_date)) or (frequency_type = 'monthly' and @lastDayOfMonth = DATEPART(DAY, @chosen_date) and runat_day >= @lastDayOfMonth) or (frequency_type = 'yearly' and runat_day = DATEPART(DAY, @chosen_date) and runat_month = DATEPART(MONTH, @chosen_date)) 

So far, all my use cases have been correct, even the end of the month dates that fall on a day that does not exist in this month are handled correctly (for example, the 31st of every month will still start on February 28th)

Fields that are not required for this frequency_type parameter simply have zero or zero in them and are ignored by the request.

He does not take into account the annual event that takes place on February 29 in a leap year, and he does not consider the time of day in any way.

Answer 8

I read the answers above, here is what I think should be done:

Schedule

---

• I'd

• type (Daily, monthly, weekly, fixed, annual) - Enum

• frequency (can be 1-7 [days of the week], 1-30 (or 28) [days of the month], 1-365 [days of the year] or zero (for daily, fixed) - ArrayField (from integers) - [1, 7] OR [23] OR [235] OR null

• time (time of day in UTC) - ArrayField (from the lines Char - ['9:00', '13: 30 ']

• date (for fixed type) - datetime - 2009-03-21

• is_active (boolean) - to enable, disable the schedule

• name (CharField) - if you want to name the schedule

The remaining fields require context to what you are building.

Now for this, I think of starting a cronjob every 30 minutes (I take the time divided by 30 minutes), which runs a script (django control command in my case) that filters the schedules from this table that need to be run:

The request will be something like this:

 current_day_of_week = 3 current_day_of_month = 24 current_day_of_year = 114 current_time = 13:30 current_date = 2019-04-24 Filter records that match the below query(not even psuedo code)(I'm using Q objects(https://docs.djangoproject.com/en/2.2/topics/db/queries/#complex-lookups-with-q-objects) Q(daily AND current_time) OR Q(weekly AND current_day_of_week AND current_time) OR Q(monthly AND current_day_of_month AND current_time) OR Q(yearly AND current_day_of_year AND current_time) OR Q(fixed AND current_date AND current_time)