Fixed #25774 -- Refactor datetime expressions into public API

2 files changed, 410 insertions, 0 deletions

diff --git a/docs/ref/models/database-functions.txt b/docs/ref/models/database-functions.txt
index cd2d1ee368..f6db325540 100644
--- a/docs/ref/models/database-functions.txt
+++ b/docs/ref/models/database-functions.txt
@@ -293,3 +293,402 @@ Usage example::
 .. versionchanged:: 1.9
 
     The ability to register the function as a transform was added.
+
+Date Functions
+==============
+
+.. module:: django.db.models.functions.datetime
+
+.. versionadded:: 1.10
+
+We'll be using the following model in examples of each function::
+
+    class Experiment(models.Model):
+        start_time = models.DateTimeField()
+        start_date = models.DateField(null=True, blank=True)
+        end_time = models.DateTimeField(null=True, blank=True)
+        end_date = models.DateField(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`` or ``DateTimeField`` and a
+``lookup_name``, and returns the part of the date referenced by ``lookup_name``
+as an ``IntegerField``. Django usually uses the databases' extract function, so
+you may use any ``lookup_name`` that your database supports. A ``tzinfo``
+subclass, usually provided by ``pytz``, can be passed to extract a value in a
+specific timezone.
+
+Given the datetime ``2015-06-15 23:30:01.000321+00:00``, the built-in
+``lookup_name``\s return:
+
+* "year": 2015
+* "month": 6
+* "day": 15
+* "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
+
+.. 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
+
+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_time=start, start_date=start.date(),
+    ...    end_time=end, end_date=end.date())
+    >>> # Add the experiment start year as a field in the QuerySet.
+    >>> experiment = Experiment.objects.annotate(
+    ...    start_year=Extract('start_time', 'year')).get()
+    >>> experiment.start_year
+    2015
+    >>> # How many experiments completed in the same year in which they started?
+    >>> Experiment.objects.filter(
+    ...    start_time__year=Extract('end_time', 'year')).count()
+    1
+
+``DateField`` extracts
+~~~~~~~~~~~~~~~~~~~~~~
+
+.. class:: ExtractYear(expression, tzinfo=None, **extra)
+
+    .. attribute:: lookup_name = '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'
+
+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 (
+    ...    ExtractYear, ExtractMonth, ExtractDay, ExtractWeekDay
+    ... )
+    >>> 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_time=start_2015, start_date=start_2015.date(),
+    ...    end_time=end_2015, end_date=end_2015.date())
+    >>> Experiment.objects.annotate(
+    ...     year=ExtractYear('start_date'),
+    ...     month=ExtractMonth('start_date'),
+    ...     day=ExtractDay('start_date'),
+    ...     weekday=ExtractWeekDay('start_date'),
+    ... ).values('year', 'month', 'day', 'weekday').get(
+    ...     end_date__year=ExtractYear('start_date'),
+    ... )
+    {'year': 2015, 'month': 6, '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)
+
+    .. 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
+    >>> from django.utils import timezone
+    >>> from django.db.models.functions import (
+    ...    ExtractYear, ExtractMonth, ExtractDay, ExtractWeekDay,
+    ...    ExtractHour, ExtractMinute, ExtractSecond,
+    ... )
+    >>> 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_time=start_2015, start_date=start_2015.date(),
+    ...    end_time=end_2015, end_date=end_2015.date())
+    >>> Experiment.objects.annotate(
+    ...     year=ExtractYear('start_time'),
+    ...     month=ExtractMonth('start_time'),
+    ...     day=ExtractDay('start_time'),
+    ...     weekday=ExtractWeekDay('start_time'),
+    ...     hour=ExtractHour('start_time'),
+    ...     minute=ExtractMinute('start_time'),
+    ...     second=ExtractSecond('start_time'),
+    ... ).values(
+    ...     'year', 'month', 'day', 'weekday', 'hour', 'minute', 'second',
+    ... ).get(end_time__year=ExtractYear('start_time'))
+    {'year': 2015, 'month': 6, 'day': 15, 'weekday': 2, '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::
+
+    >>> import pytz
+    >>> tzinfo = pytz.timezone('Australia/Melbourne')  # UTC+10:00
+    >>> with timezone.override(tzinfo):
+    ...    Experiment.objects.annotate(
+    ...        day=ExtractDay('start_time'),
+    ...        weekday=ExtractWeekDay('start_time'),
+    ...        hour=ExtractHour('start_time'),
+    ...    ).values('day', 'weekday', 'hour').get(
+    ...        end_time__year=ExtractYear('start_time'),
+    ...    )
+    {'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
+    >>> tzinfo = pytz.timezone('Australia/Melbourne')
+    >>> Experiment.objects.annotate(
+    ...     day=ExtractDay('start_time', tzinfo=melb),
+    ...     weekday=ExtractWeekDay('start_time', tzinfo=melb),
+    ...     hour=ExtractHour('start_time', tzinfo=melb),
+    ... ).values('day', 'weekday', 'hour').get(
+    ...     end_time__year=ExtractYear('start_time'),
+    ... )
+    {'day': 16, 'weekday': 3, 'hour': 9}
+
+
+``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`` or
+``DateTimeField``, a ``kind`` representing a date part, and an ``output_field``
+that's either ``DateTimeField()`` or ``DateField()``. It returns a datetime or
+date, 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
+* "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
+* "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_time=datetime(2015, 6, 15, 14, 30, 50, 321))
+    >>> Experiment.objects.create(start_time=datetime(2015, 6, 15, 14, 40, 2, 123))
+    >>> Experiment.objects.create(start_time=datetime(2015, 12, 25, 10, 5, 27, 999))
+    >>> experiments_per_day = Experiment.objects.annotate(
+    ...    start_day=Trunc('start_time', '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_time', 'day', output_field=DateTimeField())
+    ... ).filter(start_day=datetime(2015, 6, 15))
+    >>> for exp in experiments:
+    ...     print(exp.start_time)
+    ...
+    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)
+
+    .. attribute:: kind = 'year'
+
+.. class:: TruncMonth(expression, output_field=None, tzinfo=None, **extra)
+
+    .. attribute:: kind = 'month'
+
+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_time=start1, start_date=start1.date())
+    >>> Experiment.objects.create(start_time=start2, start_date=start2.date())
+    >>> Experiment.objects.create(start_time=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_time', 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)
+
+    .. 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:: 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_time=start1, start_date=start1.date())
+    >>> melb = pytz.timezone('Australia/Melbourne')
+    >>> Experiment.objects.annotate(
+    ...     date=TruncDate('start_time'),
+    ...     day=TruncDay('start_time', tzinfo=melb),
+    ...     hour=TruncHour('start_time', tzinfo=melb),
+    ...     minute=TruncMinute('start_time'),
+    ...     second=TruncSecond('start_time'),
+    ... ).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>)
+    }
diff --git a/docs/releases/1.10.txt b/docs/releases/1.10.txt
index a73790b9b8..14b23def72 100644
--- a/docs/releases/1.10.txt
+++ b/docs/releases/1.10.txt
@@ -443,6 +443,13 @@ Models
 * A proxy model may now inherit multiple proxy models that share a common
   non-abstract parent class.
 
+* Added :class:`~django.db.models.functions.datetime.Extract` functions
+  to extract datetime components as integers, such as year and hour.
+
+* Added :class:`~django.db.models.functions.datetime.Trunc` functions to
+  truncate a date or datetime to a significant component. They enable queries
+  like sales-per-day or sales-per-hour.
+
 * ``Model.__init__()`` now sets values of virtual fields from its keyword
   arguments.
 
@@ -900,6 +907,10 @@ Miscellaneous
   989 characters. If you were counting on a limited length, truncate the subject
   yourself.
 
+* Private expressions ``django.db.models.expressions.Date`` and ``DateTime``
+  are removed. The new :class:`~django.db.models.functions.datetime.Trunc`
+  expressions provide the same functionality.
+
 .. _deprecated-features-1.10:
 
 Features deprecated in 1.10