How to retrieve records from past weeks in django
django, django-orm, mysql
Solution
I assume what you are looking for are all the entries that belong to the same calendar week of the previous week.
This should do the trick:
class Entry(models.Model):
pub_date = models.DateField([...])
To get the objects:
from datetime import timedelta
from django.utils import timezone
some_day_last_week = timezone.now().date() - timedelta(days=7)
monday_of_last_week = some_day_last_week - timedelta(days=(some_day_last_week.isocalendar()[2] - 1))
monday_of_this_week = monday_of_last_week + timedelta(days=7)
Entry.objects.filter(created_at__gte=monday_of_last_week, created_at__lt=monday_of_this_week)
Note that I added 7 days to get the monday of the this week instead of adding 6 days to get the sunday of last week and that I used created_at__lt=monday_of_this_week (instead of __lte=). I did that because if your pub_date was a DateTimeField, it wouldn't include the sunday objects since the time is 00:00:00 when using now().date().
This could easily be adjusted to consider Sunday as the first day of the week instead, but isocalendar() considers it the last, so I went with that.
If using Django < 1.4 use the following:
from datetime import date, timedelta
some_day_last_week = date.today() - timedelta(days=7)
instead of:
from datetime import timedelta
from django.utils import timezone
some_day_last_week = timezone.now().date() - timedelta(days=7)
Problem
I am having trouble in django on how to retrieve data from last week (not 7 days ago). Using `date.isocalendar()[1]` would be great. However Some stackoverflow browsing lead me to no satisfactory results. Whatsoever, I could do without portability and use mysql's `INTERVAL` function. This is the query I want to make using django's ORM. `SELECT id, user_id, CAST(timestamp AS Date), WEEK(timestamp,3), WEEK(CURDATE(), 3) FROM main_userstats WHERE week(timestamp, 3) = WEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK ), 3)` how can I do this using the `extra` function in django (if it's not possible to do in any other simpler way)?