Refs #28643 -- Reorganized database functions docs.

Thanks Tim Graham for the review.

1 files changed, 232 insertions, 223 deletions

diff --git a/docs/ref/models/database-functions.txt b/docs/ref/models/database-functions.txt
index 5b0c77aa9e..acb22249c5 100644
--- a/docs/ref/models/database-functions.txt
+++ b/docs/ref/models/database-functions.txt
@@ -23,8 +23,13 @@ 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)
 
@@ -40,7 +45,7 @@ Usage example::
     4.0
 
 ``Coalesce``
-============
+------------
 
 .. class:: Coalesce(*expressions, **extra)
 
@@ -80,39 +85,8 @@ Usage examples::
     >>> now = timezone.now()
     >>> Coalesce('updated', Cast(now, DateTimeField()))
 
-``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)
-
 ``Greatest``
-============
+------------
 
 .. class:: Greatest(*expressions, **extra)
 
@@ -154,7 +128,7 @@ and ``comment.modified``.
     a sensible minimum value to provide as a default.
 
 ``Least``
-=========
+---------
 
 .. class:: Least(*expressions, **extra)
 
@@ -175,148 +149,11 @@ will result in a database error.
     The PostgreSQL behavior can be emulated using ``Coalesce`` if you know
     a sensible maximum value to provide as a default.
 
-``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 :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
-
-``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`.
-
-``StrIndex``
-============
-
-.. class:: StrIndex(string, substring, **extra)
-
-.. versionadded:: 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 :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 Substr, Lower
-    >>> 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 :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
+.. _date-functions:
 
-Date Functions
+Date functions
 ==============
 
-.. module:: django.db.models.functions.datetime
-
 We'll be using the following model in examples of each function::
 
     class Experiment(models.Model):
@@ -554,6 +391,26 @@ way, and takes priority over an active timezone::
     ... )
     {'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>]>
+
+.. 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``
 ---------
@@ -692,6 +549,74 @@ that deal with date-parts can be used with ``DateField``::
     2016-01-01 00:00:00+11:00 1
     2014-06-01 00:00:00+10:00 1
 
+``DateTimeField`` truncation
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. class:: TruncDate(expression, **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, **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, **extra)
+
+    .. attribute:: kind = 'day'
+
+.. class:: TruncHour(expression, output_field=None, tzinfo=None, **extra)
+
+    .. attribute:: kind = 'hour'
+
+.. class:: TruncMinute(expression, output_field=None, tzinfo=None, **extra)
+
+    .. attribute:: kind = 'minute'
+
+.. class:: TruncSecond(expression, output_field=None, tzinfo=None, **extra)
+
+    .. attribute:: 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
 ~~~~~~~~~~~~~~~~~~~~~~~~
 
@@ -745,73 +670,157 @@ that deal with time-parts can be used with ``TimeField``::
     2014-06-16 00:00:00+10:00 2
     2016-01-01 04:00:00+11:00 1
 
-``DateTimeField`` truncation
-~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+.. _text-functions:
 
-.. class:: TruncDate(expression, **extra)
+Text functions
+==============
 
-    .. attribute:: lookup_name = 'date'
-    .. attribute:: output_field = DateField()
+``Concat``
+----------
 
-``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:: Concat(*expressions, **extra)
 
-.. class:: TruncTime(expression, **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.
 
-    .. attribute:: lookup_name = 'time'
-    .. attribute:: output_field = TimeField()
+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.
 
-``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``.
+Usage example::
 
-.. class:: TruncDay(expression, output_field=None, tzinfo=None, **extra)
+    >>> # 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)
 
-    .. attribute:: kind = 'day'
+``Length``
+----------
 
-.. class:: TruncHour(expression, output_field=None, tzinfo=None, **extra)
+.. class:: Length(expression, **extra)
 
-    .. attribute:: kind = 'hour'
+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.
 
-.. class:: TruncMinute(expression, output_field=None, tzinfo=None, **extra)
+Usage example::
 
-    .. attribute:: kind = 'minute'
+    >>> # 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)
 
-.. class:: TruncSecond(expression, output_field=None, tzinfo=None, **extra)
+It can also be registered as a transform. For example::
 
-    .. attribute:: kind = 'second'
+    >>> 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)
 
-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``.
+``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 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>)
-    }
+    >>> 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)
+
+.. versionadded:: 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 :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 Substr, Lower
+    >>> 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 :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: