Django F expression on datetime objects

Question

My model is:

class Test():
   date1 = models.DateTimeField()
   date2 = models.DateTimeField()

I can find out objects whose date2 is greater than date1, using the following query:

Test.objects.filter(date2__gt=F('date1'))

I would like to find all the objects whose date2 is greater than date1 by one year.
How can I find out objects based on difference between date1 and date2?

Answer 1

General Solution:

You can annotate the date difference and then check this against the timedelta(days=365) (pretty close to what @Anonymous suggests in his comment):

Test.objects.annotate(
    duration=F('date2') - F('date1')
).filter(duration__gt=timedelta(days=365))

---

PostgreSQL Specific Solution:

If you are using PostgreSQL, there is another option derived from this answer:

from django.db.models import F, Func

Test.objects.annotate(
    duration = Func(F('date2'), F('date1'), function='age')
).filter(duration__gt=timedelta(days=365))

Answer 2

You can use __date lookup and TruncDate function together:

from django.db.models import DateField, ExpressionWrapper, F
from django.db.models.functions import TruncDate
Test.obejcts.filter(
    date2__date__gt=ExpressionWrapper(
        TruncDate(F('date1')) + datetime.timedelta(days=365),
        output_field=DateField(),
    ),
)

If what you really need is something like date1 = 2019-05-14, date2 > 2020-05-14. Then this approach is not always correct because leap year have 366 days. This issue can be solved using Trunc and Extract functions together. Different approaches are possible... For example:

from django.db.models import DateField, ExpressionWrapper, F
from django.db.models.functions import TruncDate, ExtractDay

date_field = DateField()

YEAR = timedelta(days=365)
LEAP_YEAR = timedelta(days=366)

shifted_date1 = ExpressionWrapper(
    TruncDate(F('date1')) + YEAR,
    output_field=date_field,
)

leap_shifted_date1 = ExpressionWrapper(
    TruncDate(F('date1')) + LEAP_YEAR,
    output_field=date_field,
)


qs = Test.objects.filter(
    (
        # It's ok to add 365 days if...
        Q(date2__date__gt=shifted_date1)
        &
        (
            # If day of month after 365 days is the same...
            Q(date1__day=ExtractDay(shifted_date1))
            |
            # Or it's 29-th of February
            Q(
                date1__month=2,
                date1__day=29,
            )
        )
    )
    |
    Q(
        # Use 366 days for other cases
        date2__date__gt=leap_shifted_date1,
    )
)

P.S. If you have USE_TZ = True and performing queries in specific timezone (e.g use timezone.activate(...) before executing querysets), then it's important to do TruncDate before adding timedelta, because doing TruncDate(F('date1')+timedelta(...)) may give incorrect results in countries where switch to "Daylight saving time" is performed on different dates each year. For example:

• Some country switched to DST time on 2019-03-31 in year 2019 and will switch 2020-03-29 in year 2020.
• Local time on 2019-03-30 23:30 is not using DST yet.
• Adding 366 days (because next year is a leap year) to it will give 2020-03-30 23:30 "non-DST", so after "normalization" this datetime will become 2020-03-31 00:30 "DST"
• Using TruncDate before adding timedelta solves the issue, because TruncDate casts value to date.

Extra info: some countries are switching to DST on a fixed dates e.g. on 1-st of February each year, others might be switching "on last Sunday of March" which might be a different date each year.

import pytz
import datetime

kyiv.localize(datetime.datetime(2011, 3, 28, 0, 1)) - kyiv.localize(datetime.datetime(2010, 3, 28, 0, 1))
# `datetime.timedelta(364, 82800)` is less than 365 days

P.P.S. last seconds of "leap second year" (2016-12-31 23:59:60.999) might have been affected by ordering of TruncDate/timedelta-shift too, but "fortunately" most databases do not support leap seconds, and python's datetime.datetime also lacks this feature