Database FunctionsLink to this heading
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.
Comparison and conversion functionsLink to this heading
CastLink to this heading
- class Cast(expression, output_field)Link to this definition
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
>>> Author.objects.create(age=25, name='Margaret Smith')
>>> author = Author.objects.annotate(
... age_as_float=Cast('age', output_field=FloatField()),
... ).get()
>>> print(author.age_as_float)
25.0
CoalesceLink to this heading
- class Coalesce(*expressions, **extra)Link to this definition
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
GreatestLink to this heading
- class Greatest(*expressions, **extra)Link to this definition
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.
LeastLink to this heading
- class Least(*expressions, **extra)Link to this definition
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.
NullIfLink to this heading
- class NullIf(expression1, expression2)Link to this definition
Accepts two expressions and returns None if they are equal, otherwise
returns expression1.
Date functionsLink to this heading
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)
ExtractLink to this heading
- class Extract(expression, lookup_name=None, tzinfo=None, **extra)Link to this definition
Extracts a component of a date as a number.
Takes an expression representing a DateField, DateTimeField,
TimeField, or DurationField 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:
«year»: 2015
«iso_year»: 2015
«quarter»: 2
«month»: 6
«day»: 15
«week»: 25
«week_day»: 2
«hour»: 23
«minute»: 30
«second»: 1
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:
«day»: 16
«week_day»: 3
«hour»: 9
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 extractsLink to this heading
- class ExtractYear(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'year'
- class ExtractIsoYear(expression, tzinfo=None, **extra)Link to this definition
-
Returns the ISO-8601 week-numbering year.
- lookup_name = 'iso_year'
- class ExtractMonth(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'month'
- class ExtractDay(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'day'
- class ExtractWeekDay(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'week_day'
- class ExtractWeek(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'week'
- class ExtractQuarter(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'quarter'
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, ExtractQuarter, ExtractWeek,
... ExtractWeekDay, ExtractIsoYear, 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'),
... isoyear=ExtractIsoYear('start_date'),
... quarter=ExtractQuarter('start_date'),
... month=ExtractMonth('start_date'),
... week=ExtractWeek('start_date'),
... day=ExtractDay('start_date'),
... weekday=ExtractWeekDay('start_date'),
... ).values('year', 'isoyear', 'quarter', 'month', 'week', 'day', 'weekday').get(
... end_date__year=ExtractYear('start_date'),
... )
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
'day': 15, 'weekday': 2}
DateTimeField extractsLink to this heading
In addition to the following, all extracts for DateField listed above may
also be used on DateTimeFields .
- class ExtractHour(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'hour'
- class ExtractMinute(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'minute'
- class ExtractSecond(expression, tzinfo=None, **extra)Link to this definition
- lookup_name = 'second'
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,
... ExtractQuarter, 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'),
... isoyear=ExtractIsoYear('start_datetime'),
... quarter=ExtractQuarter('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', 'isoyear', 'month', 'week', 'day',
... 'weekday', 'hour', 'minute', 'second',
... ).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'isoyear': 2015, 'quarter': 2, '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
>>> melb = pytz.timezone('Australia/Melbourne') # UTC+10:00
>>> with timezone.override(melb):
... 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
>>> melb = 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}
NowLink to this heading
- class NowLink to this definition
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>]>
TruncLink to this heading
- class Trunc(expression, kind, output_field=None, tzinfo=None, **extra)Link to this definition
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:
«year»: 2015-01-01 00:00:00+00:00
«quarter»: 2015-04-01 00:00:00+00:00
«month»: 2015-06-01 00:00:00+00:00
«week»: 2015-06-15 00:00:00+00:00
«day»: 2015-06-15 00:00:00+00:00
«hour»: 2015-06-15 14:00:00+00:00
«minute»: 2015-06-15 14:30:00+00:00
«second»: 2015-06-15 14:30:50+00:00
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:
«year»: 2015-01-01 00:00:00+11:00
«quarter»: 2015-04-01 00:00:00+10:00
«month»: 2015-06-01 00:00:00+10:00
«week»: 2015-06-16 00:00:00+10:00
«day»: 2015-06-16 00:00:00+10:00
«hour»: 2015-06-16 00:00:00+10:00
«minute»: 2015-06-16 00:30:00+10:00
«second»: 2015-06-16 00:30:50+10:00
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 truncationLink to this heading
- class TruncYear(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- kind = 'year'
- class TruncMonth(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- kind = 'month'
- class TruncWeek(expression, output_field=None, tzinfo=None, **extra)Link to this definition
-
Truncates to midnight on the Monday of the week.
- kind = 'week'
- class TruncQuarter(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- kind = 'quarter'
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
DateTimeField truncationLink to this heading
- class TruncDate(expression, **extra)Link to this definition
- lookup_name = 'date'
- output_field = DateField()
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.
- class TruncTime(expression, **extra)Link to this definition
- 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.
- class TruncDay(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- kind = 'day'
- class TruncHour(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- kind = 'hour'
- class TruncMinute(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- kind = 'minute'
- class TruncSecond(expression, output_field=None, tzinfo=None, **extra)Link to this definition
- 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>)
}
TimeField truncationLink to this heading
- class TruncHour(expression, output_field=None, tzinfo=None, **extra)
- kind = 'hour'
- class TruncMinute(expression, output_field=None, tzinfo=None, **extra)
- kind = 'minute'
- class TruncSecond(expression, output_field=None, tzinfo=None, **extra)
- kind = 'second'
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
Math FunctionsLink to this heading
We’ll be using the following model in math function examples:
class Vector(models.Model):
x = models.FloatField()
y = models.FloatField()
AbsLink to this heading
- class Abs(expression, **extra)Link to this definition
Returns the absolute value of a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Abs
>>> Vector.objects.create(x=-0.5, y=1.1)
>>> vector = Vector.objects.annotate(x_abs=Abs('x'), y_abs=Abs('y')).get()
>>> vector.x_abs, vector.y_abs
(0.5, 1.1)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Abs
>>> FloatField.register_lookup(Abs)
>>> # Get vectors inside the unit cube
>>> vectors = Vector.objects.filter(x__abs__lt=1, y__abs__lt=1)
ACosLink to this heading
- class ACos(expression, **extra)Link to this definition
Returns the arccosine of a numeric field or expression. The expression value must be within the range -1 to 1.
Usage example:
>>> from django.db.models.functions import ACos
>>> Vector.objects.create(x=0.5, y=-0.9)
>>> vector = Vector.objects.annotate(x_acos=ACos('x'), y_acos=ACos('y')).get()
>>> vector.x_acos, vector.y_acos
(1.0471975511965979, 2.6905658417935308)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import ACos
>>> FloatField.register_lookup(ACos)
>>> # Get vectors whose arccosine is less than 1
>>> vectors = Vector.objects.filter(x__acos__lt=1, y__acos__lt=1)
ASinLink to this heading
- class ASin(expression, **extra)Link to this definition
Returns the arcsine of a numeric field or expression. The expression value must be in the range -1 to 1.
Usage example:
>>> from django.db.models.functions import ASin
>>> Vector.objects.create(x=0, y=1)
>>> vector = Vector.objects.annotate(x_asin=ASin('x'), y_asin=ASin('y')).get()
>>> vector.x_asin, vector.y_asin
(0.0, 1.5707963267948966)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import ASin
>>> FloatField.register_lookup(ASin)
>>> # Get vectors whose arcsine is less than 1
>>> vectors = Vector.objects.filter(x__asin__lt=1, y__asin__lt=1)
ATanLink to this heading
- class ATan(expression, **extra)Link to this definition
Returns the arctangent of a numeric field or expression.
Usage example:
>>> from django.db.models.functions import ATan
>>> Vector.objects.create(x=3.12, y=6.987)
>>> vector = Vector.objects.annotate(x_atan=ATan('x'), y_atan=ATan('y')).get()
>>> vector.x_atan, vector.y_atan
(1.2606282660069106, 1.428638798133829)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import ATan
>>> FloatField.register_lookup(ATan)
>>> # Get vectors whose arctangent is less than 2
>>> vectors = Vector.objects.filter(x__atan__lt=2, y__atan__lt=2)
ATan2Link to this heading
- class ATan2(expression1, expression2, **extra)Link to this definition
Returns the arctangent of expression1 / expression2.
Usage example:
>>> from django.db.models.functions import ATan2
>>> Vector.objects.create(x=2.5, y=1.9)
>>> vector = Vector.objects.annotate(atan2=ATan2('x', 'y')).get()
>>> vector.atan2
0.9209258773829491
CeilLink to this heading
- class Ceil(expression, **extra)Link to this definition
Returns the smallest integer greater than or equal to a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Ceil
>>> Vector.objects.create(x=3.12, y=7.0)
>>> vector = Vector.objects.annotate(x_ceil=Ceil('x'), y_ceil=Ceil('y')).get()
>>> vector.x_ceil, vector.y_ceil
(4.0, 7.0)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Ceil
>>> FloatField.register_lookup(Ceil)
>>> # Get vectors whose ceil is less than 10
>>> vectors = Vector.objects.filter(x__ceil__lt=10, y__ceil__lt=10)
CosLink to this heading
- class Cos(expression, **extra)Link to this definition
Returns the cosine of a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Cos
>>> Vector.objects.create(x=-8.0, y=3.1415926)
>>> vector = Vector.objects.annotate(x_cos=Cos('x'), y_cos=Cos('y')).get()
>>> vector.x_cos, vector.y_cos
(-0.14550003380861354, -0.9999999999999986)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cos
>>> FloatField.register_lookup(Cos)
>>> # Get vectors whose cosine is less than 0.5
>>> vectors = Vector.objects.filter(x__cos__lt=0.5, y__cos__lt=0.5)
CotLink to this heading
- class Cot(expression, **extra)Link to this definition
Returns the cotangent of a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Cot
>>> Vector.objects.create(x=12.0, y=1.0)
>>> vector = Vector.objects.annotate(x_cot=Cot('x'), y_cot=Cot('y')).get()
>>> vector.x_cot, vector.y_cot
(-1.5726734063976826, 0.642092615934331)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cot
>>> FloatField.register_lookup(Cot)
>>> # Get vectors whose cotangent is less than 1
>>> vectors = Vector.objects.filter(x__cot__lt=1, y__cot__lt=1)
DegreesLink to this heading
- class Degrees(expression, **extra)Link to this definition
Converts a numeric field or expression from radians to degrees.
Usage example:
>>> from django.db.models.functions import Degrees
>>> Vector.objects.create(x=-1.57, y=3.14)
>>> vector = Vector.objects.annotate(x_d=Degrees('x'), y_d=Degrees('y')).get()
>>> vector.x_d, vector.y_d
(-89.95437383553924, 179.9087476710785)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Degrees
>>> FloatField.register_lookup(Degrees)
>>> # Get vectors whose degrees are less than 360
>>> vectors = Vector.objects.filter(x__degrees__lt=360, y__degrees__lt=360)
ExpLink to this heading
- class Exp(expression, **extra)Link to this definition
Returns the value of e (the natural logarithm base) raised to the power of
a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Exp
>>> Vector.objects.create(x=5.4, y=-2.0)
>>> vector = Vector.objects.annotate(x_exp=Exp('x'), y_exp=Exp('y')).get()
>>> vector.x_exp, vector.y_exp
(221.40641620418717, 0.1353352832366127)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Exp
>>> FloatField.register_lookup(Exp)
>>> # Get vectors whose exp() is greater than 10
>>> vectors = Vector.objects.filter(x__exp__gt=10, y__exp__gt=10)
FloorLink to this heading
- class Floor(expression, **extra)Link to this definition
Returns the largest integer value not greater than a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Floor
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_floor=Floor('x'), y_floor=Floor('y')).get()
>>> vector.x_floor, vector.y_floor
(5.0, -3.0)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Floor
>>> FloatField.register_lookup(Floor)
>>> # Get vectors whose floor() is greater than 10
>>> vectors = Vector.objects.filter(x__floor__gt=10, y__floor__gt=10)
LnLink to this heading
- class Ln(expression, **extra)Link to this definition
Returns the natural logarithm a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Ln
>>> Vector.objects.create(x=5.4, y=233.0)
>>> vector = Vector.objects.annotate(x_ln=Ln('x'), y_ln=Ln('y')).get()
>>> vector.x_ln, vector.y_ln
(1.6863989535702288, 5.4510384535657)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Ln
>>> FloatField.register_lookup(Ln)
>>> # Get vectors whose value greater than e
>>> vectors = Vector.objects.filter(x__ln__gt=1, y__ln__gt=1)
LogLink to this heading
- class Log(expression1, expression2, **extra)Link to this definition
Accepts two numeric fields or expressions and returns the logarithm of the first to base of the second.
Usage example:
>>> from django.db.models.functions import Log
>>> Vector.objects.create(x=2.0, y=4.0)
>>> vector = Vector.objects.annotate(log=Log('x', 'y')).get()
>>> vector.log
2.0
ModLink to this heading
- class Mod(expression1, expression2, **extra)Link to this definition
Accepts two numeric fields or expressions and returns the remainder of the first divided by the second (modulo operation).
Usage example:
>>> from django.db.models.functions import Mod
>>> Vector.objects.create(x=5.4, y=2.3)
>>> vector = Vector.objects.annotate(mod=Mod('x', 'y')).get()
>>> vector.mod
0.8
PiLink to this heading
- class Pi(**extra)Link to this definition
Returns the value of the mathematical constant π.
PowerLink to this heading
- class Power(expression1, expression2, **extra)Link to this definition
Accepts two numeric fields or expressions and returns the value of the first raised to the power of the second.
Usage example:
>>> from django.db.models.functions import Power
>>> Vector.objects.create(x=2, y=-2)
>>> vector = Vector.objects.annotate(power=Power('x', 'y')).get()
>>> vector.power
0.25
RadiansLink to this heading
- class Radians(expression, **extra)Link to this definition
Converts a numeric field or expression from degrees to radians.
Usage example:
>>> from django.db.models.functions import Radians
>>> Vector.objects.create(x=-90, y=180)
>>> vector = Vector.objects.annotate(x_r=Radians('x'), y_r=Radians('y')).get()
>>> vector.x_r, vector.y_r
(-1.5707963267948966, 3.141592653589793)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Radians
>>> FloatField.register_lookup(Radians)
>>> # Get vectors whose radians are less than 1
>>> vectors = Vector.objects.filter(x__radians__lt=1, y__radians__lt=1)
RoundLink to this heading
- class Round(expression, **extra)Link to this definition
Rounds a numeric field or expression to the nearest integer. Whether half values are rounded up or down depends on the database.
Usage example:
>>> from django.db.models.functions import Round
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_r=Round('x'), y_r=Round('y')).get()
>>> vector.x_r, vector.y_r
(5.0, -2.0)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Round
>>> FloatField.register_lookup(Round)
>>> # Get vectors whose round() is less than 20
>>> vectors = Vector.objects.filter(x__round__lt=20, y__round__lt=20)
SinLink to this heading
- class Sin(expression, **extra)Link to this definition
Returns the sine of a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Sin
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_sin=Sin('x'), y_sin=Sin('y')).get()
>>> vector.x_sin, vector.y_sin
(-0.7727644875559871, -0.7457052121767203)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Sin
>>> FloatField.register_lookup(Sin)
>>> # Get vectors whose sin() is less than 0
>>> vectors = Vector.objects.filter(x__sin__lt=0, y__sin__lt=0)
SqrtLink to this heading
- class Sqrt(expression, **extra)Link to this definition
Returns the square root of a nonnegative numeric field or expression.
Usage example:
>>> from django.db.models.functions import Sqrt
>>> Vector.objects.create(x=4.0, y=12.0)
>>> vector = Vector.objects.annotate(x_sqrt=Sqrt('x'), y_sqrt=Sqrt('y')).get()
>>> vector.x_sqrt, vector.y_sqrt
(2.0, 3.46410)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Sqrt
>>> FloatField.register_lookup(Sqrt)
>>> # Get vectors whose sqrt() is less than 5
>>> vectors = Vector.objects.filter(x__sqrt__lt=5, y__sqrt__lt=5)
TanLink to this heading
- class Tan(expression, **extra)Link to this definition
Returns the tangent of a numeric field or expression.
Usage example:
>>> from django.db.models.functions import Tan
>>> Vector.objects.create(x=0, y=12)
>>> vector = Vector.objects.annotate(x_tan=Tan('x'), y_tan=Tan('y')).get()
>>> vector.x_tan, vector.y_tan
(0.0, -0.6358599286615808)
It can also be registered as a transform. For example:
>>> from django.db.models import FloatField
>>> from django.db.models.functions import Tan
>>> FloatField.register_lookup(Tan)
>>> # Get vectors whose tangent is less than 0
>>> vectors = Vector.objects.filter(x__tan__lt=0, y__tan__lt=0)
Text functionsLink to this heading
ChrLink to this heading
- class Chr(expression, **extra)Link to this definition
Accepts a numeric field or expression and returns the text representation of
the expression as a single character. It works the same as Python’s chr()
function.
Like Length, it can be registered as a transform on IntegerField.
The default lookup name is chr.
Usage example:
>>> from django.db.models.functions import Chr
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.filter(name__startswith=Chr(ord('M'))).get()
>>> print(author.name)
Margaret Smith
ConcatLink to this heading
- class Concat(*expressions, **extra)Link to this definition
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(). Specifying an
output_field 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)
LeftLink to this heading
- class Left(expression, length, **extra)Link to this definition
Returns the first length characters of the given text field or expression.
Usage example:
>>> from django.db.models.functions import Left
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(first_initial=Left('name', 1)).get()
>>> print(author.first_initial)
M
LengthLink to this heading
- class Length(expression, **extra)Link to this definition
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)
>>> # Get authors whose name is longer than 7 characters
>>> authors = Author.objects.filter(name__length__gt=7)
LowerLink to this heading
- class Lower(expression, **extra)Link to this definition
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
LPadLink to this heading
- class LPad(expression, length, fill_text=Value(' '), **extra)Link to this definition
Returns the value of the given text field or expression padded on the left side
with fill_text so that the resulting value is length characters long.
The default fill_text is a space.
Usage example:
>>> from django.db.models import Value
>>> from django.db.models.functions import LPad
>>> Author.objects.create(name='John', alias='j')
>>> Author.objects.update(name=LPad('name', 8, Value('abc')))
1
>>> print(Author.objects.get(alias='j').name)
abcaJohn
LTrimLink to this heading
- class LTrim(expression, **extra)Link to this definition
Similar to Trim, but removes only leading
spaces.
OrdLink to this heading
- class Ord(expression, **extra)Link to this definition
Accepts a single text field or expression and returns the Unicode code point
value for the first character of that expression. It works similar to Python’s
ord() function, but an exception isn’t raised if the expression is more
than one character long.
It can also be registered as a transform as described in Length.
The default lookup name is ord.
Usage example:
>>> from django.db.models.functions import Ord
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_code_point=Ord('name')).get()
>>> print(author.name_code_point)
77
RepeatLink to this heading
- class Repeat(expression, number, **extra)Link to this definition
Returns the value of the given text field or expression repeated number
times.
Usage example:
>>> from django.db.models.functions import Repeat
>>> Author.objects.create(name='John', alias='j')
>>> Author.objects.update(name=Repeat('name', 3))
1
>>> print(Author.objects.get(alias='j').name)
JohnJohnJohn
ReplaceLink to this heading
- class Replace(expression, text, replacement=Value(''), **extra)Link to this definition
Replaces all occurrences of text with replacement in expression.
The default replacement text is the empty string. The arguments to the function
are case-sensitive.
Usage example:
>>> from django.db.models import Value
>>> from django.db.models.functions import Replace
>>> Author.objects.create(name='Margaret Johnson')
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.update(name=Replace('name', Value('Margaret'), Value('Margareth')))
2
>>> Author.objects.values('name')
<QuerySet [{'name': 'Margareth Johnson'}, {'name': 'Margareth Smith'}]>
ReverseLink to this heading
- class Reverse(expression, **extra)Link to this definition
Accepts a single text field or expression and returns the characters of that expression in reverse order.
It can also be registered as a transform as described in Length. The
default lookup name is reverse.
Usage example:
>>> from django.db.models.functions import Reverse
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(backward=Reverse('name')).get()
>>> print(author.backward)
htimS teragraM
RightLink to this heading
- class Right(expression, length, **extra)Link to this definition
Returns the last length characters of the given text field or expression.
Usage example:
>>> from django.db.models.functions import Right
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(last_letter=Right('name', 1)).get()
>>> print(author.last_letter)
h
RPadLink to this heading
- class RPad(expression, length, fill_text=Value(' '), **extra)Link to this definition
Similar to LPad, but pads on the right
side.
RTrimLink to this heading
- class RTrim(expression, **extra)Link to this definition
Similar to Trim, but removes only trailing
spaces.
StrIndexLink to this heading
- class StrIndex(string, substring, **extra)Link to this definition
Returns a positive integer corresponding to the 1-indexed position of the first
occurrence of substring inside string, or 0 if substring is not
found.
Usage example:
>>> from django.db.models import Value as V
>>> from django.db.models.functions import StrIndex
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.create(name='Smith, Margaret')
>>> Author.objects.create(name='Margaret Jackson')
>>> Author.objects.filter(name='Margaret Jackson').annotate(
... smith_index=StrIndex('name', V('Smith'))
... ).get().smith_index
0
>>> authors = Author.objects.annotate(
... smith_index=StrIndex('name', V('Smith'))
... ).filter(smith_index__gt=0)
<QuerySet [<Author: Margaret Smith>, <Author: Smith, Margaret>]>
SubstrLink to this heading
- class Substr(expression, pos, length=None, **extra)Link to this definition
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 Lower, Substr
>>> 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
TrimLink to this heading
- class Trim(expression, **extra)Link to this definition
Returns the value of the given text field or expression with leading and trailing spaces removed.
Usage example:
>>> from django.db.models.functions import Trim
>>> Author.objects.create(name=' John ', alias='j')
>>> Author.objects.update(name=Trim('name'))
1
>>> print(Author.objects.get(alias='j').name)
John
UpperLink to this heading
- class Upper(expression, **extra)Link to this definition
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
Window functionsLink to this heading
There are a number of functions to use in a
Window expression for computing the rank
of elements or the Ntile of some rows.
CumeDistLink to this heading
- class CumeDist(*expressions, **extra)Link to this definition
Calculates the cumulative distribution of a value within a window or partition. The cumulative distribution is defined as the number of rows preceding or peered with the current row divided by the total number of rows in the frame.
DenseRankLink to this heading
- class DenseRank(*expressions, **extra)Link to this definition
Equivalent to Rank but does not have gaps.
FirstValueLink to this heading
- class FirstValue(expression, **extra)Link to this definition
Returns the value evaluated at the row that’s the first row of the window
frame, or None if no such value exists.
LagLink to this heading
- class Lag(expression, offset=1, default=None, **extra)Link to this definition
Calculates the value offset by offset, and if no row exists there, returns
default.
default must have the same type as the expression, however, this is
only validated by the database and not in Python.
LastValueLink to this heading
- class LastValue(expression, **extra)Link to this definition
Comparable to FirstValue, it calculates the last value in a given
frame clause.
LeadLink to this heading
- class Lead(expression, offset=1, default=None, **extra)Link to this definition
Calculates the leading value in a given frame. Both
offset and default are evaluated with respect to the current row.
default must have the same type as the expression, however, this is
only validated by the database and not in Python.
NthValueLink to this heading
- class NthValue(expression, nth=1, **extra)Link to this definition
Computes the row relative to the offset nth (must be a positive value)
within the window. Returns None if no row exists.
Some databases may handle a nonexistent nth-value differently. For example,
Oracle returns an empty string rather than None for character-based
expressions. Django doesn’t do any conversions in these cases.
NtileLink to this heading
- class Ntile(num_buckets=1, **extra)Link to this definition
Calculates a partition for each of the rows in the frame clause, distributing
numbers as evenly as possible between 1 and num_buckets. If the rows don’t
divide evenly into a number of buckets, one or more buckets will be represented
more frequently.
PercentRankLink to this heading
- class PercentRank(*expressions, **extra)Link to this definition
Computes the percentile rank of the rows in the frame clause. This computation is equivalent to evaluating:
(rank - 1) / (total rows - 1)
The following table explains the calculation for the percentile rank of a row:
Row # |
Value |
Rank |
Calculation |
Percent Rank |
|---|---|---|---|---|
1 |
15 |
1 |
(1-1)/(7-1) |
0.0000 |
2 |
20 |
2 |
(2-1)/(7-1) |
0.1666 |
3 |
20 |
2 |
(2-1)/(7-1) |
0.1666 |
4 |
20 |
2 |
(2-1)/(7-1) |
0.1666 |
5 |
30 |
5 |
(5-1)/(7-1) |
0.6666 |
6 |
30 |
5 |
(5-1)/(7-1) |
0.6666 |
7 |
40 |
7 |
(7-1)/(7-1) |
1.0000 |
RankLink to this heading
- class Rank(*expressions, **extra)Link to this definition
Comparable to RowNumber, this function ranks rows in the window. The
computed rank contains gaps. Use DenseRank to compute rank without
gaps.
RowNumberLink to this heading
- class RowNumber(*expressions, **extra)Link to this definition
Computes the row number according to the ordering of either the frame clause or the ordering of the whole query if there is no partitioning of the window frame.