CODEWITHSUNDEEP
pythonsqlalchemy
draxous
draxous

Reputation: 59

Date range of current week, month, year. Python/Flask

I have a datefield (created_on) on a data model that I am trying to filter queries by. I am trying to filter by current week, month, year. This is not to be confused with a week ago , month ago, in the last year. (not taking todays date and subtracting X daterange/timedelta to get last 7, 31, 365 days worth)

For Example:

Current Week: If its Wednesday the 7th, then I want to filter from Monday 5th - Sunday 11th..

Current Month If its Feb 16th, I want to filter by February 1st - February 29th.

Current Year If its March 3rd 2016, I want to filter from January 1st 2016 - December 31st 2016.

Phonetic Example:

.filter(Table.created_on BETWEEN FIRST AND LAST DAY OF THIS WEEK/MONTH/YEAR)

Upvotes: 0

Views: 2812

Answers (1)

Vivian
Vivian

Reputation: 1639

In all cases, you'd want LOWER_BOUND < Table.created_on < UPPER_BOUND. Might need to split that into two, not sure if sqlalchemy can do that part automatically.

Current week, you'd subtract between 0 and 6 days depending on the day of the week to get a lower bound, add between 0 and 6 for an upper bound.

Current month, find the current month and year, lower bound is YEAR-MONTH-01, upper bound is YEAR-MONTH-DAYS_IN_MONTH.

Current year, lower bound is YEAR-01-01, upper bound is YEAR-12-31.

To get this information, try the datetime package from the standard library. It really is quite simple, apart from leap-years which are out of scope for this. This stuff doesn't get complicated unless you either deal with precise times (and run into epoch limits or leap seconds or time zones or DST) or "N months/years ago" (in which case you have to deal with "1 month before Dec. 31", "1 year before Feb. 29th", etcetera). As long as you're not using variable-duration things like months or years as a unit, and you don't need precise clock times so days can be safely treated as units, there's barely even arithmetic involved.

# In all
from datetime import date, timedelta
today = date.today()

Then, for week:

weekday = today.weekday()
# lower bound
mon = today - timedelta(days=weekday)
# upper bound
sun = today + timedelta(days=(6 - weekday))

For month:

days_per_month = {1: 31, 2: 29, 3: 31, ...} # you can fill this in yourself
# lower bound
first = today.replace(day=1)
# upper bound
try:
    last = today.replace(day=days_per_month[today.month])
except ValueError:
    if today.month == 2:  # Not a leap year
        last = today.replace(day=28)
    else:
        raise  # just to be safe, in case there's some other error I missed

And for year:

# lower bound
janfirst = today.replace(month=1, day=1)
# upper bound
declast = today.replace(month=12, day=31)

In all cases, if the contents of the column are guaranteed to be in the past (e.g. if they're the date the row was added to the table), you can replace the upper bound with the current date. Or maybe with tomorrow's, in case of a query started very close to midnight and a new entry just after.

Upvotes: 2

Related Questions