How to subtract a timedelta from a datetime in peewee?

Consider the following tables:

class Recurring(db.Model):
    schedule    = ForeignKeyField(Schedule)
    occurred_at = DateTimeField(default=datetime.now)

class Schedule(db.Model):
    delay = IntegerField() # I would prefer if we had a TimeDeltaField

Now, I'd like to get all those events which should recur:

query = Recurring.select(Recurring, Schedule).join(Schedule)
query = query.where(Recurring.occurred_at < now - Schedule.delay) # wishful

Unfortunately, this doesn't work. Hence, I'm currently doing something as follows:

for schedule in schedules:
    then  = now - timedelta(minutes=schedule.delay)
    query = Recurring.select(Recurring, Schedule).join(Schedule)
    query = query.where(Schedule == schedule, Recurring.occurred_at < then)

However, now instead of executing one query, I am executing multiple queries.

Is there a way to solve the above problem only using one query? One solution that I thought of was:

class Recurring(db.Model):
    schedule     = ForeignKeyField(Schedule)
    occurred_at  = DateTimeField(default=datetime.now)
    repeat_after = DateTimeField() # repeat_after = occurred_at + delay

query = Recurring.select(Recurring, Schedule).join(Schedule)
query = query.where(Recurring.repeat_after < now)

However, the above schema violates the rules of the third normal form.

Solution

Each database implements different datetime addition functionality, which sucks. So it will depend a little bit on what database you are using.

For postgres, for example, we can use the "interval" helper:

# Calculate the timestamp of the next occurrence. This is done
# by taking the last occurrence and adding the number of seconds
# indicated by the schedule. 
one_second = SQL("INTERVAL '1 second'")
next_occurrence = Recurring.occurred_at + (one_second * Schedule.delay)

# Get all recurring rows where the current timestamp on the
# postgres server is greater than the calculated next occurrence.
query = (Recurring
         .select(Recurring, Schedule)
         .join(Schedule)
         .where(SQL('current_timestamp') >= next_occurrence))

for recur in query:
    print(recur.occurred_at, recur.schedule.delay)

You could also substitute a datetime object for the "current_timestamp" if you prefer:

my_dt = datetime.datetime(2019, 3, 1, 3, 3, 7)
...
.where(Value(my_dt) >= next_occurrence)

For SQLite, you would do:

# Convert to a timestamp, add the scheduled seconds, then convert back
# to a datetime string for comparison with the last occurrence.
next_ts = fn.strftime('%s', Recurring.occurred_at) + Schedule.delay
next_occurrence = fn.datetime(next_ts, 'unixepoch')

For MySQL, you would do:

# from peewee import NodeList
nl = NodeList((SQL('INTERVAL'), Schedule.delay, SQL('SECOND')))
next_occurrence = fn.date_add(Recurring.occurred_at, nl)

Also lastly, I'd suggest you try better names for your models/fields. i.e., Schedule.interval instead of Schedule.delay, and Recurring.last_run instead of occurred_at.