Database Functions

The classes documented below provide a way for users to use functions providedby the underlying database as annotations, aggregations, or filters in Django.Functions are also expressions, so they can be used andcombined 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 thisallows the field to have two “empty values”, but it’s important for theCoalesce example below.

Comparison and conversion functions

Cast

• class Cast(expression, output_field)
• 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

• class Coalesce(*expressions, **extra)
• Accepts a list of at least two field names or expressions and returns thefirst non-null value (note that an empty string is not considered a nullvalue). Each argument must be of a similar type, so mixing text and numberswill 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 anincorrect 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()))

Greatest

• class Greatest(*expressions, **extra)
• Accepts a list of at least two field names or expressions and returns thegreatest value. Each argument must be of a similar type, so mixing text andnumbers 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.modifiedand comment.modified.

Warning

The behavior of Greatest when one or more expression may be nullvaries between databases:

• PostgreSQL: Greatest will return the largest non-null expression,or null if all expressions are null.
• SQLite, Oracle, and MySQL: If any expression is null, Greatestwill return null.The PostgreSQL behavior can be emulated using Coalesce if you knowa sensible minimum value to provide as a default.

Least

• class Least(*expressions, **extra)
• Accepts a list of at least two field names or expressions and returns theleast value. Each argument must be of a similar type, so mixing text and numberswill result in a database error.

Warning

The behavior of Least when one or more expression may be nullvaries between databases:

• PostgreSQL: Least will return the smallest non-null expression,or null if all expressions are null.
• SQLite, Oracle, and MySQL: If any expression is null, Leastwill return null.The PostgreSQL behavior can be emulated using Coalesce if you knowa sensible maximum value to provide as a default.

NullIf

• class NullIf(expression1, expression2)
• New in Django 2.2:

Accepts two expressions and returns None if they are equal, otherwisereturns expression1.

Caveats on Oracle

Due to an Oracle convention, thisfunction returns the empty string instead of None when the expressionsare of type CharField.

Passing Value(None) to expression1 is prohibited on Oracle sinceOracle doesn’t accept NULL as the first argument.

Date functions

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

• class Extract(expression, lookup_name=None, tzinfo=None, **extra)
• 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 partof the date referenced by lookup_name as an IntegerField.Django usually uses the databases’ extract function, so you may use anylookup_name that your database supports. A tzinfo subclass, usuallyprovided 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-inlookup_names return:

• “year”: 2015
• “iso_year”: 2015
• “quarter”: 2
• “month”: 6
• “day”: 15
• “week”: 25
• “week_day”: 2
• “hour”: 23
• “minute”: 30

• “second”: 1If a different timezone like Australia/Melbourne is active in Django, thenthe datetime is converted to the timezone before the value is extracted. Thetimezone offset for Melbourne in the example date above is +10:00. The valuesreturned when this timezone is active will be the same as above except for:

• “day”: 16

• “week_day”: 3
• “hour”: 9

week_day values

The week_day lookup_type is calculated differently from mostdatabases and from Python’s standard functions. This function will return1 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 of a year is the one thatcontains the year’s first Thursday, i.e. the first week has the majority(four or more) of its days in the year. The value returned is in the range1 to 52 or 53.

Each lookup_name above has a corresponding Extract subclass (listedbelow) 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

• class ExtractYear(expression, tzinfo=None, **extra)
• • lookup_name = 'year'
• class ExtractIsoYear(expression, tzinfo=None, **extra)
• New in Django 2.2:

Returns the ISO-8601 week-numbering year.

• lookup_name = 'iso_year'
• • class ExtractMonth(expression, tzinfo=None, **extra)
• lookup_name = 'month'
• • class ExtractDay(expression, tzinfo=None, **extra)
• lookup_name = 'day'
• • class ExtractWeekDay(expression, tzinfo=None, **extra)
• lookup_name = 'week_day'
• • class ExtractWeek(expression, tzinfo=None, **extra)
• lookup_name = 'week'
• • class ExtractQuarter(expression, tzinfo=None, **extra)
• lookup_name = 'quarter'
• These are logically equivalent to Extract('datefield', lookupname). Eachclass is also a Transform registered on DateField and DateTimeFieldas (lookup_name), e.g. __year.

Since DateFields don’t have a time component, only Extract subclassesthat 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 extracts

In addition to the following, all extracts for DateField listed above mayalso be used on DateTimeFields .

• class ExtractHour(expression, tzinfo=None, **extra)
• • lookup_name = 'hour'
• class ExtractMinute(expression, tzinfo=None, **extra)
• • lookup_name = 'minute'
• class ExtractSecond(expression, tzinfo=None, **extra)
• • lookup_name = 'second'
  • These are logically equivalent to Extract('datetimefield', lookupname).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 databasein UTC. If a different timezone is active in Django, the datetime is convertedto that timezone before the value is extracted. The example below converts tothe Melbourne timezone (UTC +10:00), which changes the day, weekday, and hourvalues 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 sameway, 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}

Now

• class 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 thecurrent transaction started. Therefore for cross-database compatibility,Now() uses STATEMENT_TIMESTAMP instead. If you need the transactiontimestamp, use django.contrib.postgres.functions.TransactionNow.

Trunc

• class Trunc(expression, kind, output_field=None, tzinfo=None, is_dst=None, **extra)
• 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 tofilter or aggregate your data. For example, you can use Trunc to calculatethe number of sales per day.

Trunc takes a single expression, representing a DateField,TimeField, or DateTimeField, a kind representing a date or timepart, and an output_field that’s either DateTimeField(),TimeField(), or DateField(). It returns a datetime, date, or timedepending on output_field, with fields up to kind set to their minimumvalue. If output_field is omitted, it will default to the output_fieldof expression. A tzinfo subclass, usually provided by pytz, can bepassed to truncate a value in a specific timezone.

The is_dst parameter indicates whether or not pytz should interpretnonexistent and ambiguous datetimes in daylight saving time. By default (whenis_dst=None), pytz raises an exception for such datetimes.

New in Django 3.0:The is_dst parameter was added.

Given the datetime 2015-06-15 14:30:50.000321+00:00, the built-in kindsreturn:

• “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:00If a different timezone like Australia/Melbourne is active in Django, thenthe 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. Thevalues 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:00The year has an offset of +11:00 because the result transitioned into daylightsaving time.

Each kind above has a corresponding Trunc subclass (listed below) thatshould 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 withany fields, because the obvious lookup names are already reserved by theExtract 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

• class TruncYear(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'year'
• class TruncMonth(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'month'
• class TruncWeek(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

• Truncates to midnight on the Monday of the week.

  • kind = 'week'
• class TruncQuarter(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'quarter'
  • These are logically equivalent to Trunc('date_field', kind). They truncateall parts of the date up to kind which allows grouping or filtering dateswith less precision. expression can have an output_field of eitherDateField or DateTimeField.

Since DateFields don’t have a time component, only Trunc subclassesthat 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 truncation

• class TruncDate(expression, **extra)
• • lookup_name = 'date'
  • output_field = DateField()
  • TruncDate casts expression to a date rather than using the built-in SQLtruncate function. It’s also registered as a transform on DateTimeField as__date.

• class TruncTime(expression, **extra)

• • lookup_name = 'time'
  • output_field = TimeField()
  • TruncTime casts expression to a time rather than using the built-in SQLtruncate function. It’s also registered as a transform on DateTimeField as__time.

• class TruncDay(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

• • kind = 'day'
• class TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'hour'
• class TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'minute'
• class TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'second'
  • These are logically equivalent to Trunc('datetime_field', kind). Theytruncate all parts of the date up to kind and allow grouping or filteringdatetimes with less precision. expression must have an output_field ofDateTimeField.

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 truncation

• class TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'hour'
• class TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'minute'
• class TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
• • kind = 'second'
  • These are logically equivalent to Trunc('time_field', kind). They truncateall parts of the time up to kind which allows grouping or filtering timeswith less precision. expression can have an output_field of eitherTimeField or DateTimeField.

Since TimeFields don’t have a date component, only Trunc subclassesthat 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 Functions

New in Django 2.2:

We’ll be using the following model in math function examples:

class Vector(models.Model):
    x = models.FloatField()
    y = models.FloatField()

Abs

• class Abs(expression, **extra)
• 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)

ACos

• class ACos(expression, **extra)
• Returns the arccosine of a numeric field or expression. The expression valuemust 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)

ASin

• class ASin(expression, **extra)
• Returns the arcsine of a numeric field or expression. The expression value mustbe 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)

ATan

• class ATan(expression, **extra)
• 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)

ATan2

• class ATan2(expression1, expression2, **extra)
• 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

Ceil

• class Ceil(expression, **extra)
• Returns the smallest integer greater than or equal to a numeric field orexpression.

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)

Cos

• class Cos(expression, **extra)
• 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)

Cot

• class Cot(expression, **extra)
• 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)

Degrees

• class Degrees(expression, **extra)
• 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)

Exp

• class Exp(expression, **extra)
• Returns the value of e (the natural logarithm base) raised to the power ofa 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)

Floor

• class Floor(expression, **extra)
• Returns the largest integer value not greater than a numeric field orexpression.

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)

Ln

• class Ln(expression, **extra)
• 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)

Log

• class Log(expression1, expression2, **extra)
• Accepts two numeric fields or expressions and returns the logarithm ofthe 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

Mod

• class Mod(expression1, expression2, **extra)
• Accepts two numeric fields or expressions and returns the remainder ofthe 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

Pi

• class Pi(**extra)
• Returns the value of the mathematical constant π.

Power

• class Power(expression1, expression2, **extra)
• Accepts two numeric fields or expressions and returns the value of the firstraised 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

Radians

• class Radians(expression, **extra)
• 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)

Round

• class Round(expression, **extra)
• Rounds a numeric field or expression to the nearest integer. Whether halfvalues 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)

Sign

• class Sign(expression, **extra)
• New in Django 3.0:

Returns the sign (-1, 0, 1) of a numeric field or expression.

Usage example:

>>> from django.db.models.functions import Sign
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_sign=Sign('x'), y_sign=Sign('y')).get()
>>> vector.x_sign, vector.y_sign
(1, -1)

It can also be registered as a transform. For example:

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Sign
>>> FloatField.register_lookup(Sign)
>>> # Get vectors whose signs of components are less than 0.
>>> vectors = Vector.objects.filter(x__sign__lt=0, y__sign__lt=0)

Sin

• class Sin(expression, **extra)
• 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)

Sqrt

• class Sqrt(expression, **extra)
• 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)

Tan

• class Tan(expression, **extra)
• 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 functions

Chr

• class Chr(expression, **extra)
• Accepts a numeric field or expression and returns the text representation ofthe 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

Concat

• class Concat(*expressions, **extra)
• Accepts a list of at least two text fields or expressions and returns theconcatenated text. Each argument must be of a text or char type. If you wantto concatenate a TextField() with a CharField(), then be sure to tellDjango that the output_field should be a TextField(). Specifying anoutput_field is also required when concatenating a Value as in theexample below.

This function will never have a null result. On backends where a null argumentresults in the entire expression being null, Django will ensure that each nullpart 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)

Left

• class Left(expression, length, **extra)
• 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

Length

• class Length(expression, **extra)
• Accepts a single text field or expression and returns the number of charactersthe 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)

Lower

• class Lower(expression, **extra)
• Accepts a single text field or expression and returns the lowercaserepresentation.

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

LPad

• class LPad(expression, length, fill_text=Value(' '), **extra)
• Returns the value of the given text field or expression padded on the left sidewith 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

LTrim

• class LTrim(expression, **extra)
• Similar to Trim, but removes only leadingspaces.

MD5

• class MD5(expression, **extra)
• New in Django 3.0:

Accepts a single text field or expression and returns the MD5 hash of thestring.

It can also be registered as a transform as described in Length.

Usage example:

>>> from django.db.models.functions import MD5
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_md5=MD5('name')).get()
>>> print(author.name_md5)
749fb689816b2db85f5b169c2055b247

Ord

• class Ord(expression, **extra)
• Accepts a single text field or expression and returns the Unicode code pointvalue for the first character of that expression. It works similar to Python’sord() function, but an exception isn’t raised if the expression is morethan 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

Repeat

• class Repeat(expression, number, **extra)
• Returns the value of the given text field or expression repeated numbertimes.

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

Replace

• class Replace(expression, text, replacement=Value(''), **extra)
• Replaces all occurrences of text with replacement in expression.The default replacement text is the empty string. The arguments to the functionare 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'}]>

Reverse

• class Reverse(expression, **extra)
• New in Django 2.2:

Accepts a single text field or expression and returns the characters of thatexpression in reverse order.

It can also be registered as a transform as described in Length. Thedefault 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

Right

• class Right(expression, length, **extra)
• 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