7

I am writing a small application which handles Tasks for people. Very simple, but the area I am stuck on, as far as table design goes, is the case of a recurring task, which can be either once off, daily, weekly or monthly. If weekly, it's on a specific day, weekly. Monthly is a specific day.

I have a tasks table, and a recurring_type_id, and was going to handle the recurring tasks in code, but is the the ideal way? The other way is to insert all the tasks when the task is created - for each event time. But that doesn't seem right either.

Can anyone advice on a design and how to handle this in a maintainable and efficient way?

I'm using SQL Server 2008 R2

Craig
  • 18,074
  • 38
  • 147
  • 248
  • Monthly - if you set a day number you can't handle 31 for some months and even 30 and 29 for February. Do you need more complex logic here? – Serg Jan 19 '13 at 16:49
  • For those cases, my logic would be that if the task is set for the 31st, and the month has 30, 29 or 28 days, use the last available day of THAT month. – Craig Jan 19 '13 at 20:34
  • @Craig was the answer good enough for the bounty? – Lance Jan 27 '13 at 04:37

8 Answers8

13

I would create a task table to insert my tasks into. 

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 dailies is not ever considered so it doesn't matter what value is entered.

runAt for weekly items is the day of the week that the task is to run.

runAt for mothly is the day of the month that the task is to run (month end tasks I usually run on the first since is saves the hassle of dealing with which day the month ends on although you could use this to figure that out

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

runAt for once is the actual day the task is to run.

Then I'd create a task to run daily to see what needed to be run. 

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 query gives me all the taskID for any tasks that I need to run.

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

Lance
  • 3,193
  • 2
  • 32
  • 49
  • I bet you should want to use datepart(weekday,getDate()) when freq is 'weekly'.. or you will miss something in some weeks.. ;) but overall its a nice solution – Frederic Jan 24 '13 at 19:03
  • This solution works, but only if you are okay with the tasks/reminders/notifications running at the same time of the day, every day (or whenever you decide to run the triggering task.) If you would like the flexibility to allow the user to specify the time of day, you need to solve the problem more like Attila suggested. The optimal solution is likely a hybrid of this one and the cron table solution, since the cron table solution failed to account for one-offs. – Timothy Britt Jan 30 '13 at 23:08
  • I know it's been awhile since this has been touched, but what about if you wanted to handle steps in frequencies, like every 2 weeks or ever 4 months? – Dandy Jul 28 '14 at 04:47
  • Something really similar, I posted here: https://stackoverflow.com/a/55827507/2349997 – Gourav Chawla Apr 24 '19 at 10:12
7

I will try to use something tried and tested solution. Unix Cron Table solution. This solution is also used by a lot of other tools like 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)

Also, it allows shortcut names for entries.

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 mentioned that this solution does not account for one-time offs. That is true. Linux and/or Unix also has a command named at. It seems that it stores its entries in a separate file and separate daemon monitors this file. If we want to include one-time jobs in this table. We may add extra boolean column OneTimeOnly. Or another table which only includes one time only jobs.

Seanvm
  • 394
  • 1
  • 9
Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69
1

I do a very similar thing:

   _id
   interval_type
   time
   day
   next

The day field represents the day within the interval, which is null for one-shot and daily. The next field in the table is used to identify when the task should next run. This is needed for those cases when the device is off when the task should run. You just need to decide how to handle one or more missed intervals.

jsmith
  • 4,847
  • 2
  • 32
  • 39
1

I should want to use a date for reference like a table as this

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 many ways...

the traditional weekly, daily,monthly and once. like the lance's solution..

but you would have few different ways, like

each 4 days

each 4 months

or 5 days to the end of month

or end of month

should consider including another date to the end of task loop...

but, job never finish... so, it would never be used.. ;)

Frederic
  • 1,018
  • 6
  • 11
1

I would suggest using an industry standard iCalendar format (http://www.rfc-editor.org/rfc/rfc5545.txt) instead of trying to come up with a relational design for this. You could see some examples here. Since your recurrence patterns look pretty simple, creating the iCalendar expressions for them should be a fairly straightforward task.

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

Community
  • 1
  • 1
Alex Nazarov
  • 1,285
  • 1
  • 8
  • 6
  • OP, compare http://stackoverflow.com/a/30605875: just from looking at the complexity of http://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method or similar, it seems a database of rules is easier to maintain and more efficient than actually mapping such rules into a database layout. – isync Aug 07 '15 at 11:33
0

It is not clear exactly what your environment is, but the Open-source Job Scheduler can solve this type of task.

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

colonelclick
  • 2,165
  • 2
  • 24
  • 33
0

I have taken the answers given here and come 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 looks like this:

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 to pull the data back out looks like this:

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 of my use cases have come up correctly, even end of month dates which fall on a day that doesn't exist in the given month is handled correctly (e.g. 31st of every month will still be triggered on the 28th of Feb)

The fields that aren't required for a given frequency_type simply have zero or null in them, and are ignored by the query.

It does not take in to consideration a yearly event that occurs on the 29th of Feb in a leap year, nor does it consider time of day in any way.

Dave Lucre
  • 1,105
  • 1
  • 14
  • 16
0

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

Schedule

  • Id

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

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

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

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

  • is_active (boolean) - for enabling, disabling the schedule

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

Rest of the fields would require context to what you are building.

Now, for this I'm thinking of running a cronjob every 30mins(I'm taking time input separated by 30mins) which runs a script(django management command in my case) which filters schedules from this table that need to be run:

Query would 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)
Gourav Chawla
  • 470
  • 1
  • 4
  • 12