Query between dates

So you need to write a query to get rows that exist between dates. This is a situation where the simplicity of the web2py DAL really shows, and how close it is to SQL really shines.

db(
    (db.mytable.start_date >= request.vars.date_begin)
   &(db.mytable.end_date <= request.vars.date_end)
).select()

That looks a whole lot nicer than the other ways of doing things.

We can also check if there are conflicts between records say if we were allocating resources.

Here is the sample pseudocode

# If the date ends in an existing time slot
dbstart < begin and dbend > begin then conflict=True
# If the date begins in an existing time slot
dbstart < end and dbend > end then conflict=True
# If the date is in the middle of an existing time slot
dbstart > begin and dbend < end then conflict=True

And here is the implementation for web2py

if db(
    (db.mytable.start_date < request.vars.date_begin) 
   &(db.mytable.end_date > request.vars.date_begin)
).select().count() > 0:
    conflict = True


if db(
    (db.mytable.start_date < request.vars.date_end) 
   &(db.mytable.end_date > request.vars.date_end)
).select().count() > 0:
    conflict = True


if db(
    (db.mytable.start_date > request.vars.date_begin) 
   &(db.mytable.end_date < request.vars.date_end)
).select().count() > 0:
    conflict = True