I can't use TruncHour from the documentation example

Using Django
1

Hi,

When trying to reproduce this example I get a different result than expected.

Result:

from datetime import datetime
from django.db.models import Count, TimeField
from django.db.models.functions import TruncHour
from django.utils import timezone
start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
start2 = datetime(2014, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc)
start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc)
Experiment.objects.create(start_datetime=start1, start_time=start1.time())
Experiment.objects.create(start_datetime=start2, start_time=start2.time())
Experiment.objects.create(start_datetime=start3, start_time=start3.time())
experiments_per_hour = Experiment.objects.annotate(hour=TruncHour('start_datetime', output_field=TimeField())).values('hour').annotate(experiments=Count('id'))

experiments_per_hour
>> <QuerySet [{'hour': None, 'experiments': 3}]>

I’m using sqlite database and Django 3.0.5, what can it be?

2

Not that it’s worth a whole lot, but I was able to replicate your results. I also verified that it works as advertised using PostgreSQL as the back end.

So I started looking at the generated queries.

PostgreSQL:

SELECT DATE_TRUNC('hour', "dbext_experiment"."start_datetime")::time AS "hour", COUNT("dbext_experiment"."id") AS "experiments" FROM "dbext_experiment" GROUP BY DATE_TRUNC('hour', "dbext_experiment"."start_datetime")::timer_clock:

Sqlite:

 SELECT django_time_trunc('hour', "admintest_experiment"."start_datetime") AS "hour", COUNT("admintest_experiment"."id") AS "experiments" FROM "admintest_experiment" GROUP BY django_time_trunc('hour', "admintest_experiment"."start_datetime")

The key difference appears to be the final “GROUP BY” clause, where sqlite is using a function named “django_time_trunc”, where the PostgreSQL is using DATE_TRUNC.
(I don’t know enough about either to comment, other than to say I’m sure there’s some semantic difference between the two to cause the disparate results.)

1 Like
3

I don’t know much about the internal django, let’s see if anyone can answer it. :wink:

4

Hi. It seems the Django test suite only tests without output_field set: https://github.com/django/django/blob/6461583b6cc257d25880ef9a9fd7e2125ac53ce1/tests/db_functions/datetime/test_extract_trunc.py#L910 . I think they really want DateTimeField. Can you try without? If so, I think the docs need fixing.

5

Yep, that’s it. Removing the output_field parameter in the TruncHour function allows it to work.
Replacing the output_field parameter with DateTimeField also works, and generates the same output as with no output_field parameter at all.

6

Is it just the docs that need fixing? In the paragraph above that example it states:

expression can have an output_field of either TimeField or DateTimeField.

And it does work as documented on PostgreSQL. (I don’t have an easy test-bed to try this on either MySQL or Oracle, so I don’t know its status there.)

7

Seeing that the different returns of the example happens here, the function _sqlite_time_trunc considers that the parameter dt is a time and not datetime, is this behavior expected?

self.assertEqual(DTModel.objects.filter(start_datetime=TruncHour('start_datetime', output_field=TimeField())).count(), 1)

Shouldn’t this test, for example, pass?

or using time lookup?

self.assertEqual(DTModel.objects.filter(start_datetime__time=TruncHour('start_datetime', output_field=TimeField())).count(), 1)