How to retrieve records from past weeks in django

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)?

Solution 1:

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)

Solution 2:

Look at the the filter method of Django ORM.

Basic example:

class Entry(models.Model):
  pub_date = models.DateField([...])

Entry.objects.filter(pub_date__year=2006)

But you can do more complex queries with filter like:

Entry.objects.filter(pub_date__gte=datetime.now())

As you can see, you can use datetime and other python libraries to define specific dates. Look at the documentation for field lookups to see which posibilities you have.

In your case you could do something like this (inspired by this Stackoverflow post):

from datetime import date, timedelta

d=date.today()-timedelta(days=7)
Entry.objects.filter(pub_date__gte=d)

I'm not a 100% sure if this lookup will work, but it is the right direction.