| 1 |
================== |
| 2 |
Database Functions |
| 3 |
================== |
| 4 |
|
| 5 |
.. module:: django.db.models.functions |
| 6 |
:synopsis: Database Functions |
| 7 |
|
| 8 |
The classes documented below provide a way for users to use functions provided |
| 9 |
by the underlying database as annotations, aggregations, or filters in Django. |
| 10 |
Functions are also :doc:`expressions <expressions>`, so they can be used and |
| 11 |
combined with other expressions like :ref:`aggregate functions |
| 12 |
<aggregation-functions>`. |
| 13 |
|
| 14 |
We'll be using the following model in examples of each function:: |
| 15 |
|
| 16 |
class Author(models.Model): |
| 17 |
name = models.CharField(max_length=50) |
| 18 |
age = models.PositiveIntegerField(null=True, blank=True) |
| 19 |
alias = models.CharField(max_length=50, null=True, blank=True) |
| 20 |
goes_by = models.CharField(max_length=50, null=True, blank=True) |
| 21 |
|
| 22 |
We don't usually recommend allowing ``null=True`` for ``CharField`` since this |
| 23 |
allows the field to have two "empty values", but it's important for the |
| 24 |
``Coalesce`` example below. |
| 25 |
|
| 26 |
.. _comparison-functions: |
| 27 |
|
| 28 |
Comparison and conversion functions |
| 29 |
=================================== |
| 30 |
|
| 31 |
``Cast`` |
| 32 |
-------- |
| 33 |
|
| 34 |
.. class:: Cast(expression, output_field) |
| 35 |
|
| 36 |
Forces the result type of ``expression`` to be the one from ``output_field``. |
| 37 |
|
| 38 |
Usage example: |
| 39 |
|
| 40 |
.. code-block:: pycon |
| 41 |
|
| 42 |
>>> from django.db.models import FloatField |
| 43 |
>>> from django.db.models.functions import Cast |
| 44 |
>>> Author.objects.create(age=25, name="Margaret Smith") |
| 45 |
>>> author = Author.objects.annotate( |
| 46 |
... age_as_float=Cast("age", output_field=FloatField()), |
| 47 |
... ).get() |
| 48 |
>>> print(author.age_as_float) |
| 49 |
25.0 |
| 50 |
|
| 51 |
``Coalesce`` |
| 52 |
------------ |
| 53 |
|
| 54 |
.. class:: Coalesce(*expressions, **extra) |
| 55 |
|
| 56 |
Accepts a list of at least two field names or expressions and returns the |
| 57 |
first non-null value (note that an empty string is not considered a null |
| 58 |
value). Each argument must be of a similar type, so mixing text and numbers |
| 59 |
will result in a database error. |
| 60 |
|
| 61 |
Usage examples: |
| 62 |
|
| 63 |
.. code-block:: pycon |
| 64 |
|
| 65 |
>>> # Get a screen name from least to most public |
| 66 |
>>> from django.db.models import Sum |
| 67 |
>>> from django.db.models.functions import Coalesce |
| 68 |
>>> Author.objects.create(name="Margaret Smith", goes_by="Maggie") |
| 69 |
>>> author = Author.objects.annotate(screen_name=Coalesce("alias", "goes_by", "name")).get() |
| 70 |
>>> print(author.screen_name) |
| 71 |
Maggie |
| 72 |
|
| 73 |
>>> # Prevent an aggregate Sum() from returning None |
| 74 |
>>> # The aggregate default argument uses Coalesce() under the hood. |
| 75 |
>>> aggregated = Author.objects.aggregate( |
| 76 |
... combined_age=Sum("age"), |
| 77 |
... combined_age_default=Sum("age", default=0), |
| 78 |
... combined_age_coalesce=Coalesce(Sum("age"), 0), |
| 79 |
... ) |
| 80 |
>>> print(aggregated["combined_age"]) |
| 81 |
None |
| 82 |
>>> print(aggregated["combined_age_default"]) |
| 83 |
0 |
| 84 |
>>> print(aggregated["combined_age_coalesce"]) |
| 85 |
0 |
| 86 |
|
| 87 |
.. warning:: |
| 88 |
|
| 89 |
A Python value passed to ``Coalesce`` on MySQL may be converted to an |
| 90 |
incorrect type unless explicitly cast to the correct database type: |
| 91 |
|
| 92 |
.. code-block:: pycon |
| 93 |
|
| 94 |
>>> from django.db.models import DateTimeField |
| 95 |
>>> from django.db.models.functions import Cast, Coalesce |
| 96 |
>>> from django.utils import timezone |
| 97 |
>>> now = timezone.now() |
| 98 |
>>> Coalesce("updated", Cast(now, DateTimeField())) |
| 99 |
|
| 100 |
``Collate`` |
| 101 |
----------- |
| 102 |
|
| 103 |
.. class:: Collate(expression, collation) |
| 104 |
|
| 105 |
Takes an expression and a collation name to query against. |
| 106 |
|
| 107 |
For example, to filter case-insensitively in SQLite: |
| 108 |
|
| 109 |
.. code-block:: pycon |
| 110 |
|
| 111 |
>>> Author.objects.filter(name=Collate(Value("john"), "nocase")) |
| 112 |
<QuerySet [<Author: John>, <Author: john>]> |
| 113 |
|
| 114 |
It can also be used when ordering, for example with PostgreSQL: |
| 115 |
|
| 116 |
.. code-block:: pycon |
| 117 |
|
| 118 |
>>> Author.objects.order_by(Collate("name", "et-x-icu")) |
| 119 |
<QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]> |
| 120 |
|
| 121 |
``Greatest`` |
| 122 |
------------ |
| 123 |
|
| 124 |
.. class:: Greatest(*expressions, **extra) |
| 125 |
|
| 126 |
Accepts a list of at least two field names or expressions and returns the |
| 127 |
greatest value. Each argument must be of a similar type, so mixing text and |
| 128 |
numbers will result in a database error. |
| 129 |
|
| 130 |
Usage example:: |
| 131 |
|
| 132 |
class Blog(models.Model): |
| 133 |
body = models.TextField() |
| 134 |
modified = models.DateTimeField(auto_now=True) |
| 135 |
|
| 136 |
|
| 137 |
class Comment(models.Model): |
| 138 |
body = models.TextField() |
| 139 |
modified = models.DateTimeField(auto_now=True) |
| 140 |
blog = models.ForeignKey(Blog, on_delete=models.CASCADE) |
| 141 |
|
| 142 |
.. code-block:: pycon |
| 143 |
|
| 144 |
>>> from django.db.models.functions import Greatest |
| 145 |
>>> blog = Blog.objects.create(body="Greatest is the best.") |
| 146 |
>>> comment = Comment.objects.create(body="No, Least is better.", blog=blog) |
| 147 |
>>> comments = Comment.objects.annotate(last_updated=Greatest("modified", "blog__modified")) |
| 148 |
>>> annotated_comment = comments.get() |
| 149 |
|
| 150 |
``annotated_comment.last_updated`` will be the most recent of ``blog.modified`` |
| 151 |
and ``comment.modified``. |
| 152 |
|
| 153 |
.. warning:: |
| 154 |
|
| 155 |
The behavior of ``Greatest`` when one or more expression may be ``null`` |
| 156 |
varies between databases: |
| 157 |
|
| 158 |
- PostgreSQL: ``Greatest`` will return the largest non-null expression, |
| 159 |
or ``null`` if all expressions are ``null``. |
| 160 |
- SQLite, Oracle, and MySQL: If any expression is ``null``, ``Greatest`` |
| 161 |
will return ``null``. |
| 162 |
|
| 163 |
The PostgreSQL behavior can be emulated using ``Coalesce`` if you know |
| 164 |
a sensible minimum value to provide as a default. |
| 165 |
|
| 166 |
``Least`` |
| 167 |
--------- |
| 168 |
|
| 169 |
.. class:: Least(*expressions, **extra) |
| 170 |
|
| 171 |
Accepts a list of at least two field names or expressions and returns the |
| 172 |
least value. Each argument must be of a similar type, so mixing text and numbers |
| 173 |
will result in a database error. |
| 174 |
|
| 175 |
.. warning:: |
| 176 |
|
| 177 |
The behavior of ``Least`` when one or more expression may be ``null`` |
| 178 |
varies between databases: |
| 179 |
|
| 180 |
- PostgreSQL: ``Least`` will return the smallest non-null expression, |
| 181 |
or ``null`` if all expressions are ``null``. |
| 182 |
- SQLite, Oracle, and MySQL: If any expression is ``null``, ``Least`` |
| 183 |
will return ``null``. |
| 184 |
|
| 185 |
The PostgreSQL behavior can be emulated using ``Coalesce`` if you know |
| 186 |
a sensible maximum value to provide as a default. |
| 187 |
|
| 188 |
``NullIf`` |
| 189 |
---------- |
| 190 |
|
| 191 |
.. class:: NullIf(expression1, expression2) |
| 192 |
|
| 193 |
Accepts two expressions and returns ``None`` if they are equal, otherwise |
| 194 |
returns ``expression1``. |
| 195 |
|
| 196 |
.. admonition:: Caveats on Oracle |
| 197 |
|
| 198 |
Due to an :ref:`Oracle convention<oracle-null-empty-strings>`, this |
| 199 |
function returns the empty string instead of ``None`` when the expressions |
| 200 |
are of type :class:`~django.db.models.CharField`. |
| 201 |
|
| 202 |
Passing ``Value(None)`` to ``expression1`` is prohibited on Oracle since |
| 203 |
Oracle doesn't accept ``NULL`` as the first argument. |
| 204 |
|
| 205 |
.. _date-functions: |
| 206 |
|
| 207 |
Date functions |
| 208 |
============== |
| 209 |
|
| 210 |
We'll be using the following model in examples of each function:: |
| 211 |
|
| 212 |
class Experiment(models.Model): |
| 213 |
start_datetime = models.DateTimeField() |
| 214 |
start_date = models.DateField(null=True, blank=True) |
| 215 |
start_time = models.TimeField(null=True, blank=True) |
| 216 |
end_datetime = models.DateTimeField(null=True, blank=True) |
| 217 |
end_date = models.DateField(null=True, blank=True) |
| 218 |
end_time = models.TimeField(null=True, blank=True) |
| 219 |
|
| 220 |
``Extract`` |
| 221 |
----------- |
| 222 |
|
| 223 |
.. class:: Extract(expression, lookup_name=None, tzinfo=None, **extra) |
| 224 |
|
| 225 |
Extracts a component of a date as a number. |
| 226 |
|
| 227 |
Takes an ``expression`` representing a ``DateField``, ``DateTimeField``, |
| 228 |
``TimeField``, or ``DurationField`` and a ``lookup_name``, and returns the part |
| 229 |
of the date referenced by ``lookup_name`` as an ``IntegerField``. |
| 230 |
Django usually uses the databases' extract function, so you may use any |
| 231 |
``lookup_name`` that your database supports. A ``tzinfo`` subclass, usually |
| 232 |
provided by :mod:`zoneinfo`, can be passed to extract a value in a specific |
| 233 |
timezone. |
| 234 |
|
| 235 |
Given the datetime ``2015-06-15 23:30:01.000321+00:00``, the built-in |
| 236 |
``lookup_name``\s return: |
| 237 |
|
| 238 |
* "year": 2015 |
| 239 |
* "iso_year": 2015 |
| 240 |
* "quarter": 2 |
| 241 |
* "month": 6 |
| 242 |
* "day": 15 |
| 243 |
* "week": 25 |
| 244 |
* "week_day": 2 |
| 245 |
* "iso_week_day": 1 |
| 246 |
* "hour": 23 |
| 247 |
* "minute": 30 |
| 248 |
* "second": 1 |
| 249 |
|
| 250 |
If a different timezone like ``Australia/Melbourne`` is active in Django, then |
| 251 |
the datetime is converted to the timezone before the value is extracted. The |
| 252 |
timezone offset for Melbourne in the example date above is +10:00. The values |
| 253 |
returned when this timezone is active will be the same as above except for: |
| 254 |
|
| 255 |
* "day": 16 |
| 256 |
* "week_day": 3 |
| 257 |
* "iso_week_day": 2 |
| 258 |
* "hour": 9 |
| 259 |
|
| 260 |
.. admonition:: ``week_day`` values |
| 261 |
|
| 262 |
The ``week_day`` ``lookup_type`` is calculated differently from most |
| 263 |
databases and from Python's standard functions. This function will return |
| 264 |
``1`` for Sunday, ``2`` for Monday, through ``7`` for Saturday. |
| 265 |
|
| 266 |
The equivalent calculation in Python is: |
| 267 |
|
| 268 |
.. code-block:: pycon |
| 269 |
|
| 270 |
>>> from datetime import datetime |
| 271 |
>>> dt = datetime(2015, 6, 15) |
| 272 |
>>> (dt.isoweekday() % 7) + 1 |
| 273 |
2 |
| 274 |
|
| 275 |
.. admonition:: ``week`` values |
| 276 |
|
| 277 |
The ``week`` ``lookup_type`` is calculated based on `ISO-8601 |
| 278 |
<https://en.wikipedia.org/wiki/ISO-8601>`_, i.e., |
| 279 |
a week starts on a Monday. The first week of a year is the one that |
| 280 |
contains the year's first Thursday, i.e. the first week has the majority |
| 281 |
(four or more) of its days in the year. The value returned is in the range |
| 282 |
1 to 52 or 53. |
| 283 |
|
| 284 |
Each ``lookup_name`` above has a corresponding ``Extract`` subclass (listed |
| 285 |
below) that should typically be used instead of the more verbose equivalent, |
| 286 |
e.g. use ``ExtractYear(...)`` rather than ``Extract(..., lookup_name='year')``. |
| 287 |
|
| 288 |
Usage example: |
| 289 |
|
| 290 |
.. code-block:: pycon |
| 291 |
|
| 292 |
>>> from datetime import datetime |
| 293 |
>>> from django.db.models.functions import Extract |
| 294 |
>>> start = datetime(2015, 6, 15) |
| 295 |
>>> end = datetime(2015, 7, 2) |
| 296 |
>>> Experiment.objects.create( |
| 297 |
... start_datetime=start, start_date=start.date(), end_datetime=end, end_date=end.date() |
| 298 |
... ) |
| 299 |
>>> # Add the experiment start year as a field in the QuerySet. |
| 300 |
>>> experiment = Experiment.objects.annotate( |
| 301 |
... start_year=Extract("start_datetime", "year") |
| 302 |
... ).get() |
| 303 |
>>> experiment.start_year |
| 304 |
2015 |
| 305 |
>>> # How many experiments completed in the same year in which they started? |
| 306 |
>>> Experiment.objects.filter(start_datetime__year=Extract("end_datetime", "year")).count() |
| 307 |
1 |
| 308 |
|
| 309 |
``DateField`` extracts |
| 310 |
~~~~~~~~~~~~~~~~~~~~~~ |
| 311 |
|
| 312 |
.. class:: ExtractYear(expression, tzinfo=None, **extra) |
| 313 |
|
| 314 |
.. attribute:: lookup_name = 'year' |
| 315 |
|
| 316 |
.. class:: ExtractIsoYear(expression, tzinfo=None, **extra) |
| 317 |
|
| 318 |
Returns the ISO-8601 week-numbering year. |
| 319 |
|
| 320 |
.. attribute:: lookup_name = 'iso_year' |
| 321 |
|
| 322 |
.. class:: ExtractMonth(expression, tzinfo=None, **extra) |
| 323 |
|
| 324 |
.. attribute:: lookup_name = 'month' |
| 325 |
|
| 326 |
.. class:: ExtractDay(expression, tzinfo=None, **extra) |
| 327 |
|
| 328 |
.. attribute:: lookup_name = 'day' |
| 329 |
|
| 330 |
.. class:: ExtractWeekDay(expression, tzinfo=None, **extra) |
| 331 |
|
| 332 |
.. attribute:: lookup_name = 'week_day' |
| 333 |
|
| 334 |
.. class:: ExtractIsoWeekDay(expression, tzinfo=None, **extra) |
| 335 |
|
| 336 |
Returns the ISO-8601 week day with day 1 being Monday and day 7 being |
| 337 |
Sunday. |
| 338 |
|
| 339 |
.. attribute:: lookup_name = 'iso_week_day' |
| 340 |
|
| 341 |
.. class:: ExtractWeek(expression, tzinfo=None, **extra) |
| 342 |
|
| 343 |
.. attribute:: lookup_name = 'week' |
| 344 |
|
| 345 |
.. class:: ExtractQuarter(expression, tzinfo=None, **extra) |
| 346 |
|
| 347 |
.. attribute:: lookup_name = 'quarter' |
| 348 |
|
| 349 |
These are logically equivalent to ``Extract('date_field', lookup_name)``. Each |
| 350 |
class is also a ``Transform`` registered on ``DateField`` and ``DateTimeField`` |
| 351 |
as ``__(lookup_name)``, e.g. ``__year``. |
| 352 |
|
| 353 |
Since ``DateField``\s don't have a time component, only ``Extract`` subclasses |
| 354 |
that deal with date-parts can be used with ``DateField``: |
| 355 |
|
| 356 |
.. code-block:: pycon |
| 357 |
|
| 358 |
>>> from datetime import datetime, timezone |
| 359 |
>>> from django.db.models.functions import ( |
| 360 |
... ExtractDay, |
| 361 |
... ExtractMonth, |
| 362 |
... ExtractQuarter, |
| 363 |
... ExtractWeek, |
| 364 |
... ExtractIsoWeekDay, |
| 365 |
... ExtractWeekDay, |
| 366 |
... ExtractIsoYear, |
| 367 |
... ExtractYear, |
| 368 |
... ) |
| 369 |
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc) |
| 370 |
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc) |
| 371 |
>>> Experiment.objects.create( |
| 372 |
... start_datetime=start_2015, |
| 373 |
... start_date=start_2015.date(), |
| 374 |
... end_datetime=end_2015, |
| 375 |
... end_date=end_2015.date(), |
| 376 |
... ) |
| 377 |
>>> Experiment.objects.annotate( |
| 378 |
... year=ExtractYear("start_date"), |
| 379 |
... isoyear=ExtractIsoYear("start_date"), |
| 380 |
... quarter=ExtractQuarter("start_date"), |
| 381 |
... month=ExtractMonth("start_date"), |
| 382 |
... week=ExtractWeek("start_date"), |
| 383 |
... day=ExtractDay("start_date"), |
| 384 |
... weekday=ExtractWeekDay("start_date"), |
| 385 |
... isoweekday=ExtractIsoWeekDay("start_date"), |
| 386 |
... ).values( |
| 387 |
... "year", |
| 388 |
... "isoyear", |
| 389 |
... "quarter", |
| 390 |
... "month", |
| 391 |
... "week", |
| 392 |
... "day", |
| 393 |
... "weekday", |
| 394 |
... "isoweekday", |
| 395 |
... ).get( |
| 396 |
... end_date__year=ExtractYear("start_date") |
| 397 |
... ) |
| 398 |
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25, |
| 399 |
'day': 15, 'weekday': 2, 'isoweekday': 1} |
| 400 |
|
| 401 |
``DateTimeField`` extracts |
| 402 |
~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 403 |
|
| 404 |
In addition to the following, all extracts for ``DateField`` listed above may |
| 405 |
also be used on ``DateTimeField``\s . |
| 406 |
|
| 407 |
.. class:: ExtractHour(expression, tzinfo=None, **extra) |
| 408 |
|
| 409 |
.. attribute:: lookup_name = 'hour' |
| 410 |
|
| 411 |
.. class:: ExtractMinute(expression, tzinfo=None, **extra) |
| 412 |
|
| 413 |
.. attribute:: lookup_name = 'minute' |
| 414 |
|
| 415 |
.. class:: ExtractSecond(expression, tzinfo=None, **extra) |
| 416 |
|
| 417 |
.. attribute:: lookup_name = 'second' |
| 418 |
|
| 419 |
These are logically equivalent to ``Extract('datetime_field', lookup_name)``. |
| 420 |
Each class is also a ``Transform`` registered on ``DateTimeField`` as |
| 421 |
``__(lookup_name)``, e.g. ``__minute``. |
| 422 |
|
| 423 |
``DateTimeField`` examples: |
| 424 |
|
| 425 |
.. code-block:: pycon |
| 426 |
|
| 427 |
>>> from datetime import datetime, timezone |
| 428 |
>>> from django.db.models.functions import ( |
| 429 |
... ExtractDay, |
| 430 |
... ExtractHour, |
| 431 |
... ExtractMinute, |
| 432 |
... ExtractMonth, |
| 433 |
... ExtractQuarter, |
| 434 |
... ExtractSecond, |
| 435 |
... ExtractWeek, |
| 436 |
... ExtractIsoWeekDay, |
| 437 |
... ExtractWeekDay, |
| 438 |
... ExtractIsoYear, |
| 439 |
... ExtractYear, |
| 440 |
... ) |
| 441 |
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc) |
| 442 |
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc) |
| 443 |
>>> Experiment.objects.create( |
| 444 |
... start_datetime=start_2015, |
| 445 |
... start_date=start_2015.date(), |
| 446 |
... end_datetime=end_2015, |
| 447 |
... end_date=end_2015.date(), |
| 448 |
... ) |
| 449 |
>>> Experiment.objects.annotate( |
| 450 |
... year=ExtractYear("start_datetime"), |
| 451 |
... isoyear=ExtractIsoYear("start_datetime"), |
| 452 |
... quarter=ExtractQuarter("start_datetime"), |
| 453 |
... month=ExtractMonth("start_datetime"), |
| 454 |
... week=ExtractWeek("start_datetime"), |
| 455 |
... day=ExtractDay("start_datetime"), |
| 456 |
... weekday=ExtractWeekDay("start_datetime"), |
| 457 |
... isoweekday=ExtractIsoWeekDay("start_datetime"), |
| 458 |
... hour=ExtractHour("start_datetime"), |
| 459 |
... minute=ExtractMinute("start_datetime"), |
| 460 |
... second=ExtractSecond("start_datetime"), |
| 461 |
... ).values( |
| 462 |
... "year", |
| 463 |
... "isoyear", |
| 464 |
... "month", |
| 465 |
... "week", |
| 466 |
... "day", |
| 467 |
... "weekday", |
| 468 |
... "isoweekday", |
| 469 |
... "hour", |
| 470 |
... "minute", |
| 471 |
... "second", |
| 472 |
... ).get( |
| 473 |
... end_datetime__year=ExtractYear("start_datetime") |
| 474 |
... ) |
| 475 |
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25, |
| 476 |
'day': 15, 'weekday': 2, 'isoweekday': 1, 'hour': 23, 'minute': 30, |
| 477 |
'second': 1} |
| 478 |
|
| 479 |
When :setting:`USE_TZ` is ``True`` then datetimes are stored in the database |
| 480 |
in UTC. If a different timezone is active in Django, the datetime is converted |
| 481 |
to that timezone before the value is extracted. The example below converts to |
| 482 |
the Melbourne timezone (UTC +10:00), which changes the day, weekday, and hour |
| 483 |
values that are returned: |
| 484 |
|
| 485 |
.. code-block:: pycon |
| 486 |
|
| 487 |
>>> from django.utils import timezone |
| 488 |
>>> import zoneinfo |
| 489 |
>>> melb = zoneinfo.ZoneInfo("Australia/Melbourne") # UTC+10:00 |
| 490 |
>>> with timezone.override(melb): |
| 491 |
... Experiment.objects.annotate( |
| 492 |
... day=ExtractDay("start_datetime"), |
| 493 |
... weekday=ExtractWeekDay("start_datetime"), |
| 494 |
... isoweekday=ExtractIsoWeekDay("start_datetime"), |
| 495 |
... hour=ExtractHour("start_datetime"), |
| 496 |
... ).values("day", "weekday", "isoweekday", "hour").get( |
| 497 |
... end_datetime__year=ExtractYear("start_datetime"), |
| 498 |
... ) |
| 499 |
... |
| 500 |
{'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9} |
| 501 |
|
| 502 |
Explicitly passing the timezone to the ``Extract`` function behaves in the same |
| 503 |
way, and takes priority over an active timezone: |
| 504 |
|
| 505 |
.. code-block:: pycon |
| 506 |
|
| 507 |
>>> import zoneinfo |
| 508 |
>>> melb = zoneinfo.ZoneInfo("Australia/Melbourne") |
| 509 |
>>> Experiment.objects.annotate( |
| 510 |
... day=ExtractDay("start_datetime", tzinfo=melb), |
| 511 |
... weekday=ExtractWeekDay("start_datetime", tzinfo=melb), |
| 512 |
... isoweekday=ExtractIsoWeekDay("start_datetime", tzinfo=melb), |
| 513 |
... hour=ExtractHour("start_datetime", tzinfo=melb), |
| 514 |
... ).values("day", "weekday", "isoweekday", "hour").get( |
| 515 |
... end_datetime__year=ExtractYear("start_datetime"), |
| 516 |
... ) |
| 517 |
{'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9} |
| 518 |
|
| 519 |
``Now`` |
| 520 |
------- |
| 521 |
|
| 522 |
.. class:: Now() |
| 523 |
|
| 524 |
Returns the database server's current date and time when the query is executed, |
| 525 |
typically using the SQL ``CURRENT_TIMESTAMP``. |
| 526 |
|
| 527 |
Usage example: |
| 528 |
|
| 529 |
.. code-block:: pycon |
| 530 |
|
| 531 |
>>> from django.db.models.functions import Now |
| 532 |
>>> Article.objects.filter(published__lte=Now()) |
| 533 |
<QuerySet [<Article: How to Django>]> |
| 534 |
|
| 535 |
.. admonition:: PostgreSQL considerations |
| 536 |
|
| 537 |
On PostgreSQL, the SQL ``CURRENT_TIMESTAMP`` returns the time that the |
| 538 |
current transaction started. Therefore for cross-database compatibility, |
| 539 |
``Now()`` uses ``STATEMENT_TIMESTAMP`` instead. If you need the transaction |
| 540 |
timestamp, use :class:`django.contrib.postgres.functions.TransactionNow`. |
| 541 |
|
| 542 |
.. admonition:: Oracle |
| 543 |
|
| 544 |
On Oracle, the SQL ``LOCALTIMESTAMP`` is used to avoid issues with casting |
| 545 |
``CURRENT_TIMESTAMP`` to ``DateTimeField``. |
| 546 |
|
| 547 |
``Trunc`` |
| 548 |
--------- |
| 549 |
|
| 550 |
.. class:: Trunc(expression, kind, output_field=None, tzinfo=None, **extra) |
| 551 |
|
| 552 |
Truncates a date up to a significant component. |
| 553 |
|
| 554 |
When you only care if something happened in a particular year, hour, or day, |
| 555 |
but not the exact second, then ``Trunc`` (and its subclasses) can be useful to |
| 556 |
filter or aggregate your data. For example, you can use ``Trunc`` to calculate |
| 557 |
the number of sales per day. |
| 558 |
|
| 559 |
``Trunc`` takes a single ``expression``, representing a ``DateField``, |
| 560 |
``TimeField``, or ``DateTimeField``, a ``kind`` representing a date or time |
| 561 |
part, and an ``output_field`` that's either ``DateTimeField()``, |
| 562 |
``TimeField()``, or ``DateField()``. It returns a datetime, date, or time |
| 563 |
depending on ``output_field``, with fields up to ``kind`` set to their minimum |
| 564 |
value. If ``output_field`` is omitted, it will default to the ``output_field`` |
| 565 |
of ``expression``. A ``tzinfo`` subclass, usually provided by :mod:`zoneinfo`, |
| 566 |
can be passed to truncate a value in a specific timezone. |
| 567 |
|
| 568 |
Given the datetime ``2015-06-15 14:30:50.000321+00:00``, the built-in ``kind``\s |
| 569 |
return: |
| 570 |
|
| 571 |
* "year": 2015-01-01 00:00:00+00:00 |
| 572 |
* "quarter": 2015-04-01 00:00:00+00:00 |
| 573 |
* "month": 2015-06-01 00:00:00+00:00 |
| 574 |
* "week": 2015-06-15 00:00:00+00:00 |
| 575 |
* "day": 2015-06-15 00:00:00+00:00 |
| 576 |
* "hour": 2015-06-15 14:00:00+00:00 |
| 577 |
* "minute": 2015-06-15 14:30:00+00:00 |
| 578 |
* "second": 2015-06-15 14:30:50+00:00 |
| 579 |
|
| 580 |
If a different timezone like ``Australia/Melbourne`` is active in Django, then |
| 581 |
the datetime is converted to the new timezone before the value is truncated. |
| 582 |
The timezone offset for Melbourne in the example date above is +10:00. The |
| 583 |
values returned when this timezone is active will be: |
| 584 |
|
| 585 |
* "year": 2015-01-01 00:00:00+11:00 |
| 586 |
* "quarter": 2015-04-01 00:00:00+10:00 |
| 587 |
* "month": 2015-06-01 00:00:00+10:00 |
| 588 |
* "week": 2015-06-16 00:00:00+10:00 |
| 589 |
* "day": 2015-06-16 00:00:00+10:00 |
| 590 |
* "hour": 2015-06-16 00:00:00+10:00 |
| 591 |
* "minute": 2015-06-16 00:30:00+10:00 |
| 592 |
* "second": 2015-06-16 00:30:50+10:00 |
| 593 |
|
| 594 |
The year has an offset of +11:00 because the result transitioned into daylight |
| 595 |
saving time. |
| 596 |
|
| 597 |
Each ``kind`` above has a corresponding ``Trunc`` subclass (listed below) that |
| 598 |
should typically be used instead of the more verbose equivalent, |
| 599 |
e.g. use ``TruncYear(...)`` rather than ``Trunc(..., kind='year')``. |
| 600 |
|
| 601 |
The subclasses are all defined as transforms, but they aren't registered with |
| 602 |
any fields, because the lookup names are already reserved by the ``Extract`` |
| 603 |
subclasses. |
| 604 |
|
| 605 |
Usage example: |
| 606 |
|
| 607 |
.. code-block:: pycon |
| 608 |
|
| 609 |
>>> from datetime import datetime |
| 610 |
>>> from django.db.models import Count, DateTimeField |
| 611 |
>>> from django.db.models.functions import Trunc |
| 612 |
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321)) |
| 613 |
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123)) |
| 614 |
>>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999)) |
| 615 |
>>> experiments_per_day = ( |
| 616 |
... Experiment.objects.annotate( |
| 617 |
... start_day=Trunc("start_datetime", "day", output_field=DateTimeField()) |
| 618 |
... ) |
| 619 |
... .values("start_day") |
| 620 |
... .annotate(experiments=Count("id")) |
| 621 |
... ) |
| 622 |
>>> for exp in experiments_per_day: |
| 623 |
... print(exp["start_day"], exp["experiments"]) |
| 624 |
... |
| 625 |
2015-06-15 00:00:00 2 |
| 626 |
2015-12-25 00:00:00 1 |
| 627 |
>>> experiments = Experiment.objects.annotate( |
| 628 |
... start_day=Trunc("start_datetime", "day", output_field=DateTimeField()) |
| 629 |
... ).filter(start_day=datetime(2015, 6, 15)) |
| 630 |
>>> for exp in experiments: |
| 631 |
... print(exp.start_datetime) |
| 632 |
... |
| 633 |
2015-06-15 14:30:50.000321 |
| 634 |
2015-06-15 14:40:02.000123 |
| 635 |
|
| 636 |
``DateField`` truncation |
| 637 |
~~~~~~~~~~~~~~~~~~~~~~~~ |
| 638 |
|
| 639 |
.. class:: TruncYear(expression, output_field=None, tzinfo=None, **extra) |
| 640 |
|
| 641 |
.. attribute:: kind = 'year' |
| 642 |
|
| 643 |
.. class:: TruncMonth(expression, output_field=None, tzinfo=None, **extra) |
| 644 |
|
| 645 |
.. attribute:: kind = 'month' |
| 646 |
|
| 647 |
.. class:: TruncWeek(expression, output_field=None, tzinfo=None, **extra) |
| 648 |
|
| 649 |
Truncates to midnight on the Monday of the week. |
| 650 |
|
| 651 |
.. attribute:: kind = 'week' |
| 652 |
|
| 653 |
.. class:: TruncQuarter(expression, output_field=None, tzinfo=None, **extra) |
| 654 |
|
| 655 |
.. attribute:: kind = 'quarter' |
| 656 |
|
| 657 |
These are logically equivalent to ``Trunc('date_field', kind)``. They truncate |
| 658 |
all parts of the date up to ``kind`` which allows grouping or filtering dates |
| 659 |
with less precision. ``expression`` can have an ``output_field`` of either |
| 660 |
``DateField`` or ``DateTimeField``. |
| 661 |
|
| 662 |
Since ``DateField``\s don't have a time component, only ``Trunc`` subclasses |
| 663 |
that deal with date-parts can be used with ``DateField``: |
| 664 |
|
| 665 |
.. code-block:: pycon |
| 666 |
|
| 667 |
>>> from datetime import datetime, timezone |
| 668 |
>>> from django.db.models import Count |
| 669 |
>>> from django.db.models.functions import TruncMonth, TruncYear |
| 670 |
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc) |
| 671 |
>>> start2 = datetime(2015, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc) |
| 672 |
>>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc) |
| 673 |
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date()) |
| 674 |
>>> Experiment.objects.create(start_datetime=start2, start_date=start2.date()) |
| 675 |
>>> Experiment.objects.create(start_datetime=start3, start_date=start3.date()) |
| 676 |
>>> experiments_per_year = ( |
| 677 |
... Experiment.objects.annotate(year=TruncYear("start_date")) |
| 678 |
... .values("year") |
| 679 |
... .annotate(experiments=Count("id")) |
| 680 |
... ) |
| 681 |
>>> for exp in experiments_per_year: |
| 682 |
... print(exp["year"], exp["experiments"]) |
| 683 |
... |
| 684 |
2014-01-01 1 |
| 685 |
2015-01-01 2 |
| 686 |
|
| 687 |
>>> import zoneinfo |
| 688 |
>>> melb = zoneinfo.ZoneInfo("Australia/Melbourne") |
| 689 |
>>> experiments_per_month = ( |
| 690 |
... Experiment.objects.annotate(month=TruncMonth("start_datetime", tzinfo=melb)) |
| 691 |
... .values("month") |
| 692 |
... .annotate(experiments=Count("id")) |
| 693 |
... ) |
| 694 |
>>> for exp in experiments_per_month: |
| 695 |
... print(exp["month"], exp["experiments"]) |
| 696 |
... |
| 697 |
2015-06-01 00:00:00+10:00 1 |
| 698 |
2016-01-01 00:00:00+11:00 1 |
| 699 |
2014-06-01 00:00:00+10:00 1 |
| 700 |
|
| 701 |
``DateTimeField`` truncation |
| 702 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 703 |
|
| 704 |
.. class:: TruncDate(expression, tzinfo=None, **extra) |
| 705 |
|
| 706 |
.. attribute:: lookup_name = 'date' |
| 707 |
.. attribute:: output_field = DateField() |
| 708 |
|
| 709 |
``TruncDate`` casts ``expression`` to a date rather than using the built-in SQL |
| 710 |
truncate function. It's also registered as a transform on ``DateTimeField`` as |
| 711 |
``__date``. |
| 712 |
|
| 713 |
.. class:: TruncTime(expression, tzinfo=None, **extra) |
| 714 |
|
| 715 |
.. attribute:: lookup_name = 'time' |
| 716 |
.. attribute:: output_field = TimeField() |
| 717 |
|
| 718 |
``TruncTime`` casts ``expression`` to a time rather than using the built-in SQL |
| 719 |
truncate function. It's also registered as a transform on ``DateTimeField`` as |
| 720 |
``__time``. |
| 721 |
|
| 722 |
.. class:: TruncDay(expression, output_field=None, tzinfo=None, **extra) |
| 723 |
|
| 724 |
.. attribute:: kind = 'day' |
| 725 |
|
| 726 |
.. class:: TruncHour(expression, output_field=None, tzinfo=None, **extra) |
| 727 |
|
| 728 |
.. attribute:: kind = 'hour' |
| 729 |
|
| 730 |
.. class:: TruncMinute(expression, output_field=None, tzinfo=None, **extra) |
| 731 |
|
| 732 |
.. attribute:: kind = 'minute' |
| 733 |
|
| 734 |
.. class:: TruncSecond(expression, output_field=None, tzinfo=None, **extra) |
| 735 |
|
| 736 |
.. attribute:: kind = 'second' |
| 737 |
|
| 738 |
These are logically equivalent to ``Trunc('datetime_field', kind)``. They |
| 739 |
truncate all parts of the date up to ``kind`` and allow grouping or filtering |
| 740 |
datetimes with less precision. ``expression`` must have an ``output_field`` of |
| 741 |
``DateTimeField``. |
| 742 |
|
| 743 |
Usage example: |
| 744 |
|
| 745 |
.. code-block:: pycon |
| 746 |
|
| 747 |
>>> from datetime import date, datetime, timezone |
| 748 |
>>> from django.db.models import Count |
| 749 |
>>> from django.db.models.functions import ( |
| 750 |
... TruncDate, |
| 751 |
... TruncDay, |
| 752 |
... TruncHour, |
| 753 |
... TruncMinute, |
| 754 |
... TruncSecond, |
| 755 |
... ) |
| 756 |
>>> import zoneinfo |
| 757 |
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc) |
| 758 |
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date()) |
| 759 |
>>> melb = zoneinfo.ZoneInfo("Australia/Melbourne") |
| 760 |
>>> Experiment.objects.annotate( |
| 761 |
... date=TruncDate("start_datetime"), |
| 762 |
... day=TruncDay("start_datetime", tzinfo=melb), |
| 763 |
... hour=TruncHour("start_datetime", tzinfo=melb), |
| 764 |
... minute=TruncMinute("start_datetime"), |
| 765 |
... second=TruncSecond("start_datetime"), |
| 766 |
... ).values("date", "day", "hour", "minute", "second").get() |
| 767 |
{'date': datetime.date(2014, 6, 15), |
| 768 |
'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=zoneinfo.ZoneInfo('Australia/Melbourne')), |
| 769 |
'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=zoneinfo.ZoneInfo('Australia/Melbourne')), |
| 770 |
'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=timezone.utc), |
| 771 |
'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=timezone.utc) |
| 772 |
} |
| 773 |
|
| 774 |
``TimeField`` truncation |
| 775 |
~~~~~~~~~~~~~~~~~~~~~~~~ |
| 776 |
|
| 777 |
.. class:: TruncHour(expression, output_field=None, tzinfo=None, **extra) |
| 778 |
:noindex: |
| 779 |
|
| 780 |
.. attribute:: kind = 'hour' |
| 781 |
|
| 782 |
.. class:: TruncMinute(expression, output_field=None, tzinfo=None, **extra) |
| 783 |
:noindex: |
| 784 |
|
| 785 |
.. attribute:: kind = 'minute' |
| 786 |
|
| 787 |
.. class:: TruncSecond(expression, output_field=None, tzinfo=None, **extra) |
| 788 |
:noindex: |
| 789 |
|
| 790 |
.. attribute:: kind = 'second' |
| 791 |
|
| 792 |
These are logically equivalent to ``Trunc('time_field', kind)``. They truncate |
| 793 |
all parts of the time up to ``kind`` which allows grouping or filtering times |
| 794 |
with less precision. ``expression`` can have an ``output_field`` of either |
| 795 |
``TimeField`` or ``DateTimeField``. |
| 796 |
|
| 797 |
Since ``TimeField``\s don't have a date component, only ``Trunc`` subclasses |
| 798 |
that deal with time-parts can be used with ``TimeField``: |
| 799 |
|
| 800 |
.. code-block:: pycon |
| 801 |
|
| 802 |
>>> from datetime import datetime, timezone |
| 803 |
>>> from django.db.models import Count, TimeField |
| 804 |
>>> from django.db.models.functions import TruncHour |
| 805 |
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc) |
| 806 |
>>> start2 = datetime(2014, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc) |
| 807 |
>>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc) |
| 808 |
>>> Experiment.objects.create(start_datetime=start1, start_time=start1.time()) |
| 809 |
>>> Experiment.objects.create(start_datetime=start2, start_time=start2.time()) |
| 810 |
>>> Experiment.objects.create(start_datetime=start3, start_time=start3.time()) |
| 811 |
>>> experiments_per_hour = ( |
| 812 |
... Experiment.objects.annotate( |
| 813 |
... hour=TruncHour("start_datetime", output_field=TimeField()), |
| 814 |
... ) |
| 815 |
... .values("hour") |
| 816 |
... .annotate(experiments=Count("id")) |
| 817 |
... ) |
| 818 |
>>> for exp in experiments_per_hour: |
| 819 |
... print(exp["hour"], exp["experiments"]) |
| 820 |
... |
| 821 |
14:00:00 2 |
| 822 |
17:00:00 1 |
| 823 |
|
| 824 |
>>> import zoneinfo |
| 825 |
>>> melb = zoneinfo.ZoneInfo("Australia/Melbourne") |
| 826 |
>>> experiments_per_hour = ( |
| 827 |
... Experiment.objects.annotate( |
| 828 |
... hour=TruncHour("start_datetime", tzinfo=melb), |
| 829 |
... ) |
| 830 |
... .values("hour") |
| 831 |
... .annotate(experiments=Count("id")) |
| 832 |
... ) |
| 833 |
>>> for exp in experiments_per_hour: |
| 834 |
... print(exp["hour"], exp["experiments"]) |
| 835 |
... |
| 836 |
2014-06-16 00:00:00+10:00 2 |
| 837 |
2016-01-01 04:00:00+11:00 1 |
| 838 |
|
| 839 |
.. _json-functions: |
| 840 |
|
| 841 |
JSON Functions |
| 842 |
============== |
| 843 |
|
| 844 |
``JSONArray`` |
| 845 |
------------- |
| 846 |
|
| 847 |
.. versionadded:: 5.2 |
| 848 |
|
| 849 |
.. class:: JSONArray(*expressions) |
| 850 |
|
| 851 |
Accepts a list of field names or expressions and returns a JSON array |
| 852 |
containing those values. |
| 853 |
|
| 854 |
Usage example: |
| 855 |
|
| 856 |
.. code-block:: pycon |
| 857 |
|
| 858 |
>>> from django.db.models import F |
| 859 |
>>> from django.db.models.functions import JSONArray, Lower |
| 860 |
>>> Author.objects.create(name="Margaret Smith", alias="msmith", age=25) |
| 861 |
>>> author = Author.objects.annotate( |
| 862 |
... json_array=JSONArray( |
| 863 |
... Lower("name"), |
| 864 |
... "alias", |
| 865 |
... F("age") * 2, |
| 866 |
... ) |
| 867 |
... ).get() |
| 868 |
>>> author.json_array |
| 869 |
['margaret smith', 'msmith', 50] |
| 870 |
|
| 871 |
``JSONObject`` |
| 872 |
-------------- |
| 873 |
|
| 874 |
.. class:: JSONObject(**fields) |
| 875 |
|
| 876 |
Takes a list of key-value pairs and returns a JSON object containing those |
| 877 |
pairs. |
| 878 |
|
| 879 |
Usage example: |
| 880 |
|
| 881 |
.. code-block:: pycon |
| 882 |
|
| 883 |
>>> from django.db.models import F |
| 884 |
>>> from django.db.models.functions import JSONObject, Lower |
| 885 |
>>> Author.objects.create(name="Margaret Smith", alias="msmith", age=25) |
| 886 |
>>> author = Author.objects.annotate( |
| 887 |
... json_object=JSONObject( |
| 888 |
... name=Lower("name"), |
| 889 |
... alias="alias", |
| 890 |
... age=F("age") * 2, |
| 891 |
... ) |
| 892 |
... ).get() |
| 893 |
>>> author.json_object |
| 894 |
{'name': 'margaret smith', 'alias': 'msmith', 'age': 50} |
| 895 |
|
| 896 |
.. _math-functions: |
| 897 |
|
| 898 |
Math Functions |
| 899 |
============== |
| 900 |
|
| 901 |
We'll be using the following model in math function examples:: |
| 902 |
|
| 903 |
class Vector(models.Model): |
| 904 |
x = models.FloatField() |
| 905 |
y = models.FloatField() |
| 906 |
|
| 907 |
``Abs`` |
| 908 |
------- |
| 909 |
|
| 910 |
.. class:: Abs(expression, **extra) |
| 911 |
|
| 912 |
Returns the absolute value of a numeric field or expression. |
| 913 |
|
| 914 |
Usage example: |
| 915 |
|
| 916 |
.. code-block:: pycon |
| 917 |
|
| 918 |
>>> from django.db.models.functions import Abs |
| 919 |
>>> Vector.objects.create(x=-0.5, y=1.1) |
| 920 |
>>> vector = Vector.objects.annotate(x_abs=Abs("x"), y_abs=Abs("y")).get() |
| 921 |
>>> vector.x_abs, vector.y_abs |
| 922 |
(0.5, 1.1) |
| 923 |
|
| 924 |
It can also be registered as a transform. For example: |
| 925 |
|
| 926 |
.. code-block:: pycon |
| 927 |
|
| 928 |
>>> from django.db.models import FloatField |
| 929 |
>>> from django.db.models.functions import Abs |
| 930 |
>>> FloatField.register_lookup(Abs) |
| 931 |
>>> # Get vectors inside the unit cube |
| 932 |
>>> vectors = Vector.objects.filter(x__abs__lt=1, y__abs__lt=1) |
| 933 |
|
| 934 |
``ACos`` |
| 935 |
-------- |
| 936 |
|
| 937 |
.. class:: ACos(expression, **extra) |
| 938 |
|
| 939 |
Returns the arccosine of a numeric field or expression. The expression value |
| 940 |
must be within the range -1 to 1. |
| 941 |
|
| 942 |
Usage example: |
| 943 |
|
| 944 |
.. code-block:: pycon |
| 945 |
|
| 946 |
>>> from django.db.models.functions import ACos |
| 947 |
>>> Vector.objects.create(x=0.5, y=-0.9) |
| 948 |
>>> vector = Vector.objects.annotate(x_acos=ACos("x"), y_acos=ACos("y")).get() |
| 949 |
>>> vector.x_acos, vector.y_acos |
| 950 |
(1.0471975511965979, 2.6905658417935308) |
| 951 |
|
| 952 |
It can also be registered as a transform. For example: |
| 953 |
|
| 954 |
.. code-block:: pycon |
| 955 |
|
| 956 |
>>> from django.db.models import FloatField |
| 957 |
>>> from django.db.models.functions import ACos |
| 958 |
>>> FloatField.register_lookup(ACos) |
| 959 |
>>> # Get vectors whose arccosine is less than 1 |
| 960 |
>>> vectors = Vector.objects.filter(x__acos__lt=1, y__acos__lt=1) |
| 961 |
|
| 962 |
``ASin`` |
| 963 |
-------- |
| 964 |
|
| 965 |
.. class:: ASin(expression, **extra) |
| 966 |
|
| 967 |
Returns the arcsine of a numeric field or expression. The expression value must |
| 968 |
be in the range -1 to 1. |
| 969 |
|
| 970 |
Usage example: |
| 971 |
|
| 972 |
.. code-block:: pycon |
| 973 |
|
| 974 |
>>> from django.db.models.functions import ASin |
| 975 |
>>> Vector.objects.create(x=0, y=1) |
| 976 |
>>> vector = Vector.objects.annotate(x_asin=ASin("x"), y_asin=ASin("y")).get() |
| 977 |
>>> vector.x_asin, vector.y_asin |
| 978 |
(0.0, 1.5707963267948966) |
| 979 |
|
| 980 |
It can also be registered as a transform. For example: |
| 981 |
|
| 982 |
.. code-block:: pycon |
| 983 |
|
| 984 |
>>> from django.db.models import FloatField |
| 985 |
>>> from django.db.models.functions import ASin |
| 986 |
>>> FloatField.register_lookup(ASin) |
| 987 |
>>> # Get vectors whose arcsine is less than 1 |
| 988 |
>>> vectors = Vector.objects.filter(x__asin__lt=1, y__asin__lt=1) |
| 989 |
|
| 990 |
``ATan`` |
| 991 |
-------- |
| 992 |
|
| 993 |
.. class:: ATan(expression, **extra) |
| 994 |
|
| 995 |
Returns the arctangent of a numeric field or expression. |
| 996 |
|
| 997 |
Usage example: |
| 998 |
|
| 999 |
.. code-block:: pycon |
| 1000 |
|
| 1001 |
>>> from django.db.models.functions import ATan |
| 1002 |
>>> Vector.objects.create(x=3.12, y=6.987) |
| 1003 |
>>> vector = Vector.objects.annotate(x_atan=ATan("x"), y_atan=ATan("y")).get() |
| 1004 |
>>> vector.x_atan, vector.y_atan |
| 1005 |
(1.2606282660069106, 1.428638798133829) |
| 1006 |
|
| 1007 |
It can also be registered as a transform. For example: |
| 1008 |
|
| 1009 |
.. code-block:: pycon |
| 1010 |
|
| 1011 |
>>> from django.db.models import FloatField |
| 1012 |
>>> from django.db.models.functions import ATan |
| 1013 |
>>> FloatField.register_lookup(ATan) |
| 1014 |
>>> # Get vectors whose arctangent is less than 2 |
| 1015 |
>>> vectors = Vector.objects.filter(x__atan__lt=2, y__atan__lt=2) |
| 1016 |
|
| 1017 |
``ATan2`` |
| 1018 |
--------- |
| 1019 |
|
| 1020 |
.. class:: ATan2(expression1, expression2, **extra) |
| 1021 |
|
| 1022 |
Returns the arctangent of ``expression1 / expression2``. |
| 1023 |
|
| 1024 |
Usage example: |
| 1025 |
|
| 1026 |
.. code-block:: pycon |
| 1027 |
|
| 1028 |
>>> from django.db.models.functions import ATan2 |
| 1029 |
>>> Vector.objects.create(x=2.5, y=1.9) |
| 1030 |
>>> vector = Vector.objects.annotate(atan2=ATan2("x", "y")).get() |
| 1031 |
>>> vector.atan2 |
| 1032 |
0.9209258773829491 |
| 1033 |
|
| 1034 |
``Ceil`` |
| 1035 |
-------- |
| 1036 |
|
| 1037 |
.. class:: Ceil(expression, **extra) |
| 1038 |
|
| 1039 |
Returns the smallest integer greater than or equal to a numeric field or |
| 1040 |
expression. |
| 1041 |
|
| 1042 |
Usage example: |
| 1043 |
|
| 1044 |
.. code-block:: pycon |
| 1045 |
|
| 1046 |
>>> from django.db.models.functions import Ceil |
| 1047 |
>>> Vector.objects.create(x=3.12, y=7.0) |
| 1048 |
>>> vector = Vector.objects.annotate(x_ceil=Ceil("x"), y_ceil=Ceil("y")).get() |
| 1049 |
>>> vector.x_ceil, vector.y_ceil |
| 1050 |
(4.0, 7.0) |
| 1051 |
|
| 1052 |
It can also be registered as a transform. For example: |
| 1053 |
|
| 1054 |
.. code-block:: pycon |
| 1055 |
|
| 1056 |
>>> from django.db.models import FloatField |
| 1057 |
>>> from django.db.models.functions import Ceil |
| 1058 |
>>> FloatField.register_lookup(Ceil) |
| 1059 |
>>> # Get vectors whose ceil is less than 10 |
| 1060 |
>>> vectors = Vector.objects.filter(x__ceil__lt=10, y__ceil__lt=10) |
| 1061 |
|
| 1062 |
``Cos`` |
| 1063 |
------- |
| 1064 |
|
| 1065 |
.. class:: Cos(expression, **extra) |
| 1066 |
|
| 1067 |
Returns the cosine of a numeric field or expression. |
| 1068 |
|
| 1069 |
Usage example: |
| 1070 |
|
| 1071 |
.. code-block:: pycon |
| 1072 |
|
| 1073 |
>>> from django.db.models.functions import Cos |
| 1074 |
>>> Vector.objects.create(x=-8.0, y=3.1415926) |
| 1075 |
>>> vector = Vector.objects.annotate(x_cos=Cos("x"), y_cos=Cos("y")).get() |
| 1076 |
>>> vector.x_cos, vector.y_cos |
| 1077 |
(-0.14550003380861354, -0.9999999999999986) |
| 1078 |
|
| 1079 |
It can also be registered as a transform. For example: |
| 1080 |
|
| 1081 |
.. code-block:: pycon |
| 1082 |
|
| 1083 |
>>> from django.db.models import FloatField |
| 1084 |
>>> from django.db.models.functions import Cos |
| 1085 |
>>> FloatField.register_lookup(Cos) |
| 1086 |
>>> # Get vectors whose cosine is less than 0.5 |
| 1087 |
>>> vectors = Vector.objects.filter(x__cos__lt=0.5, y__cos__lt=0.5) |
| 1088 |
|
| 1089 |
``Cot`` |
| 1090 |
------- |
| 1091 |
|
| 1092 |
.. class:: Cot(expression, **extra) |
| 1093 |
|
| 1094 |
Returns the cotangent of a numeric field or expression. |
| 1095 |
|
| 1096 |
Usage example: |
| 1097 |
|
| 1098 |
.. code-block:: pycon |
| 1099 |
|
| 1100 |
>>> from django.db.models.functions import Cot |
| 1101 |
>>> Vector.objects.create(x=12.0, y=1.0) |
| 1102 |
>>> vector = Vector.objects.annotate(x_cot=Cot("x"), y_cot=Cot("y")).get() |
| 1103 |
>>> vector.x_cot, vector.y_cot |
| 1104 |
(-1.5726734063976826, 0.642092615934331) |
| 1105 |
|
| 1106 |
It can also be registered as a transform. For example: |
| 1107 |
|
| 1108 |
.. code-block:: pycon |
| 1109 |
|
| 1110 |
>>> from django.db.models import FloatField |
| 1111 |
>>> from django.db.models.functions import Cot |
| 1112 |
>>> FloatField.register_lookup(Cot) |
| 1113 |
>>> # Get vectors whose cotangent is less than 1 |
| 1114 |
>>> vectors = Vector.objects.filter(x__cot__lt=1, y__cot__lt=1) |
| 1115 |
|
| 1116 |
``Degrees`` |
| 1117 |
----------- |
| 1118 |
|
| 1119 |
.. class:: Degrees(expression, **extra) |
| 1120 |
|
| 1121 |
Converts a numeric field or expression from radians to degrees. |
| 1122 |
|
| 1123 |
Usage example: |
| 1124 |
|
| 1125 |
.. code-block:: pycon |
| 1126 |
|
| 1127 |
>>> from django.db.models.functions import Degrees |
| 1128 |
>>> Vector.objects.create(x=-1.57, y=3.14) |
| 1129 |
>>> vector = Vector.objects.annotate(x_d=Degrees("x"), y_d=Degrees("y")).get() |
| 1130 |
>>> vector.x_d, vector.y_d |
| 1131 |
(-89.95437383553924, 179.9087476710785) |
| 1132 |
|
| 1133 |
It can also be registered as a transform. For example: |
| 1134 |
|
| 1135 |
.. code-block:: pycon |
| 1136 |
|
| 1137 |
>>> from django.db.models import FloatField |
| 1138 |
>>> from django.db.models.functions import Degrees |
| 1139 |
>>> FloatField.register_lookup(Degrees) |
| 1140 |
>>> # Get vectors whose degrees are less than 360 |
| 1141 |
>>> vectors = Vector.objects.filter(x__degrees__lt=360, y__degrees__lt=360) |
| 1142 |
|
| 1143 |
``Exp`` |
| 1144 |
------- |
| 1145 |
|
| 1146 |
.. class:: Exp(expression, **extra) |
| 1147 |
|
| 1148 |
Returns the value of ``e`` (the natural logarithm base) raised to the power of |
| 1149 |
a numeric field or expression. |
| 1150 |
|
| 1151 |
Usage example: |
| 1152 |
|
| 1153 |
.. code-block:: pycon |
| 1154 |
|
| 1155 |
>>> from django.db.models.functions import Exp |
| 1156 |
>>> Vector.objects.create(x=5.4, y=-2.0) |
| 1157 |
>>> vector = Vector.objects.annotate(x_exp=Exp("x"), y_exp=Exp("y")).get() |
| 1158 |
>>> vector.x_exp, vector.y_exp |
| 1159 |
(221.40641620418717, 0.1353352832366127) |
| 1160 |
|
| 1161 |
It can also be registered as a transform. For example: |
| 1162 |
|
| 1163 |
.. code-block:: pycon |
| 1164 |
|
| 1165 |
>>> from django.db.models import FloatField |
| 1166 |
>>> from django.db.models.functions import Exp |
| 1167 |
>>> FloatField.register_lookup(Exp) |
| 1168 |
>>> # Get vectors whose exp() is greater than 10 |
| 1169 |
>>> vectors = Vector.objects.filter(x__exp__gt=10, y__exp__gt=10) |
| 1170 |
|
| 1171 |
``Floor`` |
| 1172 |
--------- |
| 1173 |
|
| 1174 |
.. class:: Floor(expression, **extra) |
| 1175 |
|
| 1176 |
Returns the largest integer value not greater than a numeric field or |
| 1177 |
expression. |
| 1178 |
|
| 1179 |
Usage example: |
| 1180 |
|
| 1181 |
.. code-block:: pycon |
| 1182 |
|
| 1183 |
>>> from django.db.models.functions import Floor |
| 1184 |
>>> Vector.objects.create(x=5.4, y=-2.3) |
| 1185 |
>>> vector = Vector.objects.annotate(x_floor=Floor("x"), y_floor=Floor("y")).get() |
| 1186 |
>>> vector.x_floor, vector.y_floor |
| 1187 |
(5.0, -3.0) |
| 1188 |
|
| 1189 |
It can also be registered as a transform. For example: |
| 1190 |
|
| 1191 |
.. code-block:: pycon |
| 1192 |
|
| 1193 |
>>> from django.db.models import FloatField |
| 1194 |
>>> from django.db.models.functions import Floor |
| 1195 |
>>> FloatField.register_lookup(Floor) |
| 1196 |
>>> # Get vectors whose floor() is greater than 10 |
| 1197 |
>>> vectors = Vector.objects.filter(x__floor__gt=10, y__floor__gt=10) |
| 1198 |
|
| 1199 |
``Ln`` |
| 1200 |
------ |
| 1201 |
|
| 1202 |
.. class:: Ln(expression, **extra) |
| 1203 |
|
| 1204 |
Returns the natural logarithm a numeric field or expression. |
| 1205 |
|
| 1206 |
Usage example: |
| 1207 |
|
| 1208 |
.. code-block:: pycon |
| 1209 |
|
| 1210 |
>>> from django.db.models.functions import Ln |
| 1211 |
>>> Vector.objects.create(x=5.4, y=233.0) |
| 1212 |
>>> vector = Vector.objects.annotate(x_ln=Ln("x"), y_ln=Ln("y")).get() |
| 1213 |
>>> vector.x_ln, vector.y_ln |
| 1214 |
(1.6863989535702288, 5.4510384535657) |
| 1215 |
|
| 1216 |
It can also be registered as a transform. For example: |
| 1217 |
|
| 1218 |
.. code-block:: pycon |
| 1219 |
|
| 1220 |
>>> from django.db.models import FloatField |
| 1221 |
>>> from django.db.models.functions import Ln |
| 1222 |
>>> FloatField.register_lookup(Ln) |
| 1223 |
>>> # Get vectors whose value greater than e |
| 1224 |
>>> vectors = Vector.objects.filter(x__ln__gt=1, y__ln__gt=1) |
| 1225 |
|
| 1226 |
``Log`` |
| 1227 |
------- |
| 1228 |
|
| 1229 |
.. class:: Log(expression1, expression2, **extra) |
| 1230 |
|
| 1231 |
Accepts two numeric fields or expressions and returns the logarithm of |
| 1232 |
the second to base of the first. |
| 1233 |
|
| 1234 |
Usage example: |
| 1235 |
|
| 1236 |
.. code-block:: pycon |
| 1237 |
|
| 1238 |
>>> from django.db.models.functions import Log |
| 1239 |
>>> Vector.objects.create(x=2.0, y=4.0) |
| 1240 |
>>> vector = Vector.objects.annotate(log=Log("x", "y")).get() |
| 1241 |
>>> vector.log |
| 1242 |
2.0 |
| 1243 |
|
| 1244 |
``Mod`` |
| 1245 |
------- |
| 1246 |
|
| 1247 |
.. class:: Mod(expression1, expression2, **extra) |
| 1248 |
|
| 1249 |
Accepts two numeric fields or expressions and returns the remainder of |
| 1250 |
the first divided by the second (modulo operation). |
| 1251 |
|
| 1252 |
Usage example: |
| 1253 |
|
| 1254 |
.. code-block:: pycon |
| 1255 |
|
| 1256 |
>>> from django.db.models.functions import Mod |
| 1257 |
>>> Vector.objects.create(x=5.4, y=2.3) |
| 1258 |
>>> vector = Vector.objects.annotate(mod=Mod("x", "y")).get() |
| 1259 |
>>> vector.mod |
| 1260 |
0.8 |
| 1261 |
|
| 1262 |
``Pi`` |
| 1263 |
------ |
| 1264 |
|
| 1265 |
.. class:: Pi(**extra) |
| 1266 |
|
| 1267 |
Returns the value of the mathematical constant ``π``. |
| 1268 |
|
| 1269 |
``Power`` |
| 1270 |
--------- |
| 1271 |
|
| 1272 |
.. class:: Power(expression1, expression2, **extra) |
| 1273 |
|
| 1274 |
Accepts two numeric fields or expressions and returns the value of the first |
| 1275 |
raised to the power of the second. |
| 1276 |
|
| 1277 |
Usage example: |
| 1278 |
|
| 1279 |
.. code-block:: pycon |
| 1280 |
|
| 1281 |
>>> from django.db.models.functions import Power |
| 1282 |
>>> Vector.objects.create(x=2, y=-2) |
| 1283 |
>>> vector = Vector.objects.annotate(power=Power("x", "y")).get() |
| 1284 |
>>> vector.power |
| 1285 |
0.25 |
| 1286 |
|
| 1287 |
``Radians`` |
| 1288 |
----------- |
| 1289 |
|
| 1290 |
.. class:: Radians(expression, **extra) |
| 1291 |
|
| 1292 |
Converts a numeric field or expression from degrees to radians. |
| 1293 |
|
| 1294 |
Usage example: |
| 1295 |
|
| 1296 |
.. code-block:: pycon |
| 1297 |
|
| 1298 |
>>> from django.db.models.functions import Radians |
| 1299 |
>>> Vector.objects.create(x=-90, y=180) |
| 1300 |
>>> vector = Vector.objects.annotate(x_r=Radians("x"), y_r=Radians("y")).get() |
| 1301 |
>>> vector.x_r, vector.y_r |
| 1302 |
(-1.5707963267948966, 3.141592653589793) |
| 1303 |
|
| 1304 |
It can also be registered as a transform. For example: |
| 1305 |
|
| 1306 |
.. code-block:: pycon |
| 1307 |
|
| 1308 |
>>> from django.db.models import FloatField |
| 1309 |
>>> from django.db.models.functions import Radians |
| 1310 |
>>> FloatField.register_lookup(Radians) |
| 1311 |
>>> # Get vectors whose radians are less than 1 |
| 1312 |
>>> vectors = Vector.objects.filter(x__radians__lt=1, y__radians__lt=1) |
| 1313 |
|
| 1314 |
``Random`` |
| 1315 |
---------- |
| 1316 |
|
| 1317 |
.. class:: Random(**extra) |
| 1318 |
|
| 1319 |
Returns a random value in the range ``0.0 ≤ x < 1.0``. |
| 1320 |
|
| 1321 |
``Round`` |
| 1322 |
--------- |
| 1323 |
|
| 1324 |
.. class:: Round(expression, precision=0, **extra) |
| 1325 |
|
| 1326 |
Rounds a numeric field or expression to ``precision`` (must be an integer) |
| 1327 |
decimal places. By default, it rounds to the nearest integer. Whether half |
| 1328 |
values are rounded up or down depends on the database. |
| 1329 |
|
| 1330 |
Usage example: |
| 1331 |
|
| 1332 |
.. code-block:: pycon |
| 1333 |
|
| 1334 |
>>> from django.db.models.functions import Round |
| 1335 |
>>> Vector.objects.create(x=5.4, y=-2.37) |
| 1336 |
>>> vector = Vector.objects.annotate(x_r=Round("x"), y_r=Round("y", precision=1)).get() |
| 1337 |
>>> vector.x_r, vector.y_r |
| 1338 |
(5.0, -2.4) |
| 1339 |
|
| 1340 |
It can also be registered as a transform. For example: |
| 1341 |
|
| 1342 |
.. code-block:: pycon |
| 1343 |
|
| 1344 |
>>> from django.db.models import FloatField |
| 1345 |
>>> from django.db.models.functions import Round |
| 1346 |
>>> FloatField.register_lookup(Round) |
| 1347 |
>>> # Get vectors whose round() is less than 20 |
| 1348 |
>>> vectors = Vector.objects.filter(x__round__lt=20, y__round__lt=20) |
| 1349 |
|
| 1350 |
``Sign`` |
| 1351 |
-------- |
| 1352 |
|
| 1353 |
.. class:: Sign(expression, **extra) |
| 1354 |
|
| 1355 |
Returns the sign (-1, 0, 1) of a numeric field or expression. |
| 1356 |
|
| 1357 |
Usage example: |
| 1358 |
|
| 1359 |
.. code-block:: pycon |
| 1360 |
|
| 1361 |
>>> from django.db.models.functions import Sign |
| 1362 |
>>> Vector.objects.create(x=5.4, y=-2.3) |
| 1363 |
>>> vector = Vector.objects.annotate(x_sign=Sign("x"), y_sign=Sign("y")).get() |
| 1364 |
>>> vector.x_sign, vector.y_sign |
| 1365 |
(1, -1) |
| 1366 |
|
| 1367 |
It can also be registered as a transform. For example: |
| 1368 |
|
| 1369 |
.. code-block:: pycon |
| 1370 |
|
| 1371 |
>>> from django.db.models import FloatField |
| 1372 |
>>> from django.db.models.functions import Sign |
| 1373 |
>>> FloatField.register_lookup(Sign) |
| 1374 |
>>> # Get vectors whose signs of components are less than 0. |
| 1375 |
>>> vectors = Vector.objects.filter(x__sign__lt=0, y__sign__lt=0) |
| 1376 |
|
| 1377 |
``Sin`` |
| 1378 |
------- |
| 1379 |
|
| 1380 |
.. class:: Sin(expression, **extra) |
| 1381 |
|
| 1382 |
Returns the sine of a numeric field or expression. |
| 1383 |
|
| 1384 |
Usage example: |
| 1385 |
|
| 1386 |
.. code-block:: pycon |
| 1387 |
|
| 1388 |
>>> from django.db.models.functions import Sin |
| 1389 |
>>> Vector.objects.create(x=5.4, y=-2.3) |
| 1390 |
>>> vector = Vector.objects.annotate(x_sin=Sin("x"), y_sin=Sin("y")).get() |
| 1391 |
>>> vector.x_sin, vector.y_sin |
| 1392 |
(-0.7727644875559871, -0.7457052121767203) |
| 1393 |
|
| 1394 |
It can also be registered as a transform. For example: |
| 1395 |
|
| 1396 |
.. code-block:: pycon |
| 1397 |
|
| 1398 |
>>> from django.db.models import FloatField |
| 1399 |
>>> from django.db.models.functions import Sin |
| 1400 |
>>> FloatField.register_lookup(Sin) |
| 1401 |
>>> # Get vectors whose sin() is less than 0 |
| 1402 |
>>> vectors = Vector.objects.filter(x__sin__lt=0, y__sin__lt=0) |
| 1403 |
|
| 1404 |
``Sqrt`` |
| 1405 |
-------- |
| 1406 |
|
| 1407 |
.. class:: Sqrt(expression, **extra) |
| 1408 |
|
| 1409 |
Returns the square root of a nonnegative numeric field or expression. |
| 1410 |
|
| 1411 |
Usage example: |
| 1412 |
|
| 1413 |
.. code-block:: pycon |
| 1414 |
|
| 1415 |
>>> from django.db.models.functions import Sqrt |
| 1416 |
>>> Vector.objects.create(x=4.0, y=12.0) |
| 1417 |
>>> vector = Vector.objects.annotate(x_sqrt=Sqrt("x"), y_sqrt=Sqrt("y")).get() |
| 1418 |
>>> vector.x_sqrt, vector.y_sqrt |
| 1419 |
(2.0, 3.46410) |
| 1420 |
|
| 1421 |
It can also be registered as a transform. For example: |
| 1422 |
|
| 1423 |
.. code-block:: pycon |
| 1424 |
|
| 1425 |
>>> from django.db.models import FloatField |
| 1426 |
>>> from django.db.models.functions import Sqrt |
| 1427 |
>>> FloatField.register_lookup(Sqrt) |
| 1428 |
>>> # Get vectors whose sqrt() is less than 5 |
| 1429 |
>>> vectors = Vector.objects.filter(x__sqrt__lt=5, y__sqrt__lt=5) |
| 1430 |
|
| 1431 |
``Tan`` |
| 1432 |
------- |
| 1433 |
|
| 1434 |
.. class:: Tan(expression, **extra) |
| 1435 |
|
| 1436 |
Returns the tangent of a numeric field or expression. |
| 1437 |
|
| 1438 |
Usage example: |
| 1439 |
|
| 1440 |
.. code-block:: pycon |
| 1441 |
|
| 1442 |
>>> from django.db.models.functions import Tan |
| 1443 |
>>> Vector.objects.create(x=0, y=12) |
| 1444 |
>>> vector = Vector.objects.annotate(x_tan=Tan("x"), y_tan=Tan("y")).get() |
| 1445 |
>>> vector.x_tan, vector.y_tan |
| 1446 |
(0.0, -0.6358599286615808) |
| 1447 |
|
| 1448 |
It can also be registered as a transform. For example: |
| 1449 |
|
| 1450 |
.. code-block:: pycon |
| 1451 |
|
| 1452 |
>>> from django.db.models import FloatField |
| 1453 |
>>> from django.db.models.functions import Tan |
| 1454 |
>>> FloatField.register_lookup(Tan) |
| 1455 |
>>> # Get vectors whose tangent is less than 0 |
| 1456 |
>>> vectors = Vector.objects.filter(x__tan__lt=0, y__tan__lt=0) |
| 1457 |
|
| 1458 |
.. _text-functions: |
| 1459 |
|
| 1460 |
Text functions |
| 1461 |
============== |
| 1462 |
|
| 1463 |
``Chr`` |
| 1464 |
------- |
| 1465 |
|
| 1466 |
.. class:: Chr(expression, **extra) |
| 1467 |
|
| 1468 |
Accepts a numeric field or expression and returns the text representation of |
| 1469 |
the expression as a single character. It works the same as Python's :func:`chr` |
| 1470 |
function. |
| 1471 |
|
| 1472 |
Like :class:`Length`, it can be registered as a transform on ``IntegerField``. |
| 1473 |
The default lookup name is ``chr``. |
| 1474 |
|
| 1475 |
Usage example: |
| 1476 |
|
| 1477 |
.. code-block:: pycon |
| 1478 |
|
| 1479 |
>>> from django.db.models.functions import Chr |
| 1480 |
>>> Author.objects.create(name="Margaret Smith") |
| 1481 |
>>> author = Author.objects.filter(name__startswith=Chr(ord("M"))).get() |
| 1482 |
>>> print(author.name) |
| 1483 |
Margaret Smith |
| 1484 |
|
| 1485 |
``Concat`` |
| 1486 |
---------- |
| 1487 |
|
| 1488 |
.. class:: Concat(*expressions, **extra) |
| 1489 |
|
| 1490 |
Accepts a list of at least two text fields or expressions and returns the |
| 1491 |
concatenated text. Each argument must be of a text or char type. If you want |
| 1492 |
to concatenate a ``TextField()`` with a ``CharField()``, then be sure to tell |
| 1493 |
Django that the ``output_field`` should be a ``TextField()``. Specifying an |
| 1494 |
``output_field`` is also required when concatenating a ``Value`` as in the |
| 1495 |
example below. |
| 1496 |
|
| 1497 |
This function will never have a null result. On backends where a null argument |
| 1498 |
results in the entire expression being null, Django will ensure that each null |
| 1499 |
part is converted to an empty string first. |
| 1500 |
|
| 1501 |
Usage example: |
| 1502 |
|
| 1503 |
.. code-block:: pycon |
| 1504 |
|
| 1505 |
>>> # Get the display name as "name (goes_by)" |
| 1506 |
>>> from django.db.models import CharField, Value as V |
| 1507 |
>>> from django.db.models.functions import Concat |
| 1508 |
>>> Author.objects.create(name="Margaret Smith", goes_by="Maggie") |
| 1509 |
>>> author = Author.objects.annotate( |
| 1510 |
... screen_name=Concat("name", V(" ("), "goes_by", V(")"), output_field=CharField()) |
| 1511 |
... ).get() |
| 1512 |
>>> print(author.screen_name) |
| 1513 |
Margaret Smith (Maggie) |
| 1514 |
|
| 1515 |
``Left`` |
| 1516 |
-------- |
| 1517 |
|
| 1518 |
.. class:: Left(expression, length, **extra) |
| 1519 |
|
| 1520 |
Returns the first ``length`` characters of the given text field or expression. |
| 1521 |
|
| 1522 |
Usage example: |
| 1523 |
|
| 1524 |
.. code-block:: pycon |
| 1525 |
|
| 1526 |
>>> from django.db.models.functions import Left |
| 1527 |
>>> Author.objects.create(name="Margaret Smith") |
| 1528 |
>>> author = Author.objects.annotate(first_initial=Left("name", 1)).get() |
| 1529 |
>>> print(author.first_initial) |
| 1530 |
M |
| 1531 |
|
| 1532 |
``Length`` |
| 1533 |
---------- |
| 1534 |
|
| 1535 |
.. class:: Length(expression, **extra) |
| 1536 |
|
| 1537 |
Accepts a single text field or expression and returns the number of characters |
| 1538 |
the value has. If the expression is null, then the length will also be null. |
| 1539 |
|
| 1540 |
Usage example: |
| 1541 |
|
| 1542 |
.. code-block:: pycon |
| 1543 |
|
| 1544 |
>>> # Get the length of the name and goes_by fields |
| 1545 |
>>> from django.db.models.functions import Length |
| 1546 |
>>> Author.objects.create(name="Margaret Smith") |
| 1547 |
>>> author = Author.objects.annotate( |
| 1548 |
... name_length=Length("name"), goes_by_length=Length("goes_by") |
| 1549 |
... ).get() |
| 1550 |
>>> print(author.name_length, author.goes_by_length) |
| 1551 |
(14, None) |
| 1552 |
|
| 1553 |
It can also be registered as a transform. For example: |
| 1554 |
|
| 1555 |
.. code-block:: pycon |
| 1556 |
|
| 1557 |
>>> from django.db.models import CharField |
| 1558 |
>>> from django.db.models.functions import Length |
| 1559 |
>>> CharField.register_lookup(Length) |
| 1560 |
>>> # Get authors whose name is longer than 7 characters |
| 1561 |
>>> authors = Author.objects.filter(name__length__gt=7) |
| 1562 |
|
| 1563 |
``Lower`` |
| 1564 |
--------- |
| 1565 |
|
| 1566 |
.. class:: Lower(expression, **extra) |
| 1567 |
|
| 1568 |
Accepts a single text field or expression and returns the lowercase |
| 1569 |
representation. |
| 1570 |
|
| 1571 |
It can also be registered as a transform as described in :class:`Length`. |
| 1572 |
|
| 1573 |
Usage example: |
| 1574 |
|
| 1575 |
.. code-block:: pycon |
| 1576 |
|
| 1577 |
>>> from django.db.models.functions import Lower |
| 1578 |
>>> Author.objects.create(name="Margaret Smith") |
| 1579 |
>>> author = Author.objects.annotate(name_lower=Lower("name")).get() |
| 1580 |
>>> print(author.name_lower) |
| 1581 |
margaret smith |
| 1582 |
|
| 1583 |
``LPad`` |
| 1584 |
-------- |
| 1585 |
|
| 1586 |
.. class:: LPad(expression, length, fill_text=Value(' '), **extra) |
| 1587 |
|
| 1588 |
Returns the value of the given text field or expression padded on the left side |
| 1589 |
with ``fill_text`` so that the resulting value is ``length`` characters long. |
| 1590 |
The default ``fill_text`` is a space. |
| 1591 |
|
| 1592 |
Usage example: |
| 1593 |
|
| 1594 |
.. code-block:: pycon |
| 1595 |
|
| 1596 |
>>> from django.db.models import Value |
| 1597 |
>>> from django.db.models.functions import LPad |
| 1598 |
>>> Author.objects.create(name="John", alias="j") |
| 1599 |
>>> Author.objects.update(name=LPad("name", 8, Value("abc"))) |
| 1600 |
1 |
| 1601 |
>>> print(Author.objects.get(alias="j").name) |
| 1602 |
abcaJohn |
| 1603 |
|
| 1604 |
``LTrim`` |
| 1605 |
--------- |
| 1606 |
|
| 1607 |
.. class:: LTrim(expression, **extra) |
| 1608 |
|
| 1609 |
Similar to :class:`~django.db.models.functions.Trim`, but removes only leading |
| 1610 |
spaces. |
| 1611 |
|
| 1612 |
``MD5`` |
| 1613 |
------- |
| 1614 |
|
| 1615 |
.. class:: MD5(expression, **extra) |
| 1616 |
|
| 1617 |
Accepts a single text field or expression and returns the MD5 hash of the |
| 1618 |
string. |
| 1619 |
|
| 1620 |
It can also be registered as a transform as described in :class:`Length`. |
| 1621 |
|
| 1622 |
Usage example: |
| 1623 |
|
| 1624 |
.. code-block:: pycon |
| 1625 |
|
| 1626 |
>>> from django.db.models.functions import MD5 |
| 1627 |
>>> Author.objects.create(name="Margaret Smith") |
| 1628 |
>>> author = Author.objects.annotate(name_md5=MD5("name")).get() |
| 1629 |
>>> print(author.name_md5) |
| 1630 |
749fb689816b2db85f5b169c2055b247 |
| 1631 |
|
| 1632 |
``Ord`` |
| 1633 |
------- |
| 1634 |
|
| 1635 |
.. class:: Ord(expression, **extra) |
| 1636 |
|
| 1637 |
Accepts a single text field or expression and returns the Unicode code point |
| 1638 |
value for the first character of that expression. It works similar to Python's |
| 1639 |
:func:`ord` function, but an exception isn't raised if the expression is more |
| 1640 |
than one character long. |
| 1641 |
|
| 1642 |
It can also be registered as a transform as described in :class:`Length`. |
| 1643 |
The default lookup name is ``ord``. |
| 1644 |
|
| 1645 |
Usage example: |
| 1646 |
|
| 1647 |
.. code-block:: pycon |
| 1648 |
|
| 1649 |
>>> from django.db.models.functions import Ord |
| 1650 |
>>> Author.objects.create(name="Margaret Smith") |
| 1651 |
>>> author = Author.objects.annotate(name_code_point=Ord("name")).get() |
| 1652 |
>>> print(author.name_code_point) |
| 1653 |
77 |
| 1654 |
|
| 1655 |
``Repeat`` |
| 1656 |
---------- |
| 1657 |
|
| 1658 |
.. class:: Repeat(expression, number, **extra) |
| 1659 |
|
| 1660 |
Returns the value of the given text field or expression repeated ``number`` |
| 1661 |
times. |
| 1662 |
|
| 1663 |
Usage example: |
| 1664 |
|
| 1665 |
.. code-block:: pycon |
| 1666 |
|
| 1667 |
>>> from django.db.models.functions import Repeat |
| 1668 |
>>> Author.objects.create(name="John", alias="j") |
| 1669 |
>>> Author.objects.update(name=Repeat("name", 3)) |
| 1670 |
1 |
| 1671 |
>>> print(Author.objects.get(alias="j").name) |
| 1672 |
JohnJohnJohn |
| 1673 |
|
| 1674 |
``Replace`` |
| 1675 |
----------- |
| 1676 |
|
| 1677 |
.. class:: Replace(expression, text, replacement=Value(''), **extra) |
| 1678 |
|
| 1679 |
Replaces all occurrences of ``text`` with ``replacement`` in ``expression``. |
| 1680 |
The default replacement text is the empty string. The arguments to the function |
| 1681 |
are case-sensitive. |
| 1682 |
|
| 1683 |
Usage example: |
| 1684 |
|
| 1685 |
.. code-block:: pycon |
| 1686 |
|
| 1687 |
>>> from django.db.models import Value |
| 1688 |
>>> from django.db.models.functions import Replace |
| 1689 |
>>> Author.objects.create(name="Margaret Johnson") |
| 1690 |
>>> Author.objects.create(name="Margaret Smith") |
| 1691 |
>>> Author.objects.update(name=Replace("name", Value("Margaret"), Value("Margareth"))) |
| 1692 |
2 |
| 1693 |
>>> Author.objects.values("name") |
| 1694 |
<QuerySet [{'name': 'Margareth Johnson'}, {'name': 'Margareth Smith'}]> |
| 1695 |
|
| 1696 |
``Reverse`` |
| 1697 |
----------- |
| 1698 |
|
| 1699 |
.. class:: Reverse(expression, **extra) |
| 1700 |
|
| 1701 |
Accepts a single text field or expression and returns the characters of that |
| 1702 |
expression in reverse order. |
| 1703 |
|
| 1704 |
It can also be registered as a transform as described in :class:`Length`. The |
| 1705 |
default lookup name is ``reverse``. |
| 1706 |
|
| 1707 |
Usage example: |
| 1708 |
|
| 1709 |
.. code-block:: pycon |
| 1710 |
|
| 1711 |
>>> from django.db.models.functions import Reverse |
| 1712 |
>>> Author.objects.create(name="Margaret Smith") |
| 1713 |
>>> author = Author.objects.annotate(backward=Reverse("name")).get() |
| 1714 |
>>> print(author.backward) |
| 1715 |
htimS teragraM |
| 1716 |
|
| 1717 |
``Right`` |
| 1718 |
--------- |
| 1719 |
|
| 1720 |
.. class:: Right(expression, length, **extra) |
| 1721 |
|
| 1722 |
Returns the last ``length`` characters of the given text field or expression. |
| 1723 |
|
| 1724 |
Usage example: |
| 1725 |
|
| 1726 |
.. code-block:: pycon |
| 1727 |
|
| 1728 |
>>> from django.db.models.functions import Right |
| 1729 |
>>> Author.objects.create(name="Margaret Smith") |
| 1730 |
>>> author = Author.objects.annotate(last_letter=Right("name", 1)).get() |
| 1731 |
>>> print(author.last_letter) |
| 1732 |
h |
| 1733 |
|
| 1734 |
``RPad`` |
| 1735 |
-------- |
| 1736 |
|
| 1737 |
.. class:: RPad(expression, length, fill_text=Value(' '), **extra) |
| 1738 |
|
| 1739 |
Similar to :class:`~django.db.models.functions.LPad`, but pads on the right |
| 1740 |
side. |
| 1741 |
|
| 1742 |
``RTrim`` |
| 1743 |
--------- |
| 1744 |
|
| 1745 |
.. class:: RTrim(expression, **extra) |
| 1746 |
|
| 1747 |
Similar to :class:`~django.db.models.functions.Trim`, but removes only trailing |
| 1748 |
spaces. |
| 1749 |
|
| 1750 |
``SHA1``, ``SHA224``, ``SHA256``, ``SHA384``, and ``SHA512`` |
| 1751 |
------------------------------------------------------------ |
| 1752 |
|
| 1753 |
.. class:: SHA1(expression, **extra) |
| 1754 |
.. class:: SHA224(expression, **extra) |
| 1755 |
.. class:: SHA256(expression, **extra) |
| 1756 |
.. class:: SHA384(expression, **extra) |
| 1757 |
.. class:: SHA512(expression, **extra) |
| 1758 |
|
| 1759 |
Accepts a single text field or expression and returns the particular hash of |
| 1760 |
the string. |
| 1761 |
|
| 1762 |
They can also be registered as transforms as described in :class:`Length`. |
| 1763 |
|
| 1764 |
Usage example: |
| 1765 |
|
| 1766 |
.. code-block:: pycon |
| 1767 |
|
| 1768 |
>>> from django.db.models.functions import SHA1 |
| 1769 |
>>> Author.objects.create(name="Margaret Smith") |
| 1770 |
>>> author = Author.objects.annotate(name_sha1=SHA1("name")).get() |
| 1771 |
>>> print(author.name_sha1) |
| 1772 |
b87efd8a6c991c390be5a68e8a7945a7851c7e5c |
| 1773 |
|
| 1774 |
.. admonition:: PostgreSQL |
| 1775 |
|
| 1776 |
The `pgcrypto extension <https://www.postgresql.org/docs/current/ |
| 1777 |
pgcrypto.html>`_ must be installed. You can use the |
| 1778 |
:class:`~django.contrib.postgres.operations.CryptoExtension` migration |
| 1779 |
operation to install it. |
| 1780 |
|
| 1781 |
.. admonition:: Oracle |
| 1782 |
|
| 1783 |
Oracle doesn't support the ``SHA224`` function. |
| 1784 |
|
| 1785 |
``StrIndex`` |
| 1786 |
------------ |
| 1787 |
|
| 1788 |
.. class:: StrIndex(string, substring, **extra) |
| 1789 |
|
| 1790 |
Returns a positive integer corresponding to the 1-indexed position of the first |
| 1791 |
occurrence of ``substring`` inside ``string``, or 0 if ``substring`` is not |
| 1792 |
found. |
| 1793 |
|
| 1794 |
Usage example: |
| 1795 |
|
| 1796 |
.. code-block:: pycon |
| 1797 |
|
| 1798 |
>>> from django.db.models import Value as V |
| 1799 |
>>> from django.db.models.functions import StrIndex |
| 1800 |
>>> Author.objects.create(name="Margaret Smith") |
| 1801 |
>>> Author.objects.create(name="Smith, Margaret") |
| 1802 |
>>> Author.objects.create(name="Margaret Jackson") |
| 1803 |
>>> Author.objects.filter(name="Margaret Jackson").annotate( |
| 1804 |
... smith_index=StrIndex("name", V("Smith")) |
| 1805 |
... ).get().smith_index |
| 1806 |
0 |
| 1807 |
>>> authors = Author.objects.annotate(smith_index=StrIndex("name", V("Smith"))).filter( |
| 1808 |
... smith_index__gt=0 |
| 1809 |
... ) |
| 1810 |
<QuerySet [<Author: Margaret Smith>, <Author: Smith, Margaret>]> |
| 1811 |
|
| 1812 |
.. warning:: |
| 1813 |
|
| 1814 |
In MySQL, a database table's :ref:`collation<mysql-collation>` determines |
| 1815 |
whether string comparisons (such as the ``expression`` and ``substring`` of |
| 1816 |
this function) are case-sensitive. Comparisons are case-insensitive by |
| 1817 |
default. |
| 1818 |
|
| 1819 |
``Substr`` |
| 1820 |
---------- |
| 1821 |
|
| 1822 |
.. class:: Substr(expression, pos, length=None, **extra) |
| 1823 |
|
| 1824 |
Returns a substring of length ``length`` from the field or expression starting |
| 1825 |
at position ``pos``. The position is 1-indexed, so the position must be greater |
| 1826 |
than 0. If ``length`` is ``None``, then the rest of the string will be returned. |
| 1827 |
|
| 1828 |
Usage example: |
| 1829 |
|
| 1830 |
.. code-block:: pycon |
| 1831 |
|
| 1832 |
>>> # Set the alias to the first 5 characters of the name as lowercase |
| 1833 |
>>> from django.db.models.functions import Lower, Substr |
| 1834 |
>>> Author.objects.create(name="Margaret Smith") |
| 1835 |
>>> Author.objects.update(alias=Lower(Substr("name", 1, 5))) |
| 1836 |
1 |
| 1837 |
>>> print(Author.objects.get(name="Margaret Smith").alias) |
| 1838 |
marga |
| 1839 |
|
| 1840 |
``Trim`` |
| 1841 |
-------- |
| 1842 |
|
| 1843 |
.. class:: Trim(expression, **extra) |
| 1844 |
|
| 1845 |
Returns the value of the given text field or expression with leading and |
| 1846 |
trailing spaces removed. |
| 1847 |
|
| 1848 |
Usage example: |
| 1849 |
|
| 1850 |
.. code-block:: pycon |
| 1851 |
|
| 1852 |
>>> from django.db.models.functions import Trim |
| 1853 |
>>> Author.objects.create(name=" John ", alias="j") |
| 1854 |
>>> Author.objects.update(name=Trim("name")) |
| 1855 |
1 |
| 1856 |
>>> print(Author.objects.get(alias="j").name) |
| 1857 |
John |
| 1858 |
|
| 1859 |
``Upper`` |
| 1860 |
--------- |
| 1861 |
|
| 1862 |
.. class:: Upper(expression, **extra) |
| 1863 |
|
| 1864 |
Accepts a single text field or expression and returns the uppercase |
| 1865 |
representation. |
| 1866 |
|
| 1867 |
It can also be registered as a transform as described in :class:`Length`. |
| 1868 |
|
| 1869 |
Usage example: |
| 1870 |
|
| 1871 |
.. code-block:: pycon |
| 1872 |
|
| 1873 |
>>> from django.db.models.functions import Upper |
| 1874 |
>>> Author.objects.create(name="Margaret Smith") |
| 1875 |
>>> author = Author.objects.annotate(name_upper=Upper("name")).get() |
| 1876 |
>>> print(author.name_upper) |
| 1877 |
MARGARET SMITH |
| 1878 |
|
| 1879 |
.. _window-functions: |
| 1880 |
|
| 1881 |
Window functions |
| 1882 |
================ |
| 1883 |
|
| 1884 |
There are a number of functions to use in a |
| 1885 |
:class:`~django.db.models.expressions.Window` expression for computing the rank |
| 1886 |
of elements or the :class:`Ntile` of some rows. |
| 1887 |
|
| 1888 |
``CumeDist`` |
| 1889 |
------------ |
| 1890 |
|
| 1891 |
.. class:: CumeDist(*expressions, **extra) |
| 1892 |
|
| 1893 |
Calculates the cumulative distribution of a value within a window or partition. |
| 1894 |
The cumulative distribution is defined as the number of rows preceding or |
| 1895 |
peered with the current row divided by the total number of rows in the frame. |
| 1896 |
|
| 1897 |
``DenseRank`` |
| 1898 |
------------- |
| 1899 |
|
| 1900 |
.. class:: DenseRank(*expressions, **extra) |
| 1901 |
|
| 1902 |
Equivalent to :class:`Rank` but does not have gaps. |
| 1903 |
|
| 1904 |
``FirstValue`` |
| 1905 |
-------------- |
| 1906 |
|
| 1907 |
.. class:: FirstValue(expression, **extra) |
| 1908 |
|
| 1909 |
Returns the value evaluated at the row that's the first row of the window |
| 1910 |
frame, or ``None`` if no such value exists. |
| 1911 |
|
| 1912 |
``Lag`` |
| 1913 |
------- |
| 1914 |
|
| 1915 |
.. class:: Lag(expression, offset=1, default=None, **extra) |
| 1916 |
|
| 1917 |
Calculates the value offset by ``offset``, and if no row exists there, returns |
| 1918 |
``default``. |
| 1919 |
|
| 1920 |
``default`` must have the same type as the ``expression``, however, this is |
| 1921 |
only validated by the database and not in Python. |
| 1922 |
|
| 1923 |
.. admonition:: MariaDB and ``default`` |
| 1924 |
|
| 1925 |
MariaDB `doesn't support <https://jira.mariadb.org/browse/MDEV-12981>`_ |
| 1926 |
the ``default`` parameter. |
| 1927 |
|
| 1928 |
``LastValue`` |
| 1929 |
------------- |
| 1930 |
|
| 1931 |
.. class:: LastValue(expression, **extra) |
| 1932 |
|
| 1933 |
Comparable to :class:`FirstValue`, it calculates the last value in a given |
| 1934 |
frame clause. |
| 1935 |
|
| 1936 |
``Lead`` |
| 1937 |
-------- |
| 1938 |
|
| 1939 |
.. class:: Lead(expression, offset=1, default=None, **extra) |
| 1940 |
|
| 1941 |
Calculates the leading value in a given :ref:`frame <window-frames>`. Both |
| 1942 |
``offset`` and ``default`` are evaluated with respect to the current row. |
| 1943 |
|
| 1944 |
``default`` must have the same type as the ``expression``, however, this is |
| 1945 |
only validated by the database and not in Python. |
| 1946 |
|
| 1947 |
.. admonition:: MariaDB and ``default`` |
| 1948 |
|
| 1949 |
MariaDB `doesn't support <https://jira.mariadb.org/browse/MDEV-12981>`_ |
| 1950 |
the ``default`` parameter. |
| 1951 |
|
| 1952 |
``NthValue`` |
| 1953 |
------------ |
| 1954 |
|
| 1955 |
.. class:: NthValue(expression, nth=1, **extra) |
| 1956 |
|
| 1957 |
Computes the row relative to the offset ``nth`` (must be a positive value) |
| 1958 |
within the window. Returns ``None`` if no row exists. |
| 1959 |
|
| 1960 |
Some databases may handle a nonexistent nth-value differently. For example, |
| 1961 |
Oracle returns an empty string rather than ``None`` for character-based |
| 1962 |
expressions. Django doesn't do any conversions in these cases. |
| 1963 |
|
| 1964 |
``Ntile`` |
| 1965 |
--------- |
| 1966 |
|
| 1967 |
.. class:: Ntile(num_buckets=1, **extra) |
| 1968 |
|
| 1969 |
Calculates a partition for each of the rows in the frame clause, distributing |
| 1970 |
numbers as evenly as possible between 1 and ``num_buckets``. If the rows don't |
| 1971 |
divide evenly into a number of buckets, one or more buckets will be represented |
| 1972 |
more frequently. |
| 1973 |
|
| 1974 |
``PercentRank`` |
| 1975 |
--------------- |
| 1976 |
|
| 1977 |
.. class:: PercentRank(*expressions, **extra) |
| 1978 |
|
| 1979 |
Computes the relative rank of the rows in the frame clause. This computation is |
| 1980 |
equivalent to evaluating: |
| 1981 |
|
| 1982 |
.. code-block:: text |
| 1983 |
|
| 1984 |
(rank - 1) / (total rows - 1) |
| 1985 |
|
| 1986 |
The following table explains the calculation for the relative rank of a row: |
| 1987 |
|
| 1988 |
===== ===== ==== ============ ============= |
| 1989 |
Row # Value Rank Calculation Relative Rank |
| 1990 |
===== ===== ==== ============ ============= |
| 1991 |
1 15 1 (1-1)/(7-1) 0.0000 |
| 1992 |
2 20 2 (2-1)/(7-1) 0.1666 |
| 1993 |
3 20 2 (2-1)/(7-1) 0.1666 |
| 1994 |
4 20 2 (2-1)/(7-1) 0.1666 |
| 1995 |
5 30 5 (5-1)/(7-1) 0.6666 |
| 1996 |
6 30 5 (5-1)/(7-1) 0.6666 |
| 1997 |
7 40 7 (7-1)/(7-1) 1.0000 |
| 1998 |
===== ===== ==== ============ ============= |
| 1999 |
|
| 2000 |
``Rank`` |
| 2001 |
-------- |
| 2002 |
|
| 2003 |
.. class:: Rank(*expressions, **extra) |
| 2004 |
|
| 2005 |
Comparable to :class:`RowNumber`, this function ranks rows in the window. The |
| 2006 |
computed rank contains gaps. Use :class:`DenseRank` to compute rank without |
| 2007 |
gaps. |
| 2008 |
|
| 2009 |
``RowNumber`` |
| 2010 |
------------- |
| 2011 |
|
| 2012 |
.. class:: RowNumber(*expressions, **extra) |
| 2013 |
|
| 2014 |
Computes the row number according to the ordering of either the frame clause |
| 2015 |
or the ordering of the whole query if there is no partitioning of the |
| 2016 |
:ref:`window frame <window-frames>`. |