ExtractDay stopped working from 3.1 to 3.2 due to tzinfo exception

Django Internals ORM
1

I have multiple places in my application where I calculate the number of days it has been since a date field. Because this is used in the middle of filtering and QuerySet operations this must be done w/ Postgres database functions. As of 3.1 the following code worked just fine, but starting in 3.2 it throws an exception. For reference, IncidentReport is a class with a simple date field called (imaginatively) date.

# Code that dies a terrible death
temp_ban_incidents = IncidentReport.objects.all().annotate(
    time_diff=ExpressionWrapper(Now() - F("date"), output_field=DurationField())
)
temp_ban_incidents.count()  # ok so far

for incident in temp_ban_incidents:
    print(incident, incident.time_diff, type(incident.time_diff))
# this confirms that yes, we have a bunch of datetime.timedelta fields correctly calculated

temp_ban_incidents = temp_ban_incidents.annotate(
    days_since_ban=ExtractDay("time_diff", tzinfo=timezone.utc),
)
temp_ban_incidents.count()  # not ok due to the following exception:

...
  File "/Users/bixbyr/anaconda3/envs/Shelterware_v2/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 50, in setup_query
    self.select, self.klass_info, self.annotation_col_map = self.get_select()
  File "/Users/bixbyr/anaconda3/envs/Shelterware_v2/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 267, in get_select
    sql, params = self.compile(col)
  File "/Users/bixbyr/anaconda3/envs/Shelterware_v2/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 463, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/Users/bixbyr/anaconda3/envs/Shelterware_v2/lib/python3.8/site-packages/django/db/models/functions/datetime.py", line 50, in as_sql
    raise ValueError('tzinfo can only be used with DateTimeField.')
ValueError: tzinfo can only be used with DateTimeField.

I have tried removing the tzinfo argument but that produces another joyful exception when the annotation is evaluated:

function pg_catalog.timezone(unknown, interval) does not exist
LINE 1: ...D U0."season_id" = 482) GROUP BY U0."client_id")) AT TIME ZO...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

For reference, In settings we have

In settings:
USE_TZ = True
TIME_ZONE = 'US/Pacific'
US_PACIFIC_TIMEZONE = pytz.timezone('US/Pacific')  # this is the normal arg to tzinfo

I would really appreciate some help, this has been an absolute nightmare with no clear end in sight. Thanks.

2

Hi,

I can reproduce the “tzinfo can only be used with DateTimeField” error but not the other one (when removing the tzinfo argument).

Here’s the testcase I used:

from operator import attrgetter

from django.db.models import ExpressionWrapper, F
from django.db.models.fields import DurationField
from django.db.models.functions import ExtractDay, Now
from django.test import TestCase
from django.utils import timezone

from .models import IncidentReport
# Here's the model definition:
#class IncidentReport(models.Model):
#    date = models.DateField()


class ReproTestCase(TestCase):
    def test_repro(self):
        IncidentReport.objects.create(date=timezone.now().date())

        queryset = IncidentReport.objects.annotate(
            time_diff=ExpressionWrapper(Now() - F("date"), output_field=DurationField()),
            days_since_ban=ExtractDay("time_diff"),
        )
        self.assertQuerysetEqual(queryset, [0], transform=attrgetter('days_since_ban'))

I see a “group by” in the error message you shared for the “joyful exception” which makes me think you might be building a more complex query than just annotate/count. Are you able to build a small and isolated test case?

3

I guess I’d be surprised that it ever worked.

I don’t see where a DurationField would (or should) be time-zone aware.

Was there any other change beyond moving from Django 3.1 to 3.2? (Python version change? Database change?)

4

I can verify that when I change Django versions in pip the test code starts working or starts failing. The release notes for 3.2 indicated some changes to the way timezones are handled, so my best guess is that is related but I don’t know beyond that.

When you say you are surprised it ever worked, do you mean passing tzinfo as an argument or trying to annotate the number of days between two date fields? Is there a better way to do that annotation?

5

I mean by trying to apply timezone information to a DurationField.

From the docs on DurationField:

When used on PostgreSQL, the data type used is an interval

PostgreSQL does allow for extract(day from interval), the question in my mind then is whether or not Django creates the appropriate SQL for this case. (It’s probably worth trying it without the tzinfo.) If not, you might have to create your own function definition for this.

6

Sorry if I was snarky about the other exception. An unrelated package forced a Django version update and exposed this issue, so I have a bunch of people with something broken while I fix this :slight_smile: I very much appreciate the help.

The snippet was indeed extracted from a much larger and longer query, and when I do not pass tzinfo (it seems this is much more correct) I think I am hitting an error further downstream which is unrelated logic… I think.

7

Using my testcase above, I bisected the change of API (tzinfo not being allowed anymore) to ticket 31640

I haven’t dug too deep into the code of ExtractDay but my gut feeling is that Django used to silently discard the tzinfo when dealing with dates or durations (but not datetimes).
To me it looks like Django is doing the right thing here because timezone information shouldn’t be mixed with dates and durations.
However, I think it should warrant a note in the release note.

Also, the documentation is not very clear and seems to imply that tzinfo is a legal argument: Database Functions | Django documentation | Django
That should probably be fixed as well.

I’m still puzzled by the other error though (“no function matches the given name…”), I think that one might point to a bug in Django’s handling.
Can you provide the exact queryset you’re building? I understand it could be complicated, but it would help to try and reproduce the issue.

8

Thank you for your help. I was able to comment out some of the downstream stuff which was causing secondary problems once I got rid of the initial tzinfo error. I’m hoping to handle default values in subqueries better with 4.0.

(I tried to extract a minimal query which reproduced the second error but it was pretty deeply buried with a lot of nested functions adding stuff, and so wasn’t easy to separate out)