Extract parts of temporal values (date, time, duration)Fri 13 October 2017
Temporal values in Python (from the ‘datetime’ library) represent dates, instants, and durations. The Django temporal field types represent those values on a data model.
What if we want only a part of a temporal value, such as the month, the day-of-week, or the minute-of-hour?
Query expressions and database functions
Django allows constructing a query – a filter, annotation, aggregation, etc. – by expressing the parts of the query in Python objects and combining them together in a query expression.
Query expressions describe a value or a computation that can be used as part of an update, create, filter, order by, annotation, or aggregate. […] Expressions can be combined, or in some cases nested, to form more complex computations.
Django temporal fields
These temporal data field types are used like any other field type when defining a model:
from django.db import models class Video(models.Model): """ A video shot with the person's camera. """ title = models.CharField( null=True, help_text="The display title of this video.") recorded = models.DateTimeField( help_text="The timestamp when this video was recorded.") duration = models.DurationField( help_text="The duration of the video content.")
Extract and Trunc in queries
Video.objects.annotate( recorded_year=models.functions.Extract('recorded', 'year'), recorded_hour=models.functions.Trunc('recorded', 'hour'), duration_hours=models.functions.Trunc('duration', 'hour'), )
This produces a queryset with the added fields:
- recorded_year with only the year (integer) value of the recorded field.
- recorded_hour with the hour-of-day (a datetime.datetime) of the recorded field; that is, the value is truncated down so that any parts smaller than an hour are zero.
- duration_hours with the hours (a datetime.timedelta) of the duration field; again, any parts smaller than an hour are zero.
Can I get a quarter?
The capability of these types is increased in Django 2.0 changes to the models, to include the “quarter” detail level for both DateTimeField and DurationField, in the Extract and Trunc function types.
Video.objects.annotate( recorded_quarter=models.functions.Extract('recorded', 'quarter'), )
The “quarter” of a temporal value is the calendar quarter which the value occupies. That is, if it occupies January–March it is in quarter 1, if it occupies April–June it is in quarter 2, etc.
Django 2.0 makes these improvements:
- The quarter level of detail is now included, as we saw above.
- Helper types for expressing “quarter” are added, in the ExtractQuarter and TruncQuarter types.
- The Extract type, originally designed for DateTimeField (and related types), can not also operate on DurationField values.
Only in supported database back-ends
Like the rest of Django's query construction features, the Python types your code uses are translated behind the scenes into the database engine's language (SQL) and the work is done there.
That means these features can only be used if your specified database back-end has support for the detail level you want to query. The Oracle and PostgreSQL engines both support all these features in Django 2.0.
Do you have questions about Django, or want to hire me for some work in a Django-related project? Come chat to me either privately at my Matrix address @bignose:matrix.org, or in the public Django chat channel.