How do I sort my Django Query Set against today date?

Question

Does anyone know how I can sort (in this instance date) my django query set against todays date ?

class Person(models.Model):
    name = models.CharField(max_length=50)
    date = models.DateField()

My goal is to list the name and date entries. At the top of the list will be the entry with the date that is closest to todays date (day/month).

Answer 1

You can use extra queryset method to select additional data from database table.

This is example that works with MySql:

Person.objects.extra(select={
    'datediff': 'ABS(DATEDIFF(date, NOW()))'}).order_by('datediff')

DATEDIFF - returns difference in days bewteen two dates, ABS - returns absolute value. For sqlite, there is different syntax, see this answer.

EDIT: use current year

Person.objects.extra(select={
    'datediff': "ABS(DATEDIFF(CONCAT(YEAR(now()), '-', MONTH(date), '-', DAY(date)), NOW()))"}
).order_by('datediff')

EDIT 2: optimized *

from datetime import date
dayofyear = int(date.today().strftime("%j"))

datediff = 'LEAST(ABS(DAYOFYEAR(date) - %d), ABS((366 - %d + DAYOFYEAR(date))) MOD 366)' % (
      dayofyear, dayofyear
    )
Person.objects.extra(select={'datediff': datediff}).order_by('datediff')

EDIT 3: closest date after given (todays) date

    from datetime import date
    dayofyear = int(date.today().strftime("%j"))

    datediff = '(DAYOFYEAR(date) - %d + 365) MOD 365' % (
          dayofyear
        )
    Persion.objects.extra(select={'datediff': datediff}).order_by('datediff')