---
title: "Database Functions"
version: 3.0
locale: es
source: https://docs.djangoproject.com/es/3.0/ref/models/database-functions/
canonical: https://djangodocs.dev/es/3.0/ref/models/database-functions/
---
# Database Functions

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](/es/3.0/ref/models/expressions/), so they can be used and
combined with other expressions like [aggregate functions](/es/3.0/ref/models/querysets/#aggregation-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 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
>>> 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
```

### `Coalesce`

#### `class Coalesce(*expressions, **extra)`

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()))
> ```

### `Greatest`

#### `class Greatest(*expressions, **extra)`

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:
>
> - 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`, `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`

#### `class Least(*expressions, **extra)`

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:
>
> - 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`, `Least`
>   will return `null`.
>
> The PostgreSQL behavior can be emulated using `Coalesce` if you know
> a 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, otherwise
returns `expression1`.

> **Caveats on Oracle**
>
> Due to an [Oracle convention](/es/3.0/ref/databases/#oracle-null-empty-strings), this
> function returns the empty string instead of `None` when the expressions
> are of type [`CharField`](/es/3.0/ref/models/fields/#django.db.models.CharField).
>
> Passing `Value(None)` to `expression1` is prohibited on Oracle since
> Oracle 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 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_name`s 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

> **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](https://en.wikipedia.org/wiki/ISO-8601), i.e.,
> a week starts on a Monday. The first week of a year is the one that
> contains 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 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

#### `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('date_field', lookup_name)`. Each
class is also a `Transform` registered on `DateField` and `DateTimeField`
as `__(lookup_name)`, e.g. `__year`.

Since `DateField`s 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` extracts

In addition to the following, all extracts for `DateField` listed above may
also be used on `DateTimeField`s .

#### `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('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`](/es/3.0/ref/settings/#std-setting-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}
```

### `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 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`](/es/3.0/ref/contrib/postgres/functions/#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 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.

The `is_dst` parameter indicates whether or not `pytz` should interpret
nonexistent and ambiguous datetimes in daylight saving time. By default (when
`is_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 `kind`s
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` 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 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 `DateField`s 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` truncation

#### `class TruncDate(expression, **extra)`

#### `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)`

#### `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, 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)`. 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` 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 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 `TimeField`s 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 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 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)
```

### `ASin`

#### `class ASin(expression, **extra)`

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)
```

### `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 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)
```

### `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 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)
```

### `Floor`

#### `class Floor(expression, **extra)`

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)
```

### `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 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
```

### `Mod`

#### `class Mod(expression1, expression2, **extra)`

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
```

### `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 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
```

### `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 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)
```

### `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 of
the expression as a single character. It works the same as Python’s [`chr()`](https://docs.python.org/3/library/functions.html#chr)
function.

Like [`Length`](#django.db.models.functions.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 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)
```

### `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 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)
```

### `Lower`

#### `class Lower(expression, **extra)`

Accepts a single text field or expression and returns the lowercase
representation.

It can also be registered as a transform as described in [`Length`](#django.db.models.functions.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 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
```

### `LTrim`

#### `class LTrim(expression, **extra)`

Similar to [`Trim`](#django.db.models.functions.Trim), but removes only leading
spaces.

### `MD5`

#### `class MD5(expression, **extra)`

> **New in Django 3.0**

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

It can also be registered as a transform as described in [`Length`](#django.db.models.functions.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 point
value for the first character of that expression. It works similar to Python’s
[`ord()`](https://docs.python.org/3/library/functions.html#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`](#django.db.models.functions.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 `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
```

### `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 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'}]>
```

### `Reverse`

#### `class Reverse(expression, **extra)`

> **New in Django 2.2**

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`](#django.db.models.functions.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
```

### `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
```

### `RPad`

#### `class RPad(expression, length, fill_text=Value(' '), **extra)`

Similar to [`LPad`](#django.db.models.functions.LPad), but pads on the right
side.

### `RTrim`

#### `class RTrim(expression, **extra)`

Similar to [`Trim`](#django.db.models.functions.Trim), but removes only trailing
spaces.

### `SHA1`, `SHA224`, `SHA256`, `SHA384`, and `SHA512`

#### `class SHA1(expression, **extra)`

#### `class SHA224(expression, **extra)`

#### `class SHA256(expression, **extra)`

#### `class SHA384(expression, **extra)`

#### `class SHA512(expression, **extra)`

> **New in Django 3.0**

Accepts a single text field or expression and returns the particular hash of
the string.

They can also be registered as transforms as described in [`Length`](#django.db.models.functions.Length).

Usage example:

```
>>> from django.db.models.functions import SHA1
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_sha1=SHA1('name')).get()
>>> print(author.name_sha1)
b87efd8a6c991c390be5a68e8a7945a7851c7e5c
```

> **PostgreSQL**
>
> The [pgcrypto extension](https://www.postgresql.org/docs/current/pgcrypto.html) must be installed. You can use the
> [`CryptoExtension`](/es/3.0/ref/contrib/postgres/operations/#django.contrib.postgres.operations.CryptoExtension) migration
> operation to install it.

> **Oracle**
>
> Oracle doesn’t support the `SHA224` function.

### `StrIndex`

#### `class StrIndex(string, substring, **extra)`

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>]>
```

> **Warning**
>
> In MySQL, a database table’s [collation](/es/3.0/ref/databases/#mysql-collation) determines
> whether string comparisons (such as the `expression` and `substring` of
> this function) are case-sensitive. Comparisons are case-insensitive by
> default.

### `Substr`

#### `class Substr(expression, pos, length=None, **extra)`

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
```

### `Trim`

#### `class Trim(expression, **extra)`

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
```

### `Upper`

#### `class Upper(expression, **extra)`

Accepts a single text field or expression and returns the uppercase
representation.

It can also be registered as a transform as described in [`Length`](#django.db.models.functions.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 functions

There are a number of functions to use in a
[`Window`](/es/3.0/ref/models/expressions/#django.db.models.expressions.Window) expression for computing the rank
of elements or the [`Ntile`](#django.db.models.functions.Ntile) of some rows.

### `CumeDist`

#### `class CumeDist(*expressions, **extra)`

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.

### `DenseRank`

#### `class DenseRank(*expressions, **extra)`

Equivalent to [`Rank`](#django.db.models.functions.Rank) but does not have gaps.

### `FirstValue`

#### `class FirstValue(expression, **extra)`

Returns the value evaluated at the row that’s the first row of the window
frame, or `None` if no such value exists.

### `Lag`

#### `class Lag(expression, offset=1, default=None, **extra)`

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.

> **MariaDB and default**
>
> MariaDB [doesn’t support](https://jira.mariadb.org/browse/MDEV-12981)
> the `default` parameter.

### `LastValue`

#### `class LastValue(expression, **extra)`

Comparable to [`FirstValue`](#django.db.models.functions.FirstValue), it calculates the last value in a given
frame clause.

### `Lead`

#### `class Lead(expression, offset=1, default=None, **extra)`

Calculates the leading value in a given [frame](/es/3.0/ref/models/expressions/#window-frames). 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.

> **MariaDB and default**
>
> MariaDB [doesn’t support](https://jira.mariadb.org/browse/MDEV-12981)
> the `default` parameter.

### `NthValue`

#### `class NthValue(expression, nth=1, **extra)`

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.

### `Ntile`

#### `class Ntile(num_buckets=1, **extra)`

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.

### `PercentRank`

#### `class PercentRank(*expressions, **extra)`

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 |

### `Rank`

#### `class Rank(*expressions, **extra)`

Comparable to `RowNumber`, this function ranks rows in the window. The
computed rank contains gaps. Use [`DenseRank`](#django.db.models.functions.DenseRank) to compute rank without
gaps.

### `RowNumber`

#### `class RowNumber(*expressions, **extra)`

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](/es/3.0/ref/models/expressions/#window-frames).