CODEWITHSUNDEEP
djangodatetimesql-order-bydjango-queryset
Bryan Veloso
Bryan Veloso

Reputation: 1595

Ordering a Django QuerySet by a datetime's month/day?

I have a list of people, each person having a birthdate, which is predictably stored in a DateField. I'm trying to create a list of those people—sorted by the month and day of their birth (disregarding the year)—to have a sort of "who's birthday is coming up" display.

I can't seem to order a QuerySet by the person's datetime.month value. Is there any way that this could be done without having to resort to coercing to a list()?

Thanks in advance and please let me know if the question needs clarification.

Upvotes: 8

Views: 9229

Answers (5)

janaki sasidhar
janaki sasidhar

Reputation: 1

Post.objects.all().order_by('date_posted__minute').reverse() where date_posted is your attribute you used in your Post model.

Post is just an example model

Upvotes: 0

Munim Munna
Munim Munna

Reputation: 17556

For django >= 2.1

You can sort the QuerySet by using month and day lookup names on DateField.

SomeModel.objects.order_by('birth_date__month', 'birth_date__day')

For django >= 1.10

Use database-function Extract to generate extra month and day columns by annotate method, then order_by these columns you can sort the QuerySet by their birthday only.

from django.db.models.functions import Extract

SomeModel.objects.annotate(
    birth_date__month = Extract('birth_date', 'month'),
    birth_date__day = Extract('birth_date', 'day')
).order_by('birth_date__month', 'birth_date__day')

For older django versions

For older django versions you can do the same using QuerySet.extra(), but you have to write database specific query.

  • MySQL

    SomeModel.objects.extra(select={
        'birth_date_month': 'MONTH(birth_date)',
        'birth_date_day': 'DAY(birth_date)'
    },
    order_by=['birth_date_month','birth_date_day']
)

  • PostgreSQL

    SomeModel.objects.extra(select={
        'birth_date_month': 'EXTRACT(MONTH FROM birth_date)',
        'birth_date_day': 'EXTRACT(DAY FROM birth_date)'
    },
    order_by=['birth_date_month','birth_date_day']
)

  • SQlite

    SomeModel.objects.extra(select={
        'birth_date_month': 'strftime("%m", birth_date)',
        'birth_date_day': 'strftime("%d", birth_date)'
    },
    order_by=['birth_date_month','birth_date_day']
)

Upvotes: 11

Fabiano Almeida
Fabiano Almeida

Reputation: 11

I tested using django 1.10.8

from django.db.models.functions import Extract
from your_project.your_app.models import Person


CHOICE_MONTH = (
    (None, '--'),
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5),
    (6, 6),
    (7, 7),
    (8, 8),
    (9, 9),
    (10, 10),
    (11, 11),
    (12, 12),
)

class PersonSearchForm(forms.Form):

    name = forms.CharField(label=u'name', required=False)
    month = forms.ChoiceField(label='month', choices=CHOICE_MONTH, required=False)

    def __init__(self, *args, **kwargs):
        self.corporation = kwargs.pop('corporation', None)
        super(PersonSearchForm, self).__init__(*args, **kwargs)

    def get_result_queryset(self):
        q = Q(corporation=self.corporation)
        if self.is_valid():
            name = self.cleaned_data['name']
            if name:
                q = q & Q(name__icontains=name)
            month = self.cleaned_data['month']
            if month:
                q = q & Q(month=int(month))

        return Person.objects.annotate(month=Extract('birthday', 'month'),
                                       day=Extract('birthday', 'day')).filter(q).order_by('month', 'day')

Upvotes: 1

Bobort
Bobort

Reputation: 3218

Newer versions of django have the lookup on DateFields and DateTimeFields. https://docs.djangoproject.com/en/1.11/ref/models/database-functions/#extract

MyModel.objects.order_by('birthday__month', 'birthday__day')

Upvotes: 2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799580

You can use QuerySet.extra() to define a month field and sort by it: 

SomeModel.objects.extra(select={'birthmonth': 'MONTH(birthdate)'},
    order_by=['birthmonth'])

Upvotes: 9

Related Questions