---
title: "Database Functions"
version: 2.0
locale: pt-br
source: https://docs.djangoproject.com/pt-br/2.0/ref/models/database-functions/
canonical: https://djangodocs.dev/pt-br/2.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](/pt-br/2.0/ref/models/expressions/), so they can be used and
combined with other expressions like [aggregate functions](/pt-br/2.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
>>> 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 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.

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

> **Changed in Django 2.0**
>
> Support for `DurationField` was added.

Given the datetime `2015-06-15 23:30:01.000321+00:00`, the built-in
`lookup_name`s return:

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

> **New in Django 1.11**

#### `lookup_name = 'week'`

#### `class ExtractQuarter(expression, tzinfo=None, **extra)`

> **New in Django 2.0**

#### `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, 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'),
...     quarter=ExtractQuarter('start_date'),
...     month=ExtractMonth('start_date'),
...     week=ExtractWeek('start_date'),
...     day=ExtractDay('start_date'),
...     weekday=ExtractWeekDay('start_date'),
... ).values('year', 'quarter', 'month', 'week', 'day', 'weekday').get(
...     end_date__year=ExtractYear('start_date'),
... )
{'year': 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'),
...     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', 'month', 'week', 'day', 'weekday', 'hour', 'minute', 'second',
... ).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'quarter': 2, 'month': 6, 'week': 25, 'day': 15, 'weekday': 2,
 'hour': 23, 'minute': 30, 'second': 1}
```

When [`USE_TZ`](/pt-br/2.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`](/pt-br/2.0/ref/contrib/postgres/functions/#django.contrib.postgres.functions.TransactionNow).

### `Trunc`

#### `class Trunc(expression, kind, output_field=None, tzinfo=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.

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
- “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
- “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, **extra)`

#### `kind = 'year'`

#### `class TruncMonth(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'month'`

#### `class TruncQuarter(expression, output_field=None, tzinfo=None, **extra)`

> **New in Django 2.0**

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

> **New in Django 1.11**
>
> #### `lookup_name = 'time'`
>
>
>
>
> #### `output_field = TimeField()`

`TruncTime` casts `expression` to a time rather than using the built-in SQL
truncate function. It’s also registered as a transform on `DateTimeField` as
`__time`.

#### `class TruncDay(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'day'`

#### `class TruncHour(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'hour'`

#### `class TruncMinute(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'minute'`

#### `class TruncSecond(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'second'`

These are logically equivalent to `Trunc('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

> **New in Django 1.11**

#### `class TruncHour(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'hour'`

#### `class TruncMinute(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'minute'`

#### `class TruncSecond(expression, output_field=None, tzinfo=None, **extra)`

#### `kind = 'second'`

These are logically equivalent to `Trunc('time_field', kind)`. They truncate
all parts of the time up to `kind` which allows grouping or filtering times
with less precision. `expression` can have an `output_field` of either
`TimeField` or `DateTimeField`.

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

## Text functions

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

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

### `StrIndex`

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

> **New in Django 2.0**

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](/pt-br/2.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
```

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

> **New in Django 2.0**

There are a number of functions to use in a
[`Window`](/pt-br/2.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.

### `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](/pt-br/2.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.

### `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](/pt-br/2.0/ref/models/expressions/#window-frames).