Database Functions¶

Cast¶

class Cast(expression, output_field)[código-fonte]¶

Forces the result type of expression to be the one from output_field.

Usage example:

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Author.objects.create(age=25, name="Margaret Smith")
>>> author = Author.objects.annotate(
...     age_as_float=Cast("age", output_field=FloatField()),
... ).get()
>>> print(author.age_as_float)
25.0

Coalesce¶

class Coalesce(*expressions, **extra)[código-fonte]¶

Accepts a list of at least two field names or expressions and returns the first non-null value (note that an empty string is not considered a null value). Each argument must be of a similar type, so mixing text and numbers will result in a database error.

Usage examples:

>>> # Get a screen name from least to most public
>>> from django.db.models import Sum
>>> from django.db.models.functions import Coalesce
>>> Author.objects.create(name="Margaret Smith", goes_by="Maggie")
>>> author = Author.objects.annotate(screen_name=Coalesce("alias", "goes_by", "name")).get()
>>> print(author.screen_name)
Maggie

>>> # Prevent an aggregate Sum() from returning None
>>> # The aggregate default argument uses Coalesce() under the hood.
>>> aggregated = Author.objects.aggregate(
...     combined_age=Sum("age"),
...     combined_age_default=Sum("age", default=0),
...     combined_age_coalesce=Coalesce(Sum("age"), 0),
... )
>>> print(aggregated["combined_age"])
None
>>> print(aggregated["combined_age_default"])
0
>>> print(aggregated["combined_age_coalesce"])
0

>>> from django.db.models import DateTimeField
>>> from django.db.models.functions import Cast, Coalesce
>>> from django.utils import timezone
>>> now = timezone.now()
>>> Coalesce("updated", Cast(now, DateTimeField()))

Collate¶

class Collate(expression, collation)[código-fonte]¶

Takes an expression and a collation name to query against.

For example, to filter case-insensitively in SQLite:

>>> Author.objects.filter(name=Collate(Value("john"), "nocase"))
<QuerySet [<Author: John>, <Author: john>]>

It can also be used when ordering, for example with PostgreSQL:

>>> Author.objects.order_by(Collate("name", "et-x-icu"))
<QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]>

Greatest¶

class Greatest(*expressions, **extra)[código-fonte]¶

Accepts a list of at least two field names or expressions and returns the greatest value. Each argument must be of a similar type, so mixing text and numbers will result in a database error.

Usage example:

class Blog(models.Model):
    body = models.TextField()
    modified = models.DateTimeField(auto_now=True)


class Comment(models.Model):
    body = models.TextField()
    modified = models.DateTimeField(auto_now=True)
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)

>>> from django.db.models.functions import Greatest
>>> blog = Blog.objects.create(body="Greatest is the best.")
>>> comment = Comment.objects.create(body="No, Least is better.", blog=blog)
>>> comments = Comment.objects.annotate(last_updated=Greatest("modified", "blog__modified"))
>>> annotated_comment = comments.get()

annotated_comment.last_updated will be the most recent of blog.modified and comment.modified.

Least¶

class Least(*expressions, **extra)[código-fonte]¶

Accepts a list of at least two field names or expressions and returns the least value. Each argument must be of a similar type, so mixing text and numbers will result in a database error.

NullIf¶

class NullIf(expression1, expression2)[código-fonte]¶

Accepts two expressions and returns None if they are equal, otherwise returns expression1.

Extract¶

class Extract(expression, lookup_name=None, tzinfo=None, **extra)[código-fonte]¶

Extracts a component of a date as a number.

Takes an expression representing a DateField, DateTimeField, TimeField, or DurationField and a lookup_name, and returns the part of the date referenced by lookup_name as an IntegerField. Django usually uses the databases’ extract function, so you may use any lookup_name that your database supports. A tzinfo subclass, usually provided by zoneinfo, can be passed to extract a value in a specific timezone.

Given the datetime 2015-06-15 23:30:01.000321+00:00, the built-in lookup_names return:

• “year”: 2015

• “iso_year”: 2015

• “quarter”: 2

• “month”: 6

• “day”: 15

• “week”: 25

• “week_day”: 2

• “iso_week_day”: 1

• “hour”: 23

• “minute”: 30

• “second”: 1

If a different timezone like Australia/Melbourne is active in Django, then the datetime is converted to the timezone before the value is extracted. The timezone offset for Melbourne in the example date above is +10:00. The values returned when this timezone is active will be the same as above except for:

• “day”: 16

• “week_day”: 3

• “iso_week_day”: 2

• “hour”: 9

>>> from datetime import datetime
>>> dt = datetime(2015, 6, 15)
>>> (dt.isoweekday() % 7) + 1
2

Each lookup_name above has a corresponding Extract subclass (listed below) that should typically be used instead of the more verbose equivalent, e.g. use ExtractYear(...) rather than Extract(..., lookup_name='year').

Usage example:

>>> from datetime import datetime
>>> from django.db.models.functions import Extract
>>> start = datetime(2015, 6, 15)
>>> end = datetime(2015, 7, 2)
>>> Experiment.objects.create(
...     start_datetime=start, start_date=start.date(), end_datetime=end, end_date=end.date()
... )
>>> # Add the experiment start year as a field in the QuerySet.
>>> experiment = Experiment.objects.annotate(
...     start_year=Extract("start_datetime", "year")
... ).get()
>>> experiment.start_year
2015
>>> # How many experiments completed in the same year in which they started?
>>> Experiment.objects.filter(start_datetime__year=Extract("end_datetime", "year")).count()
1