Debian Codegraph @

docs/ref/models/database-functions.txt

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