==================

Database Functions

==================

.. module:: django.db.models.functions

    :synopsis: 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 :doc:`expressions <expressions>`, so they can be used and

combined with other expressions like :ref:`aggregate functions

<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-functions:

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

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

    >>> # The aggregate default argument uses Coalesce() under the hood.

    >>> aggregated = Author.objects.aggregate(

    ...    combined_age=Sum('age'),

    ...    combined_age_default=Sum('age', default=0),

    ...    combined_age_coalesce=Coalesce(Sum('age'), 0),

    ... )

    >>> print(aggregated['combined_age'])

    None

    >>> print(aggregated['combined_age_default'])

    0

    >>> print(aggregated['combined_age_coalesce'])

    0

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

``Collate``

-----------

.. class:: Collate(expression, collation)

Takes an expression and a collation name to query against.

For example, to filter case-insensitively in SQLite::

    >>> Author.objects.filter(name=Collate(Value('john'), 'nocase'))

    <QuerySet [<Author: John>, <Author: john>]>

It can also be used when ordering, for example with PostgreSQL::

    >>> Author.objects.order_by(Collate('name', 'et-x-icu'))

    <QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]>

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

``JSONObject``

--------------

.. class:: JSONObject(**fields)

Takes a list of key-value pairs and returns a JSON object containing those

pairs.

Usage example::

    >>> from django.db.models import F

    >>> from django.db.models.functions import JSONObject, Lower

    >>> Author.objects.create(name='Margaret Smith', alias='msmith', age=25)

    >>> author = Author.objects.annotate(json_object=JSONObject(

    ...     name=Lower('name'),

    ...     alias='alias',

    ...     age=F('age') * 2,

    ... )).get()

    >>> author.json_object

    {'name': 'margaret smith', 'alias': 'msmith', 'age': 50}

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

Accepts two expressions and returns ``None`` if they are equal, otherwise

returns ``expression1``.

.. admonition:: Caveats on Oracle

    Due to an :ref:`Oracle convention<oracle-null-empty-strings>`, this

    function returns the empty string instead of ``None`` when the expressions

    are of type :class:`~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:

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

* "iso_week_day": 1

* "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

* "iso_week_day": 2

* "hour": 9

.. admonition:: ``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

.. admonition:: ``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)

    .. attribute:: lookup_name = 'year'

.. class:: ExtractIsoYear(expression, tzinfo=None, **extra)

    Returns the ISO-8601 week-numbering year.

    .. attribute:: lookup_name = 'iso_year'

.. class:: ExtractMonth(expression, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'month'

.. class:: ExtractDay(expression, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'day'

.. class:: ExtractWeekDay(expression, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'week_day'

.. class:: ExtractIsoWeekDay(expression, tzinfo=None, **extra)

    Returns the ISO-8601 week day with day 1 being Monday and day 7 being

    Sunday.

    .. attribute:: lookup_name = 'iso_week_day'

.. class:: ExtractWeek(expression, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'week'

.. class:: ExtractQuarter(expression, tzinfo=None, **extra)

    .. attribute:: 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, timezone

    >>> from django.db.models.functions import (

    ...     ExtractDay, ExtractMonth, ExtractQuarter, ExtractWeek,

    ...     ExtractIsoWeekDay, 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'),

    ...     isoweekday=ExtractIsoWeekDay('start_date'),

    ... ).values(

    ...     'year', 'isoyear', 'quarter', 'month', 'week', 'day', 'weekday',

    ...     'isoweekday',

    ... ).get(end_date__year=ExtractYear('start_date'))

    {'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,

     'day': 15, 'weekday': 2, 'isoweekday': 1}

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

    .. attribute:: lookup_name = 'hour'

.. class:: ExtractMinute(expression, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'minute'

.. class:: ExtractSecond(expression, tzinfo=None, **extra)

    .. attribute:: 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, timezone

    >>> from django.db.models.functions import (

    ...     ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,

    ...     ExtractQuarter, ExtractSecond, ExtractWeek, ExtractIsoWeekDay,

    ...     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_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'),

    ...     isoweekday=ExtractIsoWeekDay('start_datetime'),

    ...     hour=ExtractHour('start_datetime'),

    ...     minute=ExtractMinute('start_datetime'),

    ...     second=ExtractSecond('start_datetime'),

    ... ).values(

    ...     'year', 'isoyear', 'month', 'week', 'day',

    ...     'weekday', 'isoweekday', 'hour', 'minute', 'second',

    ... ).get(end_datetime__year=ExtractYear('start_datetime'))

    {'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,

     'day': 15, 'weekday': 2, 'isoweekday': 1, 'hour': 23, 'minute': 30,

     'second': 1}

When :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::

    >>> from django.utils import timezone

    >>> import zoneinfo

    >>> melb = zoneinfo.ZoneInfo('Australia/Melbourne')  # UTC+10:00

    >>> with timezone.override(melb):

    ...    Experiment.objects.annotate(

    ...        day=ExtractDay('start_datetime'),

    ...        weekday=ExtractWeekDay('start_datetime'),

    ...        isoweekday=ExtractIsoWeekDay('start_datetime'),

    ...        hour=ExtractHour('start_datetime'),

    ...    ).values('day', 'weekday', 'isoweekday', 'hour').get(

    ...        end_datetime__year=ExtractYear('start_datetime'),

    ...    )

    {'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}

Explicitly passing the timezone to the ``Extract`` function behaves in the same

way, and takes priority over an active timezone::

    >>> import zoneinfo

    >>> melb = zoneinfo.ZoneInfo('Australia/Melbourne')

    >>> Experiment.objects.annotate(

    ...     day=ExtractDay('start_datetime', tzinfo=melb),

    ...     weekday=ExtractWeekDay('start_datetime', tzinfo=melb),

    ...     isoweekday=ExtractIsoWeekDay('start_datetime', tzinfo=melb),

    ...     hour=ExtractHour('start_datetime', tzinfo=melb),

    ... ).values('day', 'weekday', 'isoweekday', 'hour').get(

    ...     end_datetime__year=ExtractYear('start_datetime'),

    ... )

    {'day': 16, 'weekday': 3, 'isoweekday': 2, '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>]>

.. admonition:: 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 :class:`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 :mod:`zoneinfo`,

can be passed to truncate a value in a specific timezone.

.. deprecated:: 4.0

    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.

    The ``is_dst`` parameter is deprecated and will be removed in Django 5.0.

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

    .. attribute:: kind = 'year'

.. class:: TruncMonth(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    .. attribute:: kind = 'month'

.. class:: TruncWeek(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    Truncates to midnight on the Monday of the week.

    .. attribute:: kind = 'week'

.. class:: TruncQuarter(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    .. attribute:: kind = 'quarter'

.. deprecated:: 4.0

    The ``is_dst`` parameter is deprecated and will be removed in Django 5.0.

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, timezone

    >>> from django.db.models import Count

    >>> from django.db.models.functions import TruncMonth, TruncYear

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

    >>> melb = zoneinfo.ZoneInfo('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, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'date'

    .. attribute:: 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, tzinfo=None, **extra)

    .. attribute:: lookup_name = 'time'

    .. attribute:: 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)

    .. attribute:: kind = 'day'

.. class:: TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    .. attribute:: kind = 'hour'

.. class:: TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    .. attribute:: kind = 'minute'

.. class:: TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    .. attribute:: kind = 'second'

.. deprecated:: 4.0

    The ``is_dst`` parameter is deprecated and will be removed in Django 5.0.

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, timezone

    >>> from django.db.models import Count

    >>> from django.db.models.functions import (

    ...     TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,

    ... )

    >>> import zoneinfo

    >>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)

    >>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())

    >>> melb = zoneinfo.ZoneInfo('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=zoneinfo.ZoneInfo('Australia/Melbourne')),

     'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=zoneinfo.ZoneInfo('Australia/Melbourne')),

     'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=timezone.utc),

     'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=timezone.utc)

    }

``TimeField`` truncation

~~~~~~~~~~~~~~~~~~~~~~~~

.. class:: TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    :noindex:

    .. attribute:: kind = 'hour'

.. class:: TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    :noindex:

    .. attribute:: kind = 'minute'

.. class:: TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

    :noindex:

    .. attribute:: kind = 'second'

.. deprecated:: 4.0

    The ``is_dst`` parameter is deprecated and will be removed in Django 5.0.

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, timezone

    >>> from django.db.models import Count, TimeField

    >>> from django.db.models.functions import TruncHour

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

    >>> melb = zoneinfo.ZoneInfo('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:

Math Functions

==============

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)

``Random``

----------

.. class:: Random(**extra)

Returns a random value in the range ``0.0 ≤ x < 1.0``.

``Round``

---------

.. class:: Round(expression, precision=0, **extra)

Rounds a numeric field or expression to ``precision`` (must be an integer)

decimal places. By default, it rounds 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.37)

    >>> vector = Vector.objects.annotate(x_r=Round('x'), y_r=Round('y', precision=1)).get()

    >>> vector.x_r, vector.y_r

    (5.0, -2.4)

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)

.. versionchanged:: 4.0

    The ``precision`` argument was added.

``Sign``

--------

.. class:: Sign(expression, **extra)

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:

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 :func:`chr`

function.

Like :class:`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 :class:`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 :class:`~django.db.models.functions.Trim`, but removes only leading

spaces.

``MD5``

-------

.. class:: MD5(expression, **extra)

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

:func:`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 :class:`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)

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 :class:`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 :class:`~django.db.models.functions.LPad`, but pads on the right

side.

``RTrim``

---------

.. class:: RTrim(expression, **extra)

Similar to :class:`~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)

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

.. admonition:: PostgreSQL

    The `pgcrypto extension <https://www.postgresql.org/docs/current/

    pgcrypto.html>`_ must be installed. You can use the

    :class:`~django.contrib.postgres.operations.CryptoExtension` migration

    operation to install it.

.. admonition:: 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 :ref:`collation<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 :class:`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:

Window functions

================

There are a number of functions to use in a

:class:`~django.db.models.expressions.Window` expression for computing the rank

of elements or the :class:`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 :class:`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.

.. admonition:: MariaDB and ``default``

    MariaDB `doesn't support <https://jira.mariadb.org/browse/MDEV-12981>`_

    the ``default`` parameter.

``LastValue``

-------------

.. class:: LastValue(expression, **extra)

Comparable to :class:`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 :ref:`frame <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.

.. admonition:: 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 :class:`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

:ref:`window frame <window-frames>`.