[FIXED] How to annotate field for the right sorting

Issue

I annotate queryset in Django like this:

q_set = q_set.annotate(
            period=Concat(
                ExtractYear(date_field),
                Value(' - '),
                ExtractMonth(date_field),
                output_field=CharField()
            ),
        )

But then I get this result:

2022 - 12
2023 - 1
2023 - 10
2023 - 11
2023 - 12
2023 - 2
2023 - 3
2023 - 4
2023 - 5
2023 - 6
2023 - 7
2023 - 8
2023 - 9

But I need to sort it in a right order...

Solution

Please don't do formatting in the database. Databases should store, manipulate and aggregate data, not present data in a nice format. Rendering data or providing it nicely is the task of a view, for example through a serializer.

You can use TruncMonth ^[Django-doc] to convert a date to a date object with the first of the month.

from django.db.models.functions import TruncMonth

q_set = q_set.annotate(period=TruncMonth(date_field))

Answered By - willeM_ Van Onsem