Django F expression for datetime objects
My model:
class Test():
date1 = models.DateTimeField()
date2 = models.DateTimeField()
I can find out objects that are date2
larger than date1
that using the following query:
Test.obejcts.filter(date2__gt=F('date1'))
I would like to find all objects that are date2
more than date1
one year old.
How to find out objects based on the difference between date1
and date2
?
source to share
Common decision:
You can annotate
date difference and then check it against 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 custom solution:
If you use 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))
source to share
You can use search and functions together: __date
TruncDate
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 you really need something like date1 = 2019-05-14
, date2 > 2020-05-14
. Then this approach is not always correct, since there are 366 days in a leap year. This problem can be solved by using functions Trunc
and Extract
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 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 29-th of February
Q(
date1__month=2,
date1__day=29,
)
)
)
|
Q(
# Use 366 days for other cases
date2__date__gt=leap_shifted_date1,
)
)
PS If you USE_TZ = True
and you are running queries in a specific time zone (for example, using timezone.activate(...)
before running USE_TZ = True
queries), then it is important to do TruncDate
before adding timedelta
, because running TruncDate(F('date1')+timedelta(...))
may give incorrect results in countries where Daylight Saving Time is performed on different dates every year ... For example:
- Some countries have switched to daylight saving time
2019-03-31
in 2019 and will switch to2020-03-29
in 2020. - Local time
2019-03-30 23:30
does not yet use DST. - Adding 366 days to it (because the next year is a leap year) will give
2020-03-30 23:30 "non-DST"
, so after "normalizing" this2020-03-31 00:30 "DST"
will become2020-03-31 00:30 "DST"
- Using
TruncDate
before adding timedelta solves the problem because the value is to date .TruncDate
Additional Information: Some countries switch to Daylight Saving Time on fixed dates, for example February 1st of each year, others may switch to "last Sunday in March", which may 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
The last seconds of the " leap year" in 2016-12-31 23:59:60.999
( 2016-12-31 23:59:60.999
) could also affect the order of TruncDate / timedelta-shift, but luckily most databases don't support leap seconds and python datetime.datetime
doesn't have this feature either
source to share