4

I'm trying to get make a complete query set, and now all I need is get the months between two DateTime fields from my model, it's possible to do this action in a single query set. Im not talking about filter, cause in the model for example I have two datetimeField() and now what I want to do is, gets months between this dates.

Rodrigo Espinoza
  • 380
  • 2
  • 17
  • 1
    Possible duplicate of [Django database query: How to filter objects by date range?](https://stackoverflow.com/questions/4668619/django-database-query-how-to-filter-objects-by-date-range) – ramganesh Apr 16 '18 at 12:12
  • if you perform `date1 - date2` you should get the difference i believe in seconds then you can use `strftime` function on the results to get the months – Samuel Muiruri Apr 16 '18 at 12:17

3 Answers3

5

If your database supports DurationField you can go with ExtractMonth:

from django.db import models
from django.db.models.functions import ExtractMonth

queryset = MyModel.objects.annotate(
    diff=models.ExpressionWrapper(
        models.F('date1') - models.F('date2'), output_field=models.DurationField())
    ).annotate(months=ExtractMonth('diff'))
Ivan
  • 2,469
  • 1
  • 12
  • 24
  • Could not get this to work, kept getting "Cannot extract component 'month' from DurationField 'None'." One of my dates is nullable, I think. Does that affect this? – Bradleo Aug 04 '23 at 14:37
3

The given answer didn't work for me on postgres because the diff field (DurationField) only support to Extract days function. ExtractMonth return "0".

Here the solution I've found :

queryset = MyModel.objects.annotate(
    months=(ExtractYear("date2") - ExtractYear("date1")) * 12 + (ExtractMonth("date2") - ExtractMonth("date1"))
)

Note that it only consider the difference between the first of each month and not an eventual fractional part given by the days. In this solution 2020-08-12 is considered as the same as 2020-08-01.

fabien-michel
  • 1,873
  • 19
  • 38
0

Django v4.0.4   PostgreSQL v13.4

date2_date1_months_elapsed returns how many months (as an integer) have elapsed between two dates.

created_on_months_elapsed returns how many months (as an integer) have elapsed between today and a creation date.

from django.db.models import F, IntegerField
from django.db.models.functions import Cast, ExtractDay, TruncDate
from django.utils import timezone


class MyModel(models.Model):
    date1 = models.DateTimeField()
    date2 = models.DateTimeField()
    created_on = models.DateTimeField(default=timezone.now)
    ...


queryset = MyModel.objects.annotate(
    date2_date1_days_diff=Cast(
        ExtractDay(
            TruncDate(F("date2")) - TruncDate(F("date1"))
        ),
        IntegerField(),
    ),
    date2_date1_months_elapsed=Cast(
        F("date2_date1_days_diff") / (365 / 12), IntegerField()
    ),
    created_on_days_diff=Cast(
        ExtractDay(
            TruncDate(timezone.now()) - TruncDate(F("created_on"))
        ),
        IntegerField(),
    ),
    created_on_months_elapsed=Cast(
        F("created_on_days_diff") / (365 / 12), IntegerField()
    )
)
JV conseil
  • 328
  • 5
  • 11