database-functions.txt 57 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748
  1. ==================
  2. Database Functions
  3. ==================
  4. .. module:: django.db.models.functions
  5. :synopsis: Database Functions
  6. The classes documented below provide a way for users to use functions provided
  7. by the underlying database as annotations, aggregations, or filters in Django.
  8. Functions are also :doc:`expressions <expressions>`, so they can be used and
  9. combined with other expressions like :ref:`aggregate functions
  10. <aggregation-functions>`.
  11. We'll be using the following model in examples of each function::
  12. class Author(models.Model):
  13. name = models.CharField(max_length=50)
  14. age = models.PositiveIntegerField(null=True, blank=True)
  15. alias = models.CharField(max_length=50, null=True, blank=True)
  16. goes_by = models.CharField(max_length=50, null=True, blank=True)
  17. We don't usually recommend allowing ``null=True`` for ``CharField`` since this
  18. allows the field to have two "empty values", but it's important for the
  19. ``Coalesce`` example below.
  20. .. _comparison-functions:
  21. Comparison and conversion functions
  22. ===================================
  23. ``Cast``
  24. --------
  25. .. class:: Cast(expression, output_field)
  26. Forces the result type of ``expression`` to be the one from ``output_field``.
  27. Usage example::
  28. >>> from django.db.models import FloatField
  29. >>> from django.db.models.functions import Cast
  30. >>> Author.objects.create(age=25, name='Margaret Smith')
  31. >>> author = Author.objects.annotate(
  32. ... age_as_float=Cast('age', output_field=FloatField()),
  33. ... ).get()
  34. >>> print(author.age_as_float)
  35. 25.0
  36. ``Coalesce``
  37. ------------
  38. .. class:: Coalesce(*expressions, **extra)
  39. Accepts a list of at least two field names or expressions and returns the
  40. first non-null value (note that an empty string is not considered a null
  41. value). Each argument must be of a similar type, so mixing text and numbers
  42. will result in a database error.
  43. Usage examples::
  44. >>> # Get a screen name from least to most public
  45. >>> from django.db.models import Sum, Value as V
  46. >>> from django.db.models.functions import Coalesce
  47. >>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
  48. >>> author = Author.objects.annotate(
  49. ... screen_name=Coalesce('alias', 'goes_by', 'name')).get()
  50. >>> print(author.screen_name)
  51. Maggie
  52. >>> # Prevent an aggregate Sum() from returning None
  53. >>> aggregated = Author.objects.aggregate(
  54. ... combined_age=Coalesce(Sum('age'), V(0)),
  55. ... combined_age_default=Sum('age'))
  56. >>> print(aggregated['combined_age'])
  57. 0
  58. >>> print(aggregated['combined_age_default'])
  59. None
  60. .. warning::
  61. A Python value passed to ``Coalesce`` on MySQL may be converted to an
  62. incorrect type unless explicitly cast to the correct database type:
  63. >>> from django.db.models import DateTimeField
  64. >>> from django.db.models.functions import Cast, Coalesce
  65. >>> from django.utils import timezone
  66. >>> now = timezone.now()
  67. >>> Coalesce('updated', Cast(now, DateTimeField()))
  68. ``Collate``
  69. -----------
  70. .. class:: Collate(expression, collation)
  71. .. versionadded:: 3.2
  72. Takes an expression and a collation name to query against.
  73. For example, to filter case-insensitively in SQLite::
  74. >>> Author.objects.filter(name=Collate(Value('john'), 'nocase'))
  75. <QuerySet [<Author: John>, <Author: john>]>
  76. It can also be used when ordering, for example with PostgreSQL::
  77. >>> Author.objects.order_by(Collate('name', 'et-x-icu'))
  78. <QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]>
  79. ``Greatest``
  80. ------------
  81. .. class:: Greatest(*expressions, **extra)
  82. Accepts a list of at least two field names or expressions and returns the
  83. greatest value. Each argument must be of a similar type, so mixing text and
  84. numbers will result in a database error.
  85. Usage example::
  86. class Blog(models.Model):
  87. body = models.TextField()
  88. modified = models.DateTimeField(auto_now=True)
  89. class Comment(models.Model):
  90. body = models.TextField()
  91. modified = models.DateTimeField(auto_now=True)
  92. blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
  93. >>> from django.db.models.functions import Greatest
  94. >>> blog = Blog.objects.create(body='Greatest is the best.')
  95. >>> comment = Comment.objects.create(body='No, Least is better.', blog=blog)
  96. >>> comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
  97. >>> annotated_comment = comments.get()
  98. ``annotated_comment.last_updated`` will be the most recent of ``blog.modified``
  99. and ``comment.modified``.
  100. .. warning::
  101. The behavior of ``Greatest`` when one or more expression may be ``null``
  102. varies between databases:
  103. - PostgreSQL: ``Greatest`` will return the largest non-null expression,
  104. or ``null`` if all expressions are ``null``.
  105. - SQLite, Oracle, and MySQL: If any expression is ``null``, ``Greatest``
  106. will return ``null``.
  107. The PostgreSQL behavior can be emulated using ``Coalesce`` if you know
  108. a sensible minimum value to provide as a default.
  109. ``Least``
  110. ---------
  111. .. class:: Least(*expressions, **extra)
  112. Accepts a list of at least two field names or expressions and returns the
  113. least value. Each argument must be of a similar type, so mixing text and numbers
  114. will result in a database error.
  115. .. warning::
  116. The behavior of ``Least`` when one or more expression may be ``null``
  117. varies between databases:
  118. - PostgreSQL: ``Least`` will return the smallest non-null expression,
  119. or ``null`` if all expressions are ``null``.
  120. - SQLite, Oracle, and MySQL: If any expression is ``null``, ``Least``
  121. will return ``null``.
  122. The PostgreSQL behavior can be emulated using ``Coalesce`` if you know
  123. a sensible maximum value to provide as a default.
  124. ``NullIf``
  125. ----------
  126. .. class:: NullIf(expression1, expression2)
  127. Accepts two expressions and returns ``None`` if they are equal, otherwise
  128. returns ``expression1``.
  129. .. admonition:: Caveats on Oracle
  130. Due to an :ref:`Oracle convention<oracle-null-empty-strings>`, this
  131. function returns the empty string instead of ``None`` when the expressions
  132. are of type :class:`~django.db.models.CharField`.
  133. Passing ``Value(None)`` to ``expression1`` is prohibited on Oracle since
  134. Oracle doesn't accept ``NULL`` as the first argument.
  135. .. _date-functions:
  136. Date functions
  137. ==============
  138. We'll be using the following model in examples of each function::
  139. class Experiment(models.Model):
  140. start_datetime = models.DateTimeField()
  141. start_date = models.DateField(null=True, blank=True)
  142. start_time = models.TimeField(null=True, blank=True)
  143. end_datetime = models.DateTimeField(null=True, blank=True)
  144. end_date = models.DateField(null=True, blank=True)
  145. end_time = models.TimeField(null=True, blank=True)
  146. ``Extract``
  147. -----------
  148. .. class:: Extract(expression, lookup_name=None, tzinfo=None, **extra)
  149. Extracts a component of a date as a number.
  150. Takes an ``expression`` representing a ``DateField``, ``DateTimeField``,
  151. ``TimeField``, or ``DurationField`` and a ``lookup_name``, and returns the part
  152. of the date referenced by ``lookup_name`` as an ``IntegerField``.
  153. Django usually uses the databases' extract function, so you may use any
  154. ``lookup_name`` that your database supports. A ``tzinfo`` subclass, usually
  155. provided by ``pytz``, can be passed to extract a value in a specific timezone.
  156. Given the datetime ``2015-06-15 23:30:01.000321+00:00``, the built-in
  157. ``lookup_name``\s return:
  158. * "year": 2015
  159. * "iso_year": 2015
  160. * "quarter": 2
  161. * "month": 6
  162. * "day": 15
  163. * "week": 25
  164. * "week_day": 2
  165. * "iso_week_day": 1
  166. * "hour": 23
  167. * "minute": 30
  168. * "second": 1
  169. If a different timezone like ``Australia/Melbourne`` is active in Django, then
  170. the datetime is converted to the timezone before the value is extracted. The
  171. timezone offset for Melbourne in the example date above is +10:00. The values
  172. returned when this timezone is active will be the same as above except for:
  173. * "day": 16
  174. * "week_day": 3
  175. * "iso_week_day": 2
  176. * "hour": 9
  177. .. admonition:: ``week_day`` values
  178. The ``week_day`` ``lookup_type`` is calculated differently from most
  179. databases and from Python's standard functions. This function will return
  180. ``1`` for Sunday, ``2`` for Monday, through ``7`` for Saturday.
  181. The equivalent calculation in Python is::
  182. >>> from datetime import datetime
  183. >>> dt = datetime(2015, 6, 15)
  184. >>> (dt.isoweekday() % 7) + 1
  185. 2
  186. .. admonition:: ``week`` values
  187. The ``week`` ``lookup_type`` is calculated based on `ISO-8601
  188. <https://en.wikipedia.org/wiki/ISO-8601>`_, i.e.,
  189. a week starts on a Monday. The first week of a year is the one that
  190. contains the year's first Thursday, i.e. the first week has the majority
  191. (four or more) of its days in the year. The value returned is in the range
  192. 1 to 52 or 53.
  193. Each ``lookup_name`` above has a corresponding ``Extract`` subclass (listed
  194. below) that should typically be used instead of the more verbose equivalent,
  195. e.g. use ``ExtractYear(...)`` rather than ``Extract(..., lookup_name='year')``.
  196. Usage example::
  197. >>> from datetime import datetime
  198. >>> from django.db.models.functions import Extract
  199. >>> start = datetime(2015, 6, 15)
  200. >>> end = datetime(2015, 7, 2)
  201. >>> Experiment.objects.create(
  202. ... start_datetime=start, start_date=start.date(),
  203. ... end_datetime=end, end_date=end.date())
  204. >>> # Add the experiment start year as a field in the QuerySet.
  205. >>> experiment = Experiment.objects.annotate(
  206. ... start_year=Extract('start_datetime', 'year')).get()
  207. >>> experiment.start_year
  208. 2015
  209. >>> # How many experiments completed in the same year in which they started?
  210. >>> Experiment.objects.filter(
  211. ... start_datetime__year=Extract('end_datetime', 'year')).count()
  212. 1
  213. ``DateField`` extracts
  214. ~~~~~~~~~~~~~~~~~~~~~~
  215. .. class:: ExtractYear(expression, tzinfo=None, **extra)
  216. .. attribute:: lookup_name = 'year'
  217. .. class:: ExtractIsoYear(expression, tzinfo=None, **extra)
  218. Returns the ISO-8601 week-numbering year.
  219. .. attribute:: lookup_name = 'iso_year'
  220. .. class:: ExtractMonth(expression, tzinfo=None, **extra)
  221. .. attribute:: lookup_name = 'month'
  222. .. class:: ExtractDay(expression, tzinfo=None, **extra)
  223. .. attribute:: lookup_name = 'day'
  224. .. class:: ExtractWeekDay(expression, tzinfo=None, **extra)
  225. .. attribute:: lookup_name = 'week_day'
  226. .. class:: ExtractIsoWeekDay(expression, tzinfo=None, **extra)
  227. .. versionadded:: 3.1
  228. Returns the ISO-8601 week day with day 1 being Monday and day 7 being
  229. Sunday.
  230. .. attribute:: lookup_name = 'iso_week_day'
  231. .. class:: ExtractWeek(expression, tzinfo=None, **extra)
  232. .. attribute:: lookup_name = 'week'
  233. .. class:: ExtractQuarter(expression, tzinfo=None, **extra)
  234. .. attribute:: lookup_name = 'quarter'
  235. These are logically equivalent to ``Extract('date_field', lookup_name)``. Each
  236. class is also a ``Transform`` registered on ``DateField`` and ``DateTimeField``
  237. as ``__(lookup_name)``, e.g. ``__year``.
  238. Since ``DateField``\s don't have a time component, only ``Extract`` subclasses
  239. that deal with date-parts can be used with ``DateField``::
  240. >>> from datetime import datetime
  241. >>> from django.utils import timezone
  242. >>> from django.db.models.functions import (
  243. ... ExtractDay, ExtractMonth, ExtractQuarter, ExtractWeek,
  244. ... ExtractIsoWeekDay, ExtractWeekDay, ExtractIsoYear, ExtractYear,
  245. ... )
  246. >>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
  247. >>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
  248. >>> Experiment.objects.create(
  249. ... start_datetime=start_2015, start_date=start_2015.date(),
  250. ... end_datetime=end_2015, end_date=end_2015.date())
  251. >>> Experiment.objects.annotate(
  252. ... year=ExtractYear('start_date'),
  253. ... isoyear=ExtractIsoYear('start_date'),
  254. ... quarter=ExtractQuarter('start_date'),
  255. ... month=ExtractMonth('start_date'),
  256. ... week=ExtractWeek('start_date'),
  257. ... day=ExtractDay('start_date'),
  258. ... weekday=ExtractWeekDay('start_date'),
  259. ... isoweekday=ExtractIsoWeekDay('start_date'),
  260. ... ).values(
  261. ... 'year', 'isoyear', 'quarter', 'month', 'week', 'day', 'weekday',
  262. ... 'isoweekday',
  263. ... ).get(end_date__year=ExtractYear('start_date'))
  264. {'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
  265. 'day': 15, 'weekday': 2, 'isoweekday': 1}
  266. ``DateTimeField`` extracts
  267. ~~~~~~~~~~~~~~~~~~~~~~~~~~
  268. In addition to the following, all extracts for ``DateField`` listed above may
  269. also be used on ``DateTimeField``\s .
  270. .. class:: ExtractHour(expression, tzinfo=None, **extra)
  271. .. attribute:: lookup_name = 'hour'
  272. .. class:: ExtractMinute(expression, tzinfo=None, **extra)
  273. .. attribute:: lookup_name = 'minute'
  274. .. class:: ExtractSecond(expression, tzinfo=None, **extra)
  275. .. attribute:: lookup_name = 'second'
  276. These are logically equivalent to ``Extract('datetime_field', lookup_name)``.
  277. Each class is also a ``Transform`` registered on ``DateTimeField`` as
  278. ``__(lookup_name)``, e.g. ``__minute``.
  279. ``DateTimeField`` examples::
  280. >>> from datetime import datetime
  281. >>> from django.utils import timezone
  282. >>> from django.db.models.functions import (
  283. ... ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,
  284. ... ExtractQuarter, ExtractSecond, ExtractWeek, ExtractIsoWeekDay,
  285. ... ExtractWeekDay, ExtractIsoYear, ExtractYear,
  286. ... )
  287. >>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
  288. >>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
  289. >>> Experiment.objects.create(
  290. ... start_datetime=start_2015, start_date=start_2015.date(),
  291. ... end_datetime=end_2015, end_date=end_2015.date())
  292. >>> Experiment.objects.annotate(
  293. ... year=ExtractYear('start_datetime'),
  294. ... isoyear=ExtractIsoYear('start_datetime'),
  295. ... quarter=ExtractQuarter('start_datetime'),
  296. ... month=ExtractMonth('start_datetime'),
  297. ... week=ExtractWeek('start_datetime'),
  298. ... day=ExtractDay('start_datetime'),
  299. ... weekday=ExtractWeekDay('start_datetime'),
  300. ... isoweekday=ExtractIsoWeekDay('start_datetime'),
  301. ... hour=ExtractHour('start_datetime'),
  302. ... minute=ExtractMinute('start_datetime'),
  303. ... second=ExtractSecond('start_datetime'),
  304. ... ).values(
  305. ... 'year', 'isoyear', 'month', 'week', 'day',
  306. ... 'weekday', 'isoweekday', 'hour', 'minute', 'second',
  307. ... ).get(end_datetime__year=ExtractYear('start_datetime'))
  308. {'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
  309. 'day': 15, 'weekday': 2, 'isoweekday': 1, 'hour': 23, 'minute': 30,
  310. 'second': 1}
  311. When :setting:`USE_TZ` is ``True`` then datetimes are stored in the database
  312. in UTC. If a different timezone is active in Django, the datetime is converted
  313. to that timezone before the value is extracted. The example below converts to
  314. the Melbourne timezone (UTC +10:00), which changes the day, weekday, and hour
  315. values that are returned::
  316. >>> import pytz
  317. >>> melb = pytz.timezone('Australia/Melbourne') # UTC+10:00
  318. >>> with timezone.override(melb):
  319. ... Experiment.objects.annotate(
  320. ... day=ExtractDay('start_datetime'),
  321. ... weekday=ExtractWeekDay('start_datetime'),
  322. ... isoweekday=ExtractIsoWeekDay('start_datetime'),
  323. ... hour=ExtractHour('start_datetime'),
  324. ... ).values('day', 'weekday', 'isoweekday', 'hour').get(
  325. ... end_datetime__year=ExtractYear('start_datetime'),
  326. ... )
  327. {'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}
  328. Explicitly passing the timezone to the ``Extract`` function behaves in the same
  329. way, and takes priority over an active timezone::
  330. >>> import pytz
  331. >>> melb = pytz.timezone('Australia/Melbourne')
  332. >>> Experiment.objects.annotate(
  333. ... day=ExtractDay('start_datetime', tzinfo=melb),
  334. ... weekday=ExtractWeekDay('start_datetime', tzinfo=melb),
  335. ... isoweekday=ExtractIsoWeekDay('start_datetime', tzinfo=melb),
  336. ... hour=ExtractHour('start_datetime', tzinfo=melb),
  337. ... ).values('day', 'weekday', 'isoweekday', 'hour').get(
  338. ... end_datetime__year=ExtractYear('start_datetime'),
  339. ... )
  340. {'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}
  341. ``Now``
  342. -------
  343. .. class:: Now()
  344. Returns the database server's current date and time when the query is executed,
  345. typically using the SQL ``CURRENT_TIMESTAMP``.
  346. Usage example::
  347. >>> from django.db.models.functions import Now
  348. >>> Article.objects.filter(published__lte=Now())
  349. <QuerySet [<Article: How to Django>]>
  350. .. admonition:: PostgreSQL considerations
  351. On PostgreSQL, the SQL ``CURRENT_TIMESTAMP`` returns the time that the
  352. current transaction started. Therefore for cross-database compatibility,
  353. ``Now()`` uses ``STATEMENT_TIMESTAMP`` instead. If you need the transaction
  354. timestamp, use :class:`django.contrib.postgres.functions.TransactionNow`.
  355. ``Trunc``
  356. ---------
  357. .. class:: Trunc(expression, kind, output_field=None, tzinfo=None, is_dst=None, **extra)
  358. Truncates a date up to a significant component.
  359. When you only care if something happened in a particular year, hour, or day,
  360. but not the exact second, then ``Trunc`` (and its subclasses) can be useful to
  361. filter or aggregate your data. For example, you can use ``Trunc`` to calculate
  362. the number of sales per day.
  363. ``Trunc`` takes a single ``expression``, representing a ``DateField``,
  364. ``TimeField``, or ``DateTimeField``, a ``kind`` representing a date or time
  365. part, and an ``output_field`` that's either ``DateTimeField()``,
  366. ``TimeField()``, or ``DateField()``. It returns a datetime, date, or time
  367. depending on ``output_field``, with fields up to ``kind`` set to their minimum
  368. value. If ``output_field`` is omitted, it will default to the ``output_field``
  369. of ``expression``. A ``tzinfo`` subclass, usually provided by ``pytz``, can be
  370. passed to truncate a value in a specific timezone.
  371. The ``is_dst`` parameter indicates whether or not ``pytz`` should interpret
  372. nonexistent and ambiguous datetimes in daylight saving time. By default (when
  373. ``is_dst=None``), ``pytz`` raises an exception for such datetimes.
  374. Given the datetime ``2015-06-15 14:30:50.000321+00:00``, the built-in ``kind``\s
  375. return:
  376. * "year": 2015-01-01 00:00:00+00:00
  377. * "quarter": 2015-04-01 00:00:00+00:00
  378. * "month": 2015-06-01 00:00:00+00:00
  379. * "week": 2015-06-15 00:00:00+00:00
  380. * "day": 2015-06-15 00:00:00+00:00
  381. * "hour": 2015-06-15 14:00:00+00:00
  382. * "minute": 2015-06-15 14:30:00+00:00
  383. * "second": 2015-06-15 14:30:50+00:00
  384. If a different timezone like ``Australia/Melbourne`` is active in Django, then
  385. the datetime is converted to the new timezone before the value is truncated.
  386. The timezone offset for Melbourne in the example date above is +10:00. The
  387. values returned when this timezone is active will be:
  388. * "year": 2015-01-01 00:00:00+11:00
  389. * "quarter": 2015-04-01 00:00:00+10:00
  390. * "month": 2015-06-01 00:00:00+10:00
  391. * "week": 2015-06-16 00:00:00+10:00
  392. * "day": 2015-06-16 00:00:00+10:00
  393. * "hour": 2015-06-16 00:00:00+10:00
  394. * "minute": 2015-06-16 00:30:00+10:00
  395. * "second": 2015-06-16 00:30:50+10:00
  396. The year has an offset of +11:00 because the result transitioned into daylight
  397. saving time.
  398. Each ``kind`` above has a corresponding ``Trunc`` subclass (listed below) that
  399. should typically be used instead of the more verbose equivalent,
  400. e.g. use ``TruncYear(...)`` rather than ``Trunc(..., kind='year')``.
  401. The subclasses are all defined as transforms, but they aren't registered with
  402. any fields, because the lookup names are already reserved by the ``Extract``
  403. subclasses.
  404. Usage example::
  405. >>> from datetime import datetime
  406. >>> from django.db.models import Count, DateTimeField
  407. >>> from django.db.models.functions import Trunc
  408. >>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321))
  409. >>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123))
  410. >>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999))
  411. >>> experiments_per_day = Experiment.objects.annotate(
  412. ... start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
  413. ... ).values('start_day').annotate(experiments=Count('id'))
  414. >>> for exp in experiments_per_day:
  415. ... print(exp['start_day'], exp['experiments'])
  416. ...
  417. 2015-06-15 00:00:00 2
  418. 2015-12-25 00:00:00 1
  419. >>> experiments = Experiment.objects.annotate(
  420. ... start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
  421. ... ).filter(start_day=datetime(2015, 6, 15))
  422. >>> for exp in experiments:
  423. ... print(exp.start_datetime)
  424. ...
  425. 2015-06-15 14:30:50.000321
  426. 2015-06-15 14:40:02.000123
  427. ``DateField`` truncation
  428. ~~~~~~~~~~~~~~~~~~~~~~~~
  429. .. class:: TruncYear(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  430. .. attribute:: kind = 'year'
  431. .. class:: TruncMonth(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  432. .. attribute:: kind = 'month'
  433. .. class:: TruncWeek(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  434. Truncates to midnight on the Monday of the week.
  435. .. attribute:: kind = 'week'
  436. .. class:: TruncQuarter(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  437. .. attribute:: kind = 'quarter'
  438. These are logically equivalent to ``Trunc('date_field', kind)``. They truncate
  439. all parts of the date up to ``kind`` which allows grouping or filtering dates
  440. with less precision. ``expression`` can have an ``output_field`` of either
  441. ``DateField`` or ``DateTimeField``.
  442. Since ``DateField``\s don't have a time component, only ``Trunc`` subclasses
  443. that deal with date-parts can be used with ``DateField``::
  444. >>> from datetime import datetime
  445. >>> from django.db.models import Count
  446. >>> from django.db.models.functions import TruncMonth, TruncYear
  447. >>> from django.utils import timezone
  448. >>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
  449. >>> start2 = datetime(2015, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc)
  450. >>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc)
  451. >>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
  452. >>> Experiment.objects.create(start_datetime=start2, start_date=start2.date())
  453. >>> Experiment.objects.create(start_datetime=start3, start_date=start3.date())
  454. >>> experiments_per_year = Experiment.objects.annotate(
  455. ... year=TruncYear('start_date')).values('year').annotate(
  456. ... experiments=Count('id'))
  457. >>> for exp in experiments_per_year:
  458. ... print(exp['year'], exp['experiments'])
  459. ...
  460. 2014-01-01 1
  461. 2015-01-01 2
  462. >>> import pytz
  463. >>> melb = pytz.timezone('Australia/Melbourne')
  464. >>> experiments_per_month = Experiment.objects.annotate(
  465. ... month=TruncMonth('start_datetime', tzinfo=melb)).values('month').annotate(
  466. ... experiments=Count('id'))
  467. >>> for exp in experiments_per_month:
  468. ... print(exp['month'], exp['experiments'])
  469. ...
  470. 2015-06-01 00:00:00+10:00 1
  471. 2016-01-01 00:00:00+11:00 1
  472. 2014-06-01 00:00:00+10:00 1
  473. ``DateTimeField`` truncation
  474. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  475. .. class:: TruncDate(expression, **extra)
  476. .. attribute:: lookup_name = 'date'
  477. .. attribute:: output_field = DateField()
  478. ``TruncDate`` casts ``expression`` to a date rather than using the built-in SQL
  479. truncate function. It's also registered as a transform on ``DateTimeField`` as
  480. ``__date``.
  481. .. class:: TruncTime(expression, **extra)
  482. .. attribute:: lookup_name = 'time'
  483. .. attribute:: output_field = TimeField()
  484. ``TruncTime`` casts ``expression`` to a time rather than using the built-in SQL
  485. truncate function. It's also registered as a transform on ``DateTimeField`` as
  486. ``__time``.
  487. .. class:: TruncDay(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  488. .. attribute:: kind = 'day'
  489. .. class:: TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  490. .. attribute:: kind = 'hour'
  491. .. class:: TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  492. .. attribute:: kind = 'minute'
  493. .. class:: TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  494. .. attribute:: kind = 'second'
  495. These are logically equivalent to ``Trunc('datetime_field', kind)``. They
  496. truncate all parts of the date up to ``kind`` and allow grouping or filtering
  497. datetimes with less precision. ``expression`` must have an ``output_field`` of
  498. ``DateTimeField``.
  499. Usage example::
  500. >>> from datetime import date, datetime
  501. >>> from django.db.models import Count
  502. >>> from django.db.models.functions import (
  503. ... TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,
  504. ... )
  505. >>> from django.utils import timezone
  506. >>> import pytz
  507. >>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
  508. >>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
  509. >>> melb = pytz.timezone('Australia/Melbourne')
  510. >>> Experiment.objects.annotate(
  511. ... date=TruncDate('start_datetime'),
  512. ... day=TruncDay('start_datetime', tzinfo=melb),
  513. ... hour=TruncHour('start_datetime', tzinfo=melb),
  514. ... minute=TruncMinute('start_datetime'),
  515. ... second=TruncSecond('start_datetime'),
  516. ... ).values('date', 'day', 'hour', 'minute', 'second').get()
  517. {'date': datetime.date(2014, 6, 15),
  518. 'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
  519. 'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
  520. 'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=<UTC>),
  521. 'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=<UTC>)
  522. }
  523. ``TimeField`` truncation
  524. ~~~~~~~~~~~~~~~~~~~~~~~~
  525. .. class:: TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  526. :noindex:
  527. .. attribute:: kind = 'hour'
  528. .. class:: TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  529. :noindex:
  530. .. attribute:: kind = 'minute'
  531. .. class:: TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)
  532. :noindex:
  533. .. attribute:: kind = 'second'
  534. These are logically equivalent to ``Trunc('time_field', kind)``. They truncate
  535. all parts of the time up to ``kind`` which allows grouping or filtering times
  536. with less precision. ``expression`` can have an ``output_field`` of either
  537. ``TimeField`` or ``DateTimeField``.
  538. Since ``TimeField``\s don't have a date component, only ``Trunc`` subclasses
  539. that deal with time-parts can be used with ``TimeField``::
  540. >>> from datetime import datetime
  541. >>> from django.db.models import Count, TimeField
  542. >>> from django.db.models.functions import TruncHour
  543. >>> from django.utils import timezone
  544. >>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
  545. >>> start2 = datetime(2014, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc)
  546. >>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc)
  547. >>> Experiment.objects.create(start_datetime=start1, start_time=start1.time())
  548. >>> Experiment.objects.create(start_datetime=start2, start_time=start2.time())
  549. >>> Experiment.objects.create(start_datetime=start3, start_time=start3.time())
  550. >>> experiments_per_hour = Experiment.objects.annotate(
  551. ... hour=TruncHour('start_datetime', output_field=TimeField()),
  552. ... ).values('hour').annotate(experiments=Count('id'))
  553. >>> for exp in experiments_per_hour:
  554. ... print(exp['hour'], exp['experiments'])
  555. ...
  556. 14:00:00 2
  557. 17:00:00 1
  558. >>> import pytz
  559. >>> melb = pytz.timezone('Australia/Melbourne')
  560. >>> experiments_per_hour = Experiment.objects.annotate(
  561. ... hour=TruncHour('start_datetime', tzinfo=melb),
  562. ... ).values('hour').annotate(experiments=Count('id'))
  563. >>> for exp in experiments_per_hour:
  564. ... print(exp['hour'], exp['experiments'])
  565. ...
  566. 2014-06-16 00:00:00+10:00 2
  567. 2016-01-01 04:00:00+11:00 1
  568. .. _math-functions:
  569. Math Functions
  570. ==============
  571. We'll be using the following model in math function examples::
  572. class Vector(models.Model):
  573. x = models.FloatField()
  574. y = models.FloatField()
  575. ``Abs``
  576. -------
  577. .. class:: Abs(expression, **extra)
  578. Returns the absolute value of a numeric field or expression.
  579. Usage example::
  580. >>> from django.db.models.functions import Abs
  581. >>> Vector.objects.create(x=-0.5, y=1.1)
  582. >>> vector = Vector.objects.annotate(x_abs=Abs('x'), y_abs=Abs('y')).get()
  583. >>> vector.x_abs, vector.y_abs
  584. (0.5, 1.1)
  585. It can also be registered as a transform. For example::
  586. >>> from django.db.models import FloatField
  587. >>> from django.db.models.functions import Abs
  588. >>> FloatField.register_lookup(Abs)
  589. >>> # Get vectors inside the unit cube
  590. >>> vectors = Vector.objects.filter(x__abs__lt=1, y__abs__lt=1)
  591. ``ACos``
  592. --------
  593. .. class:: ACos(expression, **extra)
  594. Returns the arccosine of a numeric field or expression. The expression value
  595. must be within the range -1 to 1.
  596. Usage example::
  597. >>> from django.db.models.functions import ACos
  598. >>> Vector.objects.create(x=0.5, y=-0.9)
  599. >>> vector = Vector.objects.annotate(x_acos=ACos('x'), y_acos=ACos('y')).get()
  600. >>> vector.x_acos, vector.y_acos
  601. (1.0471975511965979, 2.6905658417935308)
  602. It can also be registered as a transform. For example::
  603. >>> from django.db.models import FloatField
  604. >>> from django.db.models.functions import ACos
  605. >>> FloatField.register_lookup(ACos)
  606. >>> # Get vectors whose arccosine is less than 1
  607. >>> vectors = Vector.objects.filter(x__acos__lt=1, y__acos__lt=1)
  608. ``ASin``
  609. --------
  610. .. class:: ASin(expression, **extra)
  611. Returns the arcsine of a numeric field or expression. The expression value must
  612. be in the range -1 to 1.
  613. Usage example::
  614. >>> from django.db.models.functions import ASin
  615. >>> Vector.objects.create(x=0, y=1)
  616. >>> vector = Vector.objects.annotate(x_asin=ASin('x'), y_asin=ASin('y')).get()
  617. >>> vector.x_asin, vector.y_asin
  618. (0.0, 1.5707963267948966)
  619. It can also be registered as a transform. For example::
  620. >>> from django.db.models import FloatField
  621. >>> from django.db.models.functions import ASin
  622. >>> FloatField.register_lookup(ASin)
  623. >>> # Get vectors whose arcsine is less than 1
  624. >>> vectors = Vector.objects.filter(x__asin__lt=1, y__asin__lt=1)
  625. ``ATan``
  626. --------
  627. .. class:: ATan(expression, **extra)
  628. Returns the arctangent of a numeric field or expression.
  629. Usage example::
  630. >>> from django.db.models.functions import ATan
  631. >>> Vector.objects.create(x=3.12, y=6.987)
  632. >>> vector = Vector.objects.annotate(x_atan=ATan('x'), y_atan=ATan('y')).get()
  633. >>> vector.x_atan, vector.y_atan
  634. (1.2606282660069106, 1.428638798133829)
  635. It can also be registered as a transform. For example::
  636. >>> from django.db.models import FloatField
  637. >>> from django.db.models.functions import ATan
  638. >>> FloatField.register_lookup(ATan)
  639. >>> # Get vectors whose arctangent is less than 2
  640. >>> vectors = Vector.objects.filter(x__atan__lt=2, y__atan__lt=2)
  641. ``ATan2``
  642. ---------
  643. .. class:: ATan2(expression1, expression2, **extra)
  644. Returns the arctangent of ``expression1 / expression2``.
  645. Usage example::
  646. >>> from django.db.models.functions import ATan2
  647. >>> Vector.objects.create(x=2.5, y=1.9)
  648. >>> vector = Vector.objects.annotate(atan2=ATan2('x', 'y')).get()
  649. >>> vector.atan2
  650. 0.9209258773829491
  651. ``Ceil``
  652. --------
  653. .. class:: Ceil(expression, **extra)
  654. Returns the smallest integer greater than or equal to a numeric field or
  655. expression.
  656. Usage example::
  657. >>> from django.db.models.functions import Ceil
  658. >>> Vector.objects.create(x=3.12, y=7.0)
  659. >>> vector = Vector.objects.annotate(x_ceil=Ceil('x'), y_ceil=Ceil('y')).get()
  660. >>> vector.x_ceil, vector.y_ceil
  661. (4.0, 7.0)
  662. It can also be registered as a transform. For example::
  663. >>> from django.db.models import FloatField
  664. >>> from django.db.models.functions import Ceil
  665. >>> FloatField.register_lookup(Ceil)
  666. >>> # Get vectors whose ceil is less than 10
  667. >>> vectors = Vector.objects.filter(x__ceil__lt=10, y__ceil__lt=10)
  668. ``Cos``
  669. -------
  670. .. class:: Cos(expression, **extra)
  671. Returns the cosine of a numeric field or expression.
  672. Usage example::
  673. >>> from django.db.models.functions import Cos
  674. >>> Vector.objects.create(x=-8.0, y=3.1415926)
  675. >>> vector = Vector.objects.annotate(x_cos=Cos('x'), y_cos=Cos('y')).get()
  676. >>> vector.x_cos, vector.y_cos
  677. (-0.14550003380861354, -0.9999999999999986)
  678. It can also be registered as a transform. For example::
  679. >>> from django.db.models import FloatField
  680. >>> from django.db.models.functions import Cos
  681. >>> FloatField.register_lookup(Cos)
  682. >>> # Get vectors whose cosine is less than 0.5
  683. >>> vectors = Vector.objects.filter(x__cos__lt=0.5, y__cos__lt=0.5)
  684. ``Cot``
  685. -------
  686. .. class:: Cot(expression, **extra)
  687. Returns the cotangent of a numeric field or expression.
  688. Usage example::
  689. >>> from django.db.models.functions import Cot
  690. >>> Vector.objects.create(x=12.0, y=1.0)
  691. >>> vector = Vector.objects.annotate(x_cot=Cot('x'), y_cot=Cot('y')).get()
  692. >>> vector.x_cot, vector.y_cot
  693. (-1.5726734063976826, 0.642092615934331)
  694. It can also be registered as a transform. For example::
  695. >>> from django.db.models import FloatField
  696. >>> from django.db.models.functions import Cot
  697. >>> FloatField.register_lookup(Cot)
  698. >>> # Get vectors whose cotangent is less than 1
  699. >>> vectors = Vector.objects.filter(x__cot__lt=1, y__cot__lt=1)
  700. ``Degrees``
  701. -----------
  702. .. class:: Degrees(expression, **extra)
  703. Converts a numeric field or expression from radians to degrees.
  704. Usage example::
  705. >>> from django.db.models.functions import Degrees
  706. >>> Vector.objects.create(x=-1.57, y=3.14)
  707. >>> vector = Vector.objects.annotate(x_d=Degrees('x'), y_d=Degrees('y')).get()
  708. >>> vector.x_d, vector.y_d
  709. (-89.95437383553924, 179.9087476710785)
  710. It can also be registered as a transform. For example::
  711. >>> from django.db.models import FloatField
  712. >>> from django.db.models.functions import Degrees
  713. >>> FloatField.register_lookup(Degrees)
  714. >>> # Get vectors whose degrees are less than 360
  715. >>> vectors = Vector.objects.filter(x__degrees__lt=360, y__degrees__lt=360)
  716. ``Exp``
  717. -------
  718. .. class:: Exp(expression, **extra)
  719. Returns the value of ``e`` (the natural logarithm base) raised to the power of
  720. a numeric field or expression.
  721. Usage example::
  722. >>> from django.db.models.functions import Exp
  723. >>> Vector.objects.create(x=5.4, y=-2.0)
  724. >>> vector = Vector.objects.annotate(x_exp=Exp('x'), y_exp=Exp('y')).get()
  725. >>> vector.x_exp, vector.y_exp
  726. (221.40641620418717, 0.1353352832366127)
  727. It can also be registered as a transform. For example::
  728. >>> from django.db.models import FloatField
  729. >>> from django.db.models.functions import Exp
  730. >>> FloatField.register_lookup(Exp)
  731. >>> # Get vectors whose exp() is greater than 10
  732. >>> vectors = Vector.objects.filter(x__exp__gt=10, y__exp__gt=10)
  733. ``Floor``
  734. ---------
  735. .. class:: Floor(expression, **extra)
  736. Returns the largest integer value not greater than a numeric field or
  737. expression.
  738. Usage example::
  739. >>> from django.db.models.functions import Floor
  740. >>> Vector.objects.create(x=5.4, y=-2.3)
  741. >>> vector = Vector.objects.annotate(x_floor=Floor('x'), y_floor=Floor('y')).get()
  742. >>> vector.x_floor, vector.y_floor
  743. (5.0, -3.0)
  744. It can also be registered as a transform. For example::
  745. >>> from django.db.models import FloatField
  746. >>> from django.db.models.functions import Floor
  747. >>> FloatField.register_lookup(Floor)
  748. >>> # Get vectors whose floor() is greater than 10
  749. >>> vectors = Vector.objects.filter(x__floor__gt=10, y__floor__gt=10)
  750. ``Ln``
  751. ------
  752. .. class:: Ln(expression, **extra)
  753. Returns the natural logarithm a numeric field or expression.
  754. Usage example::
  755. >>> from django.db.models.functions import Ln
  756. >>> Vector.objects.create(x=5.4, y=233.0)
  757. >>> vector = Vector.objects.annotate(x_ln=Ln('x'), y_ln=Ln('y')).get()
  758. >>> vector.x_ln, vector.y_ln
  759. (1.6863989535702288, 5.4510384535657)
  760. It can also be registered as a transform. For example::
  761. >>> from django.db.models import FloatField
  762. >>> from django.db.models.functions import Ln
  763. >>> FloatField.register_lookup(Ln)
  764. >>> # Get vectors whose value greater than e
  765. >>> vectors = Vector.objects.filter(x__ln__gt=1, y__ln__gt=1)
  766. ``Log``
  767. -------
  768. .. class:: Log(expression1, expression2, **extra)
  769. Accepts two numeric fields or expressions and returns the logarithm of
  770. the first to base of the second.
  771. Usage example::
  772. >>> from django.db.models.functions import Log
  773. >>> Vector.objects.create(x=2.0, y=4.0)
  774. >>> vector = Vector.objects.annotate(log=Log('x', 'y')).get()
  775. >>> vector.log
  776. 2.0
  777. ``Mod``
  778. -------
  779. .. class:: Mod(expression1, expression2, **extra)
  780. Accepts two numeric fields or expressions and returns the remainder of
  781. the first divided by the second (modulo operation).
  782. Usage example::
  783. >>> from django.db.models.functions import Mod
  784. >>> Vector.objects.create(x=5.4, y=2.3)
  785. >>> vector = Vector.objects.annotate(mod=Mod('x', 'y')).get()
  786. >>> vector.mod
  787. 0.8
  788. ``Pi``
  789. ------
  790. .. class:: Pi(**extra)
  791. Returns the value of the mathematical constant ``π``.
  792. ``Power``
  793. ---------
  794. .. class:: Power(expression1, expression2, **extra)
  795. Accepts two numeric fields or expressions and returns the value of the first
  796. raised to the power of the second.
  797. Usage example::
  798. >>> from django.db.models.functions import Power
  799. >>> Vector.objects.create(x=2, y=-2)
  800. >>> vector = Vector.objects.annotate(power=Power('x', 'y')).get()
  801. >>> vector.power
  802. 0.25
  803. ``Radians``
  804. -----------
  805. .. class:: Radians(expression, **extra)
  806. Converts a numeric field or expression from degrees to radians.
  807. Usage example::
  808. >>> from django.db.models.functions import Radians
  809. >>> Vector.objects.create(x=-90, y=180)
  810. >>> vector = Vector.objects.annotate(x_r=Radians('x'), y_r=Radians('y')).get()
  811. >>> vector.x_r, vector.y_r
  812. (-1.5707963267948966, 3.141592653589793)
  813. It can also be registered as a transform. For example::
  814. >>> from django.db.models import FloatField
  815. >>> from django.db.models.functions import Radians
  816. >>> FloatField.register_lookup(Radians)
  817. >>> # Get vectors whose radians are less than 1
  818. >>> vectors = Vector.objects.filter(x__radians__lt=1, y__radians__lt=1)
  819. ``Round``
  820. ---------
  821. .. class:: Round(expression, **extra)
  822. Rounds a numeric field or expression to the nearest integer. Whether half
  823. values are rounded up or down depends on the database.
  824. Usage example::
  825. >>> from django.db.models.functions import Round
  826. >>> Vector.objects.create(x=5.4, y=-2.3)
  827. >>> vector = Vector.objects.annotate(x_r=Round('x'), y_r=Round('y')).get()
  828. >>> vector.x_r, vector.y_r
  829. (5.0, -2.0)
  830. It can also be registered as a transform. For example::
  831. >>> from django.db.models import FloatField
  832. >>> from django.db.models.functions import Round
  833. >>> FloatField.register_lookup(Round)
  834. >>> # Get vectors whose round() is less than 20
  835. >>> vectors = Vector.objects.filter(x__round__lt=20, y__round__lt=20)
  836. ``Sign``
  837. --------
  838. .. class:: Sign(expression, **extra)
  839. Returns the sign (-1, 0, 1) of a numeric field or expression.
  840. Usage example::
  841. >>> from django.db.models.functions import Sign
  842. >>> Vector.objects.create(x=5.4, y=-2.3)
  843. >>> vector = Vector.objects.annotate(x_sign=Sign('x'), y_sign=Sign('y')).get()
  844. >>> vector.x_sign, vector.y_sign
  845. (1, -1)
  846. It can also be registered as a transform. For example::
  847. >>> from django.db.models import FloatField
  848. >>> from django.db.models.functions import Sign
  849. >>> FloatField.register_lookup(Sign)
  850. >>> # Get vectors whose signs of components are less than 0.
  851. >>> vectors = Vector.objects.filter(x__sign__lt=0, y__sign__lt=0)
  852. ``Sin``
  853. -------
  854. .. class:: Sin(expression, **extra)
  855. Returns the sine of a numeric field or expression.
  856. Usage example::
  857. >>> from django.db.models.functions import Sin
  858. >>> Vector.objects.create(x=5.4, y=-2.3)
  859. >>> vector = Vector.objects.annotate(x_sin=Sin('x'), y_sin=Sin('y')).get()
  860. >>> vector.x_sin, vector.y_sin
  861. (-0.7727644875559871, -0.7457052121767203)
  862. It can also be registered as a transform. For example::
  863. >>> from django.db.models import FloatField
  864. >>> from django.db.models.functions import Sin
  865. >>> FloatField.register_lookup(Sin)
  866. >>> # Get vectors whose sin() is less than 0
  867. >>> vectors = Vector.objects.filter(x__sin__lt=0, y__sin__lt=0)
  868. ``Sqrt``
  869. --------
  870. .. class:: Sqrt(expression, **extra)
  871. Returns the square root of a nonnegative numeric field or expression.
  872. Usage example::
  873. >>> from django.db.models.functions import Sqrt
  874. >>> Vector.objects.create(x=4.0, y=12.0)
  875. >>> vector = Vector.objects.annotate(x_sqrt=Sqrt('x'), y_sqrt=Sqrt('y')).get()
  876. >>> vector.x_sqrt, vector.y_sqrt
  877. (2.0, 3.46410)
  878. It can also be registered as a transform. For example::
  879. >>> from django.db.models import FloatField
  880. >>> from django.db.models.functions import Sqrt
  881. >>> FloatField.register_lookup(Sqrt)
  882. >>> # Get vectors whose sqrt() is less than 5
  883. >>> vectors = Vector.objects.filter(x__sqrt__lt=5, y__sqrt__lt=5)
  884. ``Tan``
  885. -------
  886. .. class:: Tan(expression, **extra)
  887. Returns the tangent of a numeric field or expression.
  888. Usage example::
  889. >>> from django.db.models.functions import Tan
  890. >>> Vector.objects.create(x=0, y=12)
  891. >>> vector = Vector.objects.annotate(x_tan=Tan('x'), y_tan=Tan('y')).get()
  892. >>> vector.x_tan, vector.y_tan
  893. (0.0, -0.6358599286615808)
  894. It can also be registered as a transform. For example::
  895. >>> from django.db.models import FloatField
  896. >>> from django.db.models.functions import Tan
  897. >>> FloatField.register_lookup(Tan)
  898. >>> # Get vectors whose tangent is less than 0
  899. >>> vectors = Vector.objects.filter(x__tan__lt=0, y__tan__lt=0)
  900. .. _text-functions:
  901. Text functions
  902. ==============
  903. ``Chr``
  904. -------
  905. .. class:: Chr(expression, **extra)
  906. Accepts a numeric field or expression and returns the text representation of
  907. the expression as a single character. It works the same as Python's :func:`chr`
  908. function.
  909. Like :class:`Length`, it can be registered as a transform on ``IntegerField``.
  910. The default lookup name is ``chr``.
  911. Usage example::
  912. >>> from django.db.models.functions import Chr
  913. >>> Author.objects.create(name='Margaret Smith')
  914. >>> author = Author.objects.filter(name__startswith=Chr(ord('M'))).get()
  915. >>> print(author.name)
  916. Margaret Smith
  917. ``Concat``
  918. ----------
  919. .. class:: Concat(*expressions, **extra)
  920. Accepts a list of at least two text fields or expressions and returns the
  921. concatenated text. Each argument must be of a text or char type. If you want
  922. to concatenate a ``TextField()`` with a ``CharField()``, then be sure to tell
  923. Django that the ``output_field`` should be a ``TextField()``. Specifying an
  924. ``output_field`` is also required when concatenating a ``Value`` as in the
  925. example below.
  926. This function will never have a null result. On backends where a null argument
  927. results in the entire expression being null, Django will ensure that each null
  928. part is converted to an empty string first.
  929. Usage example::
  930. >>> # Get the display name as "name (goes_by)"
  931. >>> from django.db.models import CharField, Value as V
  932. >>> from django.db.models.functions import Concat
  933. >>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
  934. >>> author = Author.objects.annotate(
  935. ... screen_name=Concat(
  936. ... 'name', V(' ('), 'goes_by', V(')'),
  937. ... output_field=CharField()
  938. ... )
  939. ... ).get()
  940. >>> print(author.screen_name)
  941. Margaret Smith (Maggie)
  942. ``Left``
  943. --------
  944. .. class:: Left(expression, length, **extra)
  945. Returns the first ``length`` characters of the given text field or expression.
  946. Usage example::
  947. >>> from django.db.models.functions import Left
  948. >>> Author.objects.create(name='Margaret Smith')
  949. >>> author = Author.objects.annotate(first_initial=Left('name', 1)).get()
  950. >>> print(author.first_initial)
  951. M
  952. ``Length``
  953. ----------
  954. .. class:: Length(expression, **extra)
  955. Accepts a single text field or expression and returns the number of characters
  956. the value has. If the expression is null, then the length will also be null.
  957. Usage example::
  958. >>> # Get the length of the name and goes_by fields
  959. >>> from django.db.models.functions import Length
  960. >>> Author.objects.create(name='Margaret Smith')
  961. >>> author = Author.objects.annotate(
  962. ... name_length=Length('name'),
  963. ... goes_by_length=Length('goes_by')).get()
  964. >>> print(author.name_length, author.goes_by_length)
  965. (14, None)
  966. It can also be registered as a transform. For example::
  967. >>> from django.db.models import CharField
  968. >>> from django.db.models.functions import Length
  969. >>> CharField.register_lookup(Length)
  970. >>> # Get authors whose name is longer than 7 characters
  971. >>> authors = Author.objects.filter(name__length__gt=7)
  972. ``Lower``
  973. ---------
  974. .. class:: Lower(expression, **extra)
  975. Accepts a single text field or expression and returns the lowercase
  976. representation.
  977. It can also be registered as a transform as described in :class:`Length`.
  978. Usage example::
  979. >>> from django.db.models.functions import Lower
  980. >>> Author.objects.create(name='Margaret Smith')
  981. >>> author = Author.objects.annotate(name_lower=Lower('name')).get()
  982. >>> print(author.name_lower)
  983. margaret smith
  984. ``LPad``
  985. --------
  986. .. class:: LPad(expression, length, fill_text=Value(' '), **extra)
  987. Returns the value of the given text field or expression padded on the left side
  988. with ``fill_text`` so that the resulting value is ``length`` characters long.
  989. The default ``fill_text`` is a space.
  990. Usage example::
  991. >>> from django.db.models import Value
  992. >>> from django.db.models.functions import LPad
  993. >>> Author.objects.create(name='John', alias='j')
  994. >>> Author.objects.update(name=LPad('name', 8, Value('abc')))
  995. 1
  996. >>> print(Author.objects.get(alias='j').name)
  997. abcaJohn
  998. ``LTrim``
  999. ---------
  1000. .. class:: LTrim(expression, **extra)
  1001. Similar to :class:`~django.db.models.functions.Trim`, but removes only leading
  1002. spaces.
  1003. ``MD5``
  1004. -------
  1005. .. class:: MD5(expression, **extra)
  1006. Accepts a single text field or expression and returns the MD5 hash of the
  1007. string.
  1008. It can also be registered as a transform as described in :class:`Length`.
  1009. Usage example::
  1010. >>> from django.db.models.functions import MD5
  1011. >>> Author.objects.create(name='Margaret Smith')
  1012. >>> author = Author.objects.annotate(name_md5=MD5('name')).get()
  1013. >>> print(author.name_md5)
  1014. 749fb689816b2db85f5b169c2055b247
  1015. ``Ord``
  1016. -------
  1017. .. class:: Ord(expression, **extra)
  1018. Accepts a single text field or expression and returns the Unicode code point
  1019. value for the first character of that expression. It works similar to Python's
  1020. :func:`ord` function, but an exception isn't raised if the expression is more
  1021. than one character long.
  1022. It can also be registered as a transform as described in :class:`Length`.
  1023. The default lookup name is ``ord``.
  1024. Usage example::
  1025. >>> from django.db.models.functions import Ord
  1026. >>> Author.objects.create(name='Margaret Smith')
  1027. >>> author = Author.objects.annotate(name_code_point=Ord('name')).get()
  1028. >>> print(author.name_code_point)
  1029. 77
  1030. ``Repeat``
  1031. ----------
  1032. .. class:: Repeat(expression, number, **extra)
  1033. Returns the value of the given text field or expression repeated ``number``
  1034. times.
  1035. Usage example::
  1036. >>> from django.db.models.functions import Repeat
  1037. >>> Author.objects.create(name='John', alias='j')
  1038. >>> Author.objects.update(name=Repeat('name', 3))
  1039. 1
  1040. >>> print(Author.objects.get(alias='j').name)
  1041. JohnJohnJohn
  1042. ``Replace``
  1043. -----------
  1044. .. class:: Replace(expression, text, replacement=Value(''), **extra)
  1045. Replaces all occurrences of ``text`` with ``replacement`` in ``expression``.
  1046. The default replacement text is the empty string. The arguments to the function
  1047. are case-sensitive.
  1048. Usage example::
  1049. >>> from django.db.models import Value
  1050. >>> from django.db.models.functions import Replace
  1051. >>> Author.objects.create(name='Margaret Johnson')
  1052. >>> Author.objects.create(name='Margaret Smith')
  1053. >>> Author.objects.update(name=Replace('name', Value('Margaret'), Value('Margareth')))
  1054. 2
  1055. >>> Author.objects.values('name')
  1056. <QuerySet [{'name': 'Margareth Johnson'}, {'name': 'Margareth Smith'}]>
  1057. ``Reverse``
  1058. -----------
  1059. .. class:: Reverse(expression, **extra)
  1060. Accepts a single text field or expression and returns the characters of that
  1061. expression in reverse order.
  1062. It can also be registered as a transform as described in :class:`Length`. The
  1063. default lookup name is ``reverse``.
  1064. Usage example::
  1065. >>> from django.db.models.functions import Reverse
  1066. >>> Author.objects.create(name='Margaret Smith')
  1067. >>> author = Author.objects.annotate(backward=Reverse('name')).get()
  1068. >>> print(author.backward)
  1069. htimS teragraM
  1070. ``Right``
  1071. ---------
  1072. .. class:: Right(expression, length, **extra)
  1073. Returns the last ``length`` characters of the given text field or expression.
  1074. Usage example::
  1075. >>> from django.db.models.functions import Right
  1076. >>> Author.objects.create(name='Margaret Smith')
  1077. >>> author = Author.objects.annotate(last_letter=Right('name', 1)).get()
  1078. >>> print(author.last_letter)
  1079. h
  1080. ``RPad``
  1081. --------
  1082. .. class:: RPad(expression, length, fill_text=Value(' '), **extra)
  1083. Similar to :class:`~django.db.models.functions.LPad`, but pads on the right
  1084. side.
  1085. ``RTrim``
  1086. ---------
  1087. .. class:: RTrim(expression, **extra)
  1088. Similar to :class:`~django.db.models.functions.Trim`, but removes only trailing
  1089. spaces.
  1090. ``SHA1``, ``SHA224``, ``SHA256``, ``SHA384``, and ``SHA512``
  1091. ------------------------------------------------------------
  1092. .. class:: SHA1(expression, **extra)
  1093. .. class:: SHA224(expression, **extra)
  1094. .. class:: SHA256(expression, **extra)
  1095. .. class:: SHA384(expression, **extra)
  1096. .. class:: SHA512(expression, **extra)
  1097. Accepts a single text field or expression and returns the particular hash of
  1098. the string.
  1099. They can also be registered as transforms as described in :class:`Length`.
  1100. Usage example::
  1101. >>> from django.db.models.functions import SHA1
  1102. >>> Author.objects.create(name='Margaret Smith')
  1103. >>> author = Author.objects.annotate(name_sha1=SHA1('name')).get()
  1104. >>> print(author.name_sha1)
  1105. b87efd8a6c991c390be5a68e8a7945a7851c7e5c
  1106. .. admonition:: PostgreSQL
  1107. The `pgcrypto extension <https://www.postgresql.org/docs/current/
  1108. pgcrypto.html>`_ must be installed. You can use the
  1109. :class:`~django.contrib.postgres.operations.CryptoExtension` migration
  1110. operation to install it.
  1111. .. admonition:: Oracle
  1112. Oracle doesn't support the ``SHA224`` function.
  1113. ``StrIndex``
  1114. ------------
  1115. .. class:: StrIndex(string, substring, **extra)
  1116. Returns a positive integer corresponding to the 1-indexed position of the first
  1117. occurrence of ``substring`` inside ``string``, or 0 if ``substring`` is not
  1118. found.
  1119. Usage example::
  1120. >>> from django.db.models import Value as V
  1121. >>> from django.db.models.functions import StrIndex
  1122. >>> Author.objects.create(name='Margaret Smith')
  1123. >>> Author.objects.create(name='Smith, Margaret')
  1124. >>> Author.objects.create(name='Margaret Jackson')
  1125. >>> Author.objects.filter(name='Margaret Jackson').annotate(
  1126. ... smith_index=StrIndex('name', V('Smith'))
  1127. ... ).get().smith_index
  1128. 0
  1129. >>> authors = Author.objects.annotate(
  1130. ... smith_index=StrIndex('name', V('Smith'))
  1131. ... ).filter(smith_index__gt=0)
  1132. <QuerySet [<Author: Margaret Smith>, <Author: Smith, Margaret>]>
  1133. .. warning::
  1134. In MySQL, a database table's :ref:`collation<mysql-collation>` determines
  1135. whether string comparisons (such as the ``expression`` and ``substring`` of
  1136. this function) are case-sensitive. Comparisons are case-insensitive by
  1137. default.
  1138. ``Substr``
  1139. ----------
  1140. .. class:: Substr(expression, pos, length=None, **extra)
  1141. Returns a substring of length ``length`` from the field or expression starting
  1142. at position ``pos``. The position is 1-indexed, so the position must be greater
  1143. than 0. If ``length`` is ``None``, then the rest of the string will be returned.
  1144. Usage example::
  1145. >>> # Set the alias to the first 5 characters of the name as lowercase
  1146. >>> from django.db.models.functions import Lower, Substr
  1147. >>> Author.objects.create(name='Margaret Smith')
  1148. >>> Author.objects.update(alias=Lower(Substr('name', 1, 5)))
  1149. 1
  1150. >>> print(Author.objects.get(name='Margaret Smith').alias)
  1151. marga
  1152. ``Trim``
  1153. --------
  1154. .. class:: Trim(expression, **extra)
  1155. Returns the value of the given text field or expression with leading and
  1156. trailing spaces removed.
  1157. Usage example::
  1158. >>> from django.db.models.functions import Trim
  1159. >>> Author.objects.create(name=' John ', alias='j')
  1160. >>> Author.objects.update(name=Trim('name'))
  1161. 1
  1162. >>> print(Author.objects.get(alias='j').name)
  1163. John
  1164. ``Upper``
  1165. ---------
  1166. .. class:: Upper(expression, **extra)
  1167. Accepts a single text field or expression and returns the uppercase
  1168. representation.
  1169. It can also be registered as a transform as described in :class:`Length`.
  1170. Usage example::
  1171. >>> from django.db.models.functions import Upper
  1172. >>> Author.objects.create(name='Margaret Smith')
  1173. >>> author = Author.objects.annotate(name_upper=Upper('name')).get()
  1174. >>> print(author.name_upper)
  1175. MARGARET SMITH
  1176. .. _window-functions:
  1177. Window functions
  1178. ================
  1179. There are a number of functions to use in a
  1180. :class:`~django.db.models.expressions.Window` expression for computing the rank
  1181. of elements or the :class:`Ntile` of some rows.
  1182. ``CumeDist``
  1183. ------------
  1184. .. class:: CumeDist(*expressions, **extra)
  1185. Calculates the cumulative distribution of a value within a window or partition.
  1186. The cumulative distribution is defined as the number of rows preceding or
  1187. peered with the current row divided by the total number of rows in the frame.
  1188. ``DenseRank``
  1189. -------------
  1190. .. class:: DenseRank(*expressions, **extra)
  1191. Equivalent to :class:`Rank` but does not have gaps.
  1192. ``FirstValue``
  1193. --------------
  1194. .. class:: FirstValue(expression, **extra)
  1195. Returns the value evaluated at the row that's the first row of the window
  1196. frame, or ``None`` if no such value exists.
  1197. ``Lag``
  1198. -------
  1199. .. class:: Lag(expression, offset=1, default=None, **extra)
  1200. Calculates the value offset by ``offset``, and if no row exists there, returns
  1201. ``default``.
  1202. ``default`` must have the same type as the ``expression``, however, this is
  1203. only validated by the database and not in Python.
  1204. .. admonition:: MariaDB and ``default``
  1205. MariaDB `doesn't support <https://jira.mariadb.org/browse/MDEV-12981>`_
  1206. the ``default`` parameter.
  1207. ``LastValue``
  1208. -------------
  1209. .. class:: LastValue(expression, **extra)
  1210. Comparable to :class:`FirstValue`, it calculates the last value in a given
  1211. frame clause.
  1212. ``Lead``
  1213. --------
  1214. .. class:: Lead(expression, offset=1, default=None, **extra)
  1215. Calculates the leading value in a given :ref:`frame <window-frames>`. Both
  1216. ``offset`` and ``default`` are evaluated with respect to the current row.
  1217. ``default`` must have the same type as the ``expression``, however, this is
  1218. only validated by the database and not in Python.
  1219. .. admonition:: MariaDB and ``default``
  1220. MariaDB `doesn't support <https://jira.mariadb.org/browse/MDEV-12981>`_
  1221. the ``default`` parameter.
  1222. ``NthValue``
  1223. ------------
  1224. .. class:: NthValue(expression, nth=1, **extra)
  1225. Computes the row relative to the offset ``nth`` (must be a positive value)
  1226. within the window. Returns ``None`` if no row exists.
  1227. Some databases may handle a nonexistent nth-value differently. For example,
  1228. Oracle returns an empty string rather than ``None`` for character-based
  1229. expressions. Django doesn't do any conversions in these cases.
  1230. ``Ntile``
  1231. ---------
  1232. .. class:: Ntile(num_buckets=1, **extra)
  1233. Calculates a partition for each of the rows in the frame clause, distributing
  1234. numbers as evenly as possible between 1 and ``num_buckets``. If the rows don't
  1235. divide evenly into a number of buckets, one or more buckets will be represented
  1236. more frequently.
  1237. ``PercentRank``
  1238. ---------------
  1239. .. class:: PercentRank(*expressions, **extra)
  1240. Computes the percentile rank of the rows in the frame clause. This
  1241. computation is equivalent to evaluating::
  1242. (rank - 1) / (total rows - 1)
  1243. The following table explains the calculation for the percentile rank of a row:
  1244. ===== ===== ==== ============ ============
  1245. Row # Value Rank Calculation Percent Rank
  1246. ===== ===== ==== ============ ============
  1247. 1 15 1 (1-1)/(7-1) 0.0000
  1248. 2 20 2 (2-1)/(7-1) 0.1666
  1249. 3 20 2 (2-1)/(7-1) 0.1666
  1250. 4 20 2 (2-1)/(7-1) 0.1666
  1251. 5 30 5 (5-1)/(7-1) 0.6666
  1252. 6 30 5 (5-1)/(7-1) 0.6666
  1253. 7 40 7 (7-1)/(7-1) 1.0000
  1254. ===== ===== ==== ============ ============
  1255. ``Rank``
  1256. --------
  1257. .. class:: Rank(*expressions, **extra)
  1258. Comparable to ``RowNumber``, this function ranks rows in the window. The
  1259. computed rank contains gaps. Use :class:`DenseRank` to compute rank without
  1260. gaps.
  1261. ``RowNumber``
  1262. -------------
  1263. .. class:: RowNumber(*expressions, **extra)
  1264. Computes the row number according to the ordering of either the frame clause
  1265. or the ordering of the whole query if there is no partitioning of the
  1266. :ref:`window frame <window-frames>`.