The classes documented below provide a way for users to use functions provided by the underlying database as annotations, aggregations, or filters in Django. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.
We’ll be using the following model in examples of each function:
class Author(models.Model):
name = models.CharField(max_length=50)
age = models.PositiveIntegerField(null=True, blank=True)
alias = models.CharField(max_length=50, null=True, blank=True)
goes_by = models.CharField(max_length=50, null=True, blank=True)
We don’t usually recommend allowing null=True for CharField since this
allows the field to have two “empty values”, but it’s important for the
Coalesce example below.
Cast¶Forces the result type of expression to be the one from output_field.
Usage example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Value.objects.create(integer=4)
>>> value = Value.objects.annotate(as_float=Cast('integer', FloatField())).get()
>>> print(value.as_float)
4.0
Coalesce¶Accepts a list of at least two field names or expressions and returns the first non-null value (note that an empty string is not considered a null value). Each argument must be of a similar type, so mixing text and numbers will result in a database error.
Usage examples:
>>> # Get a screen name from least to most public
>>> from django.db.models import Sum, Value as V
>>> from django.db.models.functions import Coalesce
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
... screen_name=Coalesce('alias', 'goes_by', 'name')).get()
>>> print(author.screen_name)
Maggie
>>> # Prevent an aggregate Sum() from returning None
>>> aggregated = Author.objects.aggregate(
... combined_age=Coalesce(Sum('age'), V(0)),
... combined_age_default=Sum('age'))
>>> print(aggregated['combined_age'])
0
>>> print(aggregated['combined_age_default'])
None
Warning
A Python value passed to Coalesce on MySQL may be converted to an
incorrect type unless explicitly cast to the correct database type:
>>> from django.db.models import DateTimeField
>>> from django.db.models.functions import Cast, Coalesce
>>> from django.utils import timezone
>>> now = timezone.now()
>>> Coalesce('updated', Cast(now, DateTimeField()))
Concat¶Accepts a list of at least two text fields or expressions and returns the
concatenated text. Each argument must be of a text or char type. If you want
to concatenate a TextField() with a CharField(), then be sure to tell
Django that the output_field should be a TextField(). This is also
required when concatenating a Value as in the example below.
This function will never have a null result. On backends where a null argument results in the entire expression being null, Django will ensure that each null part is converted to an empty string first.
Usage example:
>>> # Get the display name as "name (goes_by)"
>>> from django.db.models import CharField, Value as V
>>> from django.db.models.functions import Concat
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
... screen_name=Concat('name', V(' ('), 'goes_by', V(')'),
... output_field=CharField())).get()
>>> print(author.screen_name)
Margaret Smith (Maggie)
Greatest¶Accepts a list of at least two field names or expressions and returns the greatest value. Each argument must be of a similar type, so mixing text and numbers will result in a database error.
Usage example:
class Blog(models.Model):
body = models.TextField()
modified = models.DateTimeField(auto_now=True)
class Comment(models.Model):
body = models.TextField()
modified = models.DateTimeField(auto_now=True)
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
>>> from django.db.models.functions import Greatest
>>> blog = Blog.objects.create(body='Greatest is the best.')
>>> comment = Comment.objects.create(body='No, Least is better.', blog=blog)
>>> comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
>>> annotated_comment = comments.get()
annotated_comment.last_updated will be the most recent of blog.modified
and comment.modified.
Warning
The behavior of Greatest when one or more expression may be null
varies between databases:
Greatest will return the largest non-null expression,
or null if all expressions are null.null, Greatest
will return null.The PostgreSQL behavior can be emulated using Coalesce if you know
a sensible minimum value to provide as a default.
Least¶Accepts a list of at least two field names or expressions and returns the least value. Each argument must be of a similar type, so mixing text and numbers will result in a database error.
Warning
The behavior of Least when one or more expression may be null
varies between databases:
Least will return the smallest non-null expression,
or null if all expressions are null.null, Least
will return null.The PostgreSQL behavior can be emulated using Coalesce if you know
a sensible maximum value to provide as a default.
Length¶Accepts a single text field or expression and returns the number of characters the value has. If the expression is null, then the length will also be null.
Usage example:
>>> # Get the length of the name and goes_by fields
>>> from django.db.models.functions import Length
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(
... name_length=Length('name'),
... goes_by_length=Length('goes_by')).get()
>>> print(author.name_length, author.goes_by_length)
(14, None)
It can also be registered as a transform. For example:
>>> from django.db.models import CharField
>>> from django.db.models.functions import Length
>>> CharField.register_lookup(Length, 'length')
>>> # Get authors whose name is longer than 7 characters
>>> authors = Author.objects.filter(name__length__gt=7)
Lower¶Accepts a single text field or expression and returns the lowercase representation.
It can also be registered as a transform as described in Length.
Usage example:
>>> from django.db.models.functions import Lower
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_lower=Lower('name')).get()
>>> print(author.name_lower)
margaret smith
Now¶Returns the database server’s current date and time when the query is executed,
typically using the SQL CURRENT_TIMESTAMP.
Usage example:
>>> from django.db.models.functions import Now
>>> Article.objects.filter(published__lte=Now())
<QuerySet [<Article: How to Django>]>
PostgreSQL considerations
On PostgreSQL, the SQL CURRENT_TIMESTAMP returns the time that the
current transaction started. Therefore for cross-database compatibility,
Now() uses STATEMENT_TIMESTAMP instead. If you need the transaction
timestamp, use django.contrib.postgres.functions.TransactionNow.
Substr¶Returns a substring of length length from the field or expression starting
at position pos. The position is 1-indexed, so the position must be greater
than 0. If length is None, then the rest of the string will be returned.
Usage example:
>>> # Set the alias to the first 5 characters of the name as lowercase
>>> from django.db.models.functions import Substr, Lower
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.update(alias=Lower(Substr('name', 1, 5)))
1
>>> print(Author.objects.get(name='Margaret Smith').alias)
marga
Upper¶Accepts a single text field or expression and returns the uppercase representation.
It can also be registered as a transform as described in Length.
Usage example:
>>> from django.db.models.functions import Upper
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_upper=Upper('name')).get()
>>> print(author.name_upper)
MARGARET SMITH
We’ll be using the following model in examples of each function:
class Experiment(models.Model):
start_datetime = models.DateTimeField()
start_date = models.DateField(null=True, blank=True)
start_time = models.TimeField(null=True, blank=True)
end_datetime = models.DateTimeField(null=True, blank=True)
end_date = models.DateField(null=True, blank=True)
end_time = models.TimeField(null=True, blank=True)
Extract¶Extracts a component of a date as a number.
Takes an expression representing a DateField or DateTimeField and a
lookup_name, and returns the part of the date referenced by lookup_name
as an IntegerField. Django usually uses the databases’ extract function, so
you may use any lookup_name that your database supports. A tzinfo
subclass, usually provided by pytz, can be passed to extract a value in a
specific timezone.
Given the datetime 2015-06-15 23:30:01.000321+00:00, the built-in
lookup_names return:
If a different timezone like Australia/Melbourne is active in Django, then
the datetime is converted to the timezone before the value is extracted. The
timezone offset for Melbourne in the example date above is +10:00. The values
returned when this timezone is active will be the same as above except for:
week_day values
The week_day lookup_type is calculated differently from most
databases and from Python’s standard functions. This function will return
1 for Sunday, 2 for Monday, through 7 for Saturday.
The equivalent calculation in Python is:
>>> from datetime import datetime
>>> dt = datetime(2015, 6, 15)
>>> (dt.isoweekday() % 7) + 1
2
week values
The week lookup_type is calculated based on ISO-8601, i.e.,
a week starts on a Monday. The first week is the one with the majority
of the days, i.e., a week that starts on or before Thursday. The value
returned is in the range 1 to 52 or 53.
Each lookup_name above has a corresponding Extract subclass (listed
below) that should typically be used instead of the more verbose equivalent,
e.g. use ExtractYear(...) rather than Extract(..., lookup_name='year').
Usage example:
>>> from datetime import datetime
>>> from django.db.models.functions import Extract
>>> start = datetime(2015, 6, 15)
>>> end = datetime(2015, 7, 2)
>>> Experiment.objects.create(
... start_datetime=start, start_date=start.date(),
... end_datetime=end, end_date=end.date())
>>> # Add the experiment start year as a field in the QuerySet.
>>> experiment = Experiment.objects.annotate(
... start_year=Extract('start_datetime', 'year')).get()
>>> experiment.start_year
2015
>>> # How many experiments completed in the same year in which they started?
>>> Experiment.objects.filter(
... start_datetime__year=Extract('end_datetime', 'year')).count()
1
DateField extracts¶ExtractWeek(expression, tzinfo=None, **extra)[source]¶lookup_name = 'week'These are logically equivalent to Extract('date_field', lookup_name). Each
class is also a Transform registered on DateField and DateTimeField
as __(lookup_name), e.g. __year.
Since DateFields don’t have a time component, only Extract subclasses
that deal with date-parts can be used with DateField:
>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
... ExtractDay, ExtractMonth, ExtractWeek, ExtractWeekDay, ExtractYear,
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
... start_datetime=start_2015, start_date=start_2015.date(),
... end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
... year=ExtractYear('start_date'),
... month=ExtractMonth('start_date'),
... week=ExtractWeek('start_date'),
... day=ExtractDay('start_date'),
... weekday=ExtractWeekDay('start_date'),
... ).values('year', 'month', 'week', 'day', 'weekday').get(
... end_date__year=ExtractYear('start_date'),
... )
{'year': 2015, 'month': 6, 'week': 25, 'day': 15, 'weekday': 2}
DateTimeField extracts¶In addition to the following, all extracts for DateField listed above may
also be used on DateTimeFields .
These are logically equivalent to Extract('datetime_field', lookup_name).
Each class is also a Transform registered on DateTimeField as
__(lookup_name), e.g. __minute.
DateTimeField examples:
>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
... ExtractDay, ExtractHour, ExtractMinute, ExtractMonth, ExtractSecond,
... ExtractWeek, ExtractWeekDay, ExtractYear,
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
... start_datetime=start_2015, start_date=start_2015.date(),
... end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
... year=ExtractYear('start_datetime'),
... month=ExtractMonth('start_datetime'),
... week=ExtractWeek('start_datetime'),
... day=ExtractDay('start_datetime'),
... weekday=ExtractWeekDay('start_datetime'),
... hour=ExtractHour('start_datetime'),
... minute=ExtractMinute('start_datetime'),
... second=ExtractSecond('start_datetime'),
... ).values(
... 'year', 'month', 'week', 'day', 'weekday', 'hour', 'minute', 'second',
... ).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'month': 6, 'week': 25, 'day': 15, 'weekday': 2, 'hour': 23,
'minute': 30, 'second': 1}
When USE_TZ is True then datetimes are stored in the database
in UTC. If a different timezone is active in Django, the datetime is converted
to that timezone before the value is extracted. The example below converts to
the Melbourne timezone (UTC +10:00), which changes the day, weekday, and hour
values that are returned:
>>> import pytz
>>> tzinfo = pytz.timezone('Australia/Melbourne') # UTC+10:00
>>> with timezone.override(tzinfo):
... Experiment.objects.annotate(
... day=ExtractDay('start_datetime'),
... weekday=ExtractWeekDay('start_datetime'),
... hour=ExtractHour('start_datetime'),
... ).values('day', 'weekday', 'hour').get(
... end_datetime__year=ExtractYear('start_datetime'),
... )
{'day': 16, 'weekday': 3, 'hour': 9}
Explicitly passing the timezone to the Extract function behaves in the same
way, and takes priority over an active timezone:
>>> import pytz
>>> tzinfo = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
... day=ExtractDay('start_datetime', tzinfo=melb),
... weekday=ExtractWeekDay('start_datetime', tzinfo=melb),
... hour=ExtractHour('start_datetime', tzinfo=melb),
... ).values('day', 'weekday', 'hour').get(
... end_datetime__year=ExtractYear('start_datetime'),
... )
{'day': 16, 'weekday': 3, 'hour': 9}
Trunc¶Truncates a date up to a significant component.
When you only care if something happened in a particular year, hour, or day,
but not the exact second, then Trunc (and its subclasses) can be useful to
filter or aggregate your data. For example, you can use Trunc to calculate
the number of sales per day.
Trunc takes a single expression, representing a DateField,
TimeField, or DateTimeField, a kind representing a date or time
part, and an output_field that’s either DateTimeField(),
TimeField(), or DateField(). It returns a datetime, date, or time
depending on output_field, with fields up to kind set to their minimum
value. If output_field is omitted, it will default to the output_field
of expression. A tzinfo subclass, usually provided by pytz, can be
passed to truncate a value in a specific timezone.
Given the datetime 2015-06-15 14:30:50.000321+00:00, the built-in kinds
return:
If a different timezone like Australia/Melbourne is active in Django, then
the datetime is converted to the new timezone before the value is truncated.
The timezone offset for Melbourne in the example date above is +10:00. The
values returned when this timezone is active will be:
The year has an offset of +11:00 because the result transitioned into daylight saving time.
Each kind above has a corresponding Trunc subclass (listed below) that
should typically be used instead of the more verbose equivalent,
e.g. use TruncYear(...) rather than Trunc(..., kind='year').
The subclasses are all defined as transforms, but they aren’t registered with
any fields, because the obvious lookup names are already reserved by the
Extract subclasses.
Usage example:
>>> from datetime import datetime
>>> from django.db.models import Count, DateTimeField
>>> from django.db.models.functions import Trunc
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321))
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123))
>>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999))
>>> experiments_per_day = Experiment.objects.annotate(
... start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).values('start_day').annotate(experiments=Count('id'))
>>> for exp in experiments_per_day:
... print(exp['start_day'], exp['experiments'])
...
2015-06-15 00:00:00 2
2015-12-25 00:00:00 1
>>> experiments = Experiment.objects.annotate(
... start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).filter(start_day=datetime(2015, 6, 15))
>>> for exp in experiments:
... print(exp.start_datetime)
...
2015-06-15 14:30:50.000321
2015-06-15 14:40:02.000123
DateField truncation¶These are logically equivalent to Trunc('date_field', kind). They truncate
all parts of the date up to kind which allows grouping or filtering dates
with less precision. expression can have an output_field of either
DateField or DateTimeField.
Since DateFields don’t have a time component, only Trunc subclasses
that deal with date-parts can be used with DateField:
>>> from datetime import datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import TruncMonth, TruncYear
>>> from django.utils import timezone
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> start2 = datetime(2015, 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_date=start1.date())
>>> Experiment.objects.create(start_datetime=start2, start_date=start2.date())
>>> Experiment.objects.create(start_datetime=start3, start_date=start3.date())
>>> experiments_per_year = Experiment.objects.annotate(
... year=TruncYear('start_date')).values('year').annotate(
... experiments=Count('id'))
>>> for exp in experiments_per_year:
... print(exp['year'], exp['experiments'])
...
2014-01-01 1
2015-01-01 2
>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> experiments_per_month = Experiment.objects.annotate(
... month=TruncMonth('start_datetime', tzinfo=melb)).values('month').annotate(
... experiments=Count('id'))
>>> for exp in experiments_per_month:
... print(exp['month'], exp['experiments'])
...
2015-06-01 00:00:00+10:00 1
2016-01-01 00:00:00+11:00 1
2014-06-01 00:00:00+10:00 1
TimeField truncation¶These are logically equivalent to Trunc('time_field', kind). They truncate
all parts of the time up to kind which allows grouping or filtering times
with less precision. expression can have an output_field of either
TimeField or DateTimeField.
Since TimeFields don’t have a date component, only Trunc subclasses
that deal with time-parts can be used with TimeField:
>>> 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'))
>>> for exp in experiments_per_hour:
... print(exp['hour'], exp['experiments'])
...
14:00:00 2
17:00:00 1
>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> experiments_per_hour = Experiment.objects.annotate(
... hour=TruncHour('start_datetime', tzinfo=melb),
... ).values('hour').annotate(experiments=Count('id'))
>>> for exp in experiments_per_hour:
... print(exp['hour'], exp['experiments'])
...
2014-06-16 00:00:00+10:00 2
2016-01-01 04:00:00+11:00 1
DateTimeField truncation¶TruncDate casts expression to a date rather than using the built-in SQL
truncate function. It’s also registered as a transform on DateTimeField as
__date.
lookup_name = 'time'output_field = TimeField()TruncTime casts expression to a time rather than using the built-in SQL
truncate function. It’s also registered as a transform on DateTimeField as
__time.
TruncHour(expression, output_field=None, tzinfo=None, **extra)[source]kind = 'hour'TruncMinute(expression, output_field=None, tzinfo=None, **extra)[source]kind = 'minute'TruncSecond(expression, output_field=None, tzinfo=None, **extra)[source]kind = 'second'These are logically equivalent to Trunc('datetime_field', kind). They
truncate all parts of the date up to kind and allow grouping or filtering
datetimes with less precision. expression must have an output_field of
DateTimeField.
Usage example:
>>> from datetime import date, datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import (
... TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,
... )
>>> from django.utils import timezone
>>> import pytz
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
... date=TruncDate('start_datetime'),
... day=TruncDay('start_datetime', tzinfo=melb),
... hour=TruncHour('start_datetime', tzinfo=melb),
... minute=TruncMinute('start_datetime'),
... second=TruncSecond('start_datetime'),
... ).values('date', 'day', 'hour', 'minute', 'second').get()
{'date': datetime.date(2014, 6, 15),
'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=<UTC>),
'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=<UTC>)
}
Jun 22, 2017