Database Functions¶

The classes documented below provide a way for users to use functions provided by the underlying database as annotations, aggregations, or filters in Django. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.

We’ll be using the following model in examples of each function:

classAuthor(models.Model):name=models.CharField(max_length=50)age=models.PositiveIntegerField(null=True,blank=True)alias=models.CharField(max_length=50,null=True,blank=True)goes_by=models.CharField(max_length=50,null=True,blank=True)

We don’t usually recommend allowing null=True for CharField since this allows the field to have two “empty values”, but it’s important for the Coalesce example below.

Comparison and conversion functions¶

`Cast`¶

classCast(expression, output_field)[source]¶

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

Usage example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportCast>>> 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`¶

classCoalesce(*expressions, **extra)[source]¶

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>>> fromdjango.db.modelsimportSum>>> fromdjango.db.models.functionsimportCoalesce>>> 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

Warning

A Python value passed to Coalesce on MySQL may be converted to an incorrect type unless explicitly cast to the correct database type:

>>> fromdjango.db.modelsimportDateTimeField>>> fromdjango.db.models.functionsimportCast,Coalesce>>> fromdjango.utilsimporttimezone>>> now=timezone.now()>>> Coalesce("updated",Cast(now,DateTimeField()))

`Collate`¶

classCollate(expression, collation)[source]¶

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`¶

classGreatest(*expressions, **extra)[source]¶

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:

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

>>> fromdjango.db.models.functionsimportGreatest>>> 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.

Warning

The behavior of Greatest when one or more expression may be null varies between databases:

PostgreSQL: Greatest will return the largest non-null expression, or null if all expressions are null.
SQLite, Oracle, and MySQL: If any expression is null, Greatest will return null.

The PostgreSQL behavior can be emulated using Coalesce if you know a sensible minimum value to provide as a default.

`JSONObject`¶

classJSONObject(**fields)[source]¶

Takes a list of key-value pairs and returns a JSON object containing those pairs.

Usage example:

>>> fromdjango.db.modelsimportF>>> fromdjango.db.models.functionsimportJSONObject,Lower>>> Author.objects.create(name="Margaret Smith",alias="msmith",age=25)>>> author=Author.objects.annotate(... json_object=JSONObject(... name=Lower("name"),... alias="alias",... age=F("age")*2,... )... ).get()>>> author.json_object{'name': 'margaret smith', 'alias': 'msmith', 'age': 50}

`Least`¶

classLeast(*expressions, **extra)[source]¶

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.

Warning

The behavior of Least when one or more expression may be null varies between databases:

PostgreSQL: Least will return the smallest non-null expression, or null if all expressions are null.
SQLite, Oracle, and MySQL: If any expression is null, Least will return null.

The PostgreSQL behavior can be emulated using Coalesce if you know a sensible maximum value to provide as a default.

`NullIf`¶

classNullIf(expression1, expression2)[source]¶

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

Caveats on Oracle

Due to an Oracle convention, this function returns the empty string instead of None when the expressions are of type CharField.

Passing Value(None) to expression1 is prohibited on Oracle since Oracle doesn’t accept NULL as the first argument.

Date functions¶

We’ll be using the following model in examples of each function:

classExperiment(models.Model):start_datetime=models.DateTimeField()start_date=models.DateField(null=True,blank=True)start_time=models.TimeField(null=True,blank=True)end_datetime=models.DateTimeField(null=True,blank=True)end_date=models.DateField(null=True,blank=True)end_time=models.TimeField(null=True,blank=True)

`Extract`¶

classExtract(expression, lookup_name=None, tzinfo=None, **extra)[source]¶

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-1523: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

week_day values

The week_daylookup_type is calculated differently from most databases and from Python’s standard functions. This function will return 1 for Sunday, 2 for Monday, through 7 for Saturday.

The equivalent calculation in Python is:

>>> fromdatetimeimportdatetime>>> dt=datetime(2015,6,15)>>> (dt.isoweekday()%7)+12

week values

The weeklookup_type is calculated based on ISO-8601, i.e., a week starts on a Monday. The first week of a year is the one that contains the year’s first Thursday, i.e. the first week has the majority (four or more) of its days in the year. The value returned is in the range 1 to 52 or 53.

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:

>>> fromdatetimeimportdatetime>>> fromdjango.db.models.functionsimportExtract>>> 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_year2015>>> # How many experiments completed in the same year in which they started?>>> Experiment.objects.filter(start_datetime__year=Extract("end_datetime","year")).count()1

`DateField` extracts¶

classExtractYear(expression, tzinfo=None, **extra)[source]¶

lookup_name='year'

classExtractIsoYear(expression, tzinfo=None, **extra)[source]¶

Returns the ISO-8601 week-numbering year.

lookup_name='iso_year'

classExtractMonth(expression, tzinfo=None, **extra)[source]¶

lookup_name='month'

classExtractDay(expression, tzinfo=None, **extra)[source]¶

lookup_name='day'

classExtractWeekDay(expression, tzinfo=None, **extra)[source]¶

lookup_name='week_day'

classExtractIsoWeekDay(expression, tzinfo=None, **extra)[source]¶

Returns the ISO-8601 week day with day 1 being Monday and day 7 being Sunday.

lookup_name='iso_week_day'

classExtractWeek(expression, tzinfo=None, **extra)[source]¶

lookup_name='week'

classExtractQuarter(expression, tzinfo=None, **extra)[source]¶

lookup_name='quarter'

These are logically equivalent to Extract('date_field',lookup_name). Each class is also a Transform registered on DateField and DateTimeField as __(lookup_name), e.g. __year.

Since DateFields don’t have a time component, only Extract subclasses that deal with date-parts can be used with DateField:

>>> fromdatetimeimportdatetime,timezone>>> fromdjango.db.models.functionsimport(... ExtractDay,... ExtractMonth,... ExtractQuarter,... ExtractWeek,... ExtractIsoWeekDay,... ExtractWeekDay,... ExtractIsoYear,... ExtractYear,... )>>> start_2015=datetime(2015,6,15,23,30,1,tzinfo=timezone.utc)>>> end_2015=datetime(2015,6,16,13,11,27,tzinfo=timezone.utc)>>> Experiment.objects.create(... start_datetime=start_2015,... start_date=start_2015.date(),... end_datetime=end_2015,... end_date=end_2015.date(),... )>>> Experiment.objects.annotate(... year=ExtractYear("start_date"),... isoyear=ExtractIsoYear("start_date"),... quarter=ExtractQuarter("start_date"),... month=ExtractMonth("start_date"),... week=ExtractWeek("start_date"),... day=ExtractDay("start_date"),... weekday=ExtractWeekDay("start_date"),... isoweekday=ExtractIsoWeekDay("start_date"),... ).values(... "year",... "isoyear",... "quarter",... "month",... "week",... "day",... "weekday",... "isoweekday",... ).get(... end_date__year=ExtractYear("start_date")... ){'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25, 'day': 15, 'weekday': 2, 'isoweekday': 1}

`DateTimeField` extracts¶

In addition to the following, all extracts for DateField listed above may also be used on DateTimeFields .

classExtractHour(expression, tzinfo=None, **extra)[source]¶

lookup_name='hour'

classExtractMinute(expression, tzinfo=None, **extra)[source]¶

lookup_name='minute'

classExtractSecond(expression, tzinfo=None, **extra)[source]¶

lookup_name='second'

These are logically equivalent to Extract('datetime_field',lookup_name). Each class is also a Transform registered on DateTimeField as __(lookup_name), e.g. __minute.

DateTimeField examples:

>>> fromdatetimeimportdatetime,timezone>>> fromdjango.db.models.functionsimport(... ExtractDay,... ExtractHour,... ExtractMinute,... ExtractMonth,... ExtractQuarter,... ExtractSecond,... ExtractWeek,... ExtractIsoWeekDay,... ExtractWeekDay,... ExtractIsoYear,... ExtractYear,... )>>> start_2015=datetime(2015,6,15,23,30,1,tzinfo=timezone.utc)>>> end_2015=datetime(2015,6,16,13,11,27,tzinfo=timezone.utc)>>> Experiment.objects.create(... start_datetime=start_2015,... start_date=start_2015.date(),... end_datetime=end_2015,... end_date=end_2015.date(),... )>>> Experiment.objects.annotate(... year=ExtractYear("start_datetime"),... isoyear=ExtractIsoYear("start_datetime"),... quarter=ExtractQuarter("start_datetime"),... month=ExtractMonth("start_datetime"),... week=ExtractWeek("start_datetime"),... day=ExtractDay("start_datetime"),... weekday=ExtractWeekDay("start_datetime"),... isoweekday=ExtractIsoWeekDay("start_datetime"),... hour=ExtractHour("start_datetime"),... minute=ExtractMinute("start_datetime"),... second=ExtractSecond("start_datetime"),... ).values(... "year",... "isoyear",... "month",... "week",... "day",... "weekday",... "isoweekday",... "hour",... "minute",... "second",... ).get(... end_datetime__year=ExtractYear("start_datetime")... ){'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25, 'day': 15, 'weekday': 2, 'isoweekday': 1, 'hour': 23, 'minute': 30, 'second': 1}

When USE_TZ is True then datetimes are stored in the database in UTC. If a different timezone is active in Django, the datetime is converted to that timezone before the value is extracted. The example below converts to the Melbourne timezone (UTC +10:00), which changes the day, weekday, and hour values that are returned:

>>> fromdjango.utilsimporttimezone>>> importzoneinfo>>> melb=zoneinfo.ZoneInfo("Australia/Melbourne")# UTC+10:00>>> withtimezone.override(melb):... Experiment.objects.annotate(... day=ExtractDay("start_datetime"),... weekday=ExtractWeekDay("start_datetime"),... isoweekday=ExtractIsoWeekDay("start_datetime"),... hour=ExtractHour("start_datetime"),... ).values("day","weekday","isoweekday","hour").get(... end_datetime__year=ExtractYear("start_datetime"),... )...{'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}

Explicitly passing the timezone to the Extract function behaves in the same way, and takes priority over an active timezone:

>>> importzoneinfo>>> melb=zoneinfo.ZoneInfo("Australia/Melbourne")>>> Experiment.objects.annotate(... day=ExtractDay("start_datetime",tzinfo=melb),... weekday=ExtractWeekDay("start_datetime",tzinfo=melb),... isoweekday=ExtractIsoWeekDay("start_datetime",tzinfo=melb),... hour=ExtractHour("start_datetime",tzinfo=melb),... ).values("day","weekday","isoweekday","hour").get(... end_datetime__year=ExtractYear("start_datetime"),... ){'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}

`Now`¶

classNow[source]¶

Returns the database server’s current date and time when the query is executed, typically using the SQL CURRENT_TIMESTAMP.

Usage example:

>>> fromdjango.db.models.functionsimportNow>>> Article.objects.filter(published__lte=Now())<QuerySet [<Article: How to Django>]>

PostgreSQL considerations

On PostgreSQL, the SQL CURRENT_TIMESTAMP returns the time that the current transaction started. Therefore for cross-database compatibility, Now() uses STATEMENT_TIMESTAMP instead. If you need the transaction timestamp, use django.contrib.postgres.functions.TransactionNow.

Oracle

On Oracle, the SQL LOCALTIMESTAMP is used to avoid issues with casting CURRENT_TIMESTAMP to DateTimeField.

Changed in Django 4.2:

Support for microsecond precision on MySQL and millisecond precision on SQLite were added.

Changed in Django 5.0:

In older versions, the SQL CURRENT_TIMESTAMP was used on Oracle instead of LOCALTIMESTAMP.

`Trunc`¶

classTrunc(expression, kind, output_field=None, tzinfo=None, **extra)[source]¶

Truncates a date up to a significant component.

When you only care if something happened in a particular year, hour, or day, but not the exact second, then Trunc (and its subclasses) can be useful to filter or aggregate your data. For example, you can use Trunc to calculate the number of sales per day.

Trunc takes a single expression, representing a DateField, TimeField, or DateTimeField, a kind representing a date or time part, and an output_field that’s either DateTimeField(), TimeField(), or DateField(). It returns a datetime, date, or time depending on output_field, with fields up to kind set to their minimum value. If output_field is omitted, it will default to the output_field of expression. A tzinfo subclass, usually provided by zoneinfo, can be passed to truncate a value in a specific timezone.

Given the datetime 2015-06-1514:30:50.000321+00:00, the built-in kinds return:

“year”: 2015-01-01 00:00:00+00:00
“quarter”: 2015-04-01 00:00:00+00:00
“month”: 2015-06-01 00:00:00+00:00
“week”: 2015-06-15 00:00:00+00:00
“day”: 2015-06-15 00:00:00+00:00
“hour”: 2015-06-15 14:00:00+00:00
“minute”: 2015-06-15 14:30:00+00:00
“second”: 2015-06-15 14:30:50+00:00

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

“year”: 2015-01-01 00:00:00+11:00
“quarter”: 2015-04-01 00:00:00+10:00
“month”: 2015-06-01 00:00:00+10:00
“week”: 2015-06-16 00:00:00+10:00
“day”: 2015-06-16 00:00:00+10:00
“hour”: 2015-06-16 00:00:00+10:00
“minute”: 2015-06-16 00:30:00+10:00
“second”: 2015-06-16 00:30:50+10:00

The year has an offset of +11:00 because the result transitioned into daylight saving time.

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

The subclasses are all defined as transforms, but they aren’t registered with any fields, because the lookup names are already reserved by the Extract subclasses.

Usage example:

>>> fromdatetimeimportdatetime>>> fromdjango.db.modelsimportCount,DateTimeField>>> fromdjango.db.models.functionsimportTrunc>>> Experiment.objects.create(start_datetime=datetime(2015,6,15,14,30,50,321))>>> Experiment.objects.create(start_datetime=datetime(2015,6,15,14,40,2,123))>>> Experiment.objects.create(start_datetime=datetime(2015,12,25,10,5,27,999))>>> experiments_per_day=(... Experiment.objects.annotate(... start_day=Trunc("start_datetime","day",output_field=DateTimeField())... )... .values("start_day")... .annotate(experiments=Count("id"))... )>>> forexpinexperiments_per_day:... print(exp["start_day"],exp["experiments"])...2015-06-15 00:00:00 22015-12-25 00:00:00 1>>> experiments=Experiment.objects.annotate(... start_day=Trunc("start_datetime","day",output_field=DateTimeField())... ).filter(start_day=datetime(2015,6,15))>>> forexpinexperiments:... print(exp.start_datetime)...2015-06-15 14:30:50.0003212015-06-15 14:40:02.000123

`DateField` truncation¶

classTruncYear(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='year'

classTruncMonth(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='month'

classTruncWeek(expression, output_field=None, tzinfo=None, **extra)[source]¶

Truncates to midnight on the Monday of the week.

kind='week'

classTruncQuarter(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='quarter'

These are logically equivalent to Trunc('date_field',kind). They truncate all parts of the date up to kind which allows grouping or filtering dates with less precision. expression can have an output_field of either DateField or DateTimeField.

Since DateFields don’t have a time component, only Trunc subclasses that deal with date-parts can be used with DateField:

>>> fromdatetimeimportdatetime,timezone>>> fromdjango.db.modelsimportCount>>> fromdjango.db.models.functionsimportTruncMonth,TruncYear>>> start1=datetime(2014,6,15,14,30,50,321,tzinfo=timezone.utc)>>> start2=datetime(2015,6,15,14,40,2,123,tzinfo=timezone.utc)>>> start3=datetime(2015,12,31,17,5,27,999,tzinfo=timezone.utc)>>> Experiment.objects.create(start_datetime=start1,start_date=start1.date())>>> Experiment.objects.create(start_datetime=start2,start_date=start2.date())>>> Experiment.objects.create(start_datetime=start3,start_date=start3.date())>>> experiments_per_year=(... Experiment.objects.annotate(year=TruncYear("start_date"))... .values("year")... .annotate(experiments=Count("id"))... )>>> forexpinexperiments_per_year:... print(exp["year"],exp["experiments"])...2014-01-01 12015-01-01 2>>> importzoneinfo>>> melb=zoneinfo.ZoneInfo("Australia/Melbourne")>>> experiments_per_month=(... Experiment.objects.annotate(month=TruncMonth("start_datetime",tzinfo=melb))... .values("month")... .annotate(experiments=Count("id"))... )>>> forexpinexperiments_per_month:... print(exp["month"],exp["experiments"])...2015-06-01 00:00:00+10:00 12016-01-01 00:00:00+11:00 12014-06-01 00:00:00+10:00 1

`DateTimeField` truncation¶

classTruncDate(expression, tzinfo=None, **extra)[source]¶

lookup_name='date'

output_field=DateField()

TruncDate casts expression to a date rather than using the built-in SQL truncate function. It’s also registered as a transform on DateTimeField as __date.

classTruncTime(expression, tzinfo=None, **extra)[source]¶

lookup_name='time'

output_field=TimeField()

TruncTime casts expression to a time rather than using the built-in SQL truncate function. It’s also registered as a transform on DateTimeField as __time.

classTruncDay(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='day'

classTruncHour(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='hour'

classTruncMinute(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='minute'

classTruncSecond(expression, output_field=None, tzinfo=None, **extra)[source]¶

kind='second'

These are logically equivalent to Trunc('datetime_field',kind). They truncate all parts of the date up to kind and allow grouping or filtering datetimes with less precision. expression must have an output_field of DateTimeField.

Usage example:

>>> fromdatetimeimportdate,datetime,timezone>>> fromdjango.db.modelsimportCount>>> fromdjango.db.models.functionsimport(... TruncDate,... TruncDay,... TruncHour,... TruncMinute,... TruncSecond,... )>>> importzoneinfo>>> start1=datetime(2014,6,15,14,30,50,321,tzinfo=timezone.utc)>>> Experiment.objects.create(start_datetime=start1,start_date=start1.date())>>> melb=zoneinfo.ZoneInfo("Australia/Melbourne")>>> Experiment.objects.annotate(... date=TruncDate("start_datetime"),... day=TruncDay("start_datetime",tzinfo=melb),... hour=TruncHour("start_datetime",tzinfo=melb),... minute=TruncMinute("start_datetime"),... second=TruncSecond("start_datetime"),... ).values("date","day","hour","minute","second").get(){'date': datetime.date(2014, 6, 15), 'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=zoneinfo.ZoneInfo('Australia/Melbourne')), 'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=zoneinfo.ZoneInfo('Australia/Melbourne')), 'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=timezone.utc), 'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=timezone.utc)}

`TimeField` truncation¶

classTruncHour(expression, output_field=None, tzinfo=None, **extra)[source]

kind='hour'

classTruncMinute(expression, output_field=None, tzinfo=None, **extra)[source]

kind='minute'

classTruncSecond(expression, output_field=None, tzinfo=None, **extra)[source]

kind='second'

These are logically equivalent to Trunc('time_field',kind). They truncate all parts of the time up to kind which allows grouping or filtering times with less precision. expression can have an output_field of either TimeField or DateTimeField.

Since TimeFields don’t have a date component, only Trunc subclasses that deal with time-parts can be used with TimeField:

>>> fromdatetimeimportdatetime,timezone>>> fromdjango.db.modelsimportCount,TimeField>>> fromdjango.db.models.functionsimportTruncHour>>> start1=datetime(2014,6,15,14,30,50,321,tzinfo=timezone.utc)>>> start2=datetime(2014,6,15,14,40,2,123,tzinfo=timezone.utc)>>> start3=datetime(2015,12,31,17,5,27,999,tzinfo=timezone.utc)>>> Experiment.objects.create(start_datetime=start1,start_time=start1.time())>>> Experiment.objects.create(start_datetime=start2,start_time=start2.time())>>> Experiment.objects.create(start_datetime=start3,start_time=start3.time())>>> experiments_per_hour=(... Experiment.objects.annotate(... hour=TruncHour("start_datetime",output_field=TimeField()),... )... .values("hour")... .annotate(experiments=Count("id"))... )>>> forexpinexperiments_per_hour:... print(exp["hour"],exp["experiments"])...14:00:00 217:00:00 1>>> importzoneinfo>>> melb=zoneinfo.ZoneInfo("Australia/Melbourne")>>> experiments_per_hour=(... Experiment.objects.annotate(... hour=TruncHour("start_datetime",tzinfo=melb),... )... .values("hour")... .annotate(experiments=Count("id"))... )>>> forexpinexperiments_per_hour:... print(exp["hour"],exp["experiments"])...2014-06-16 00:00:00+10:00 22016-01-01 04:00:00+11:00 1

Math Functions¶

We’ll be using the following model in math function examples:

classVector(models.Model):x=models.FloatField()y=models.FloatField()

`Abs`¶

classAbs(expression, **extra)[source]¶

Returns the absolute value of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportAbs>>> Vector.objects.create(x=-0.5,y=1.1)>>> vector=Vector.objects.annotate(x_abs=Abs("x"),y_abs=Abs("y")).get()>>> vector.x_abs,vector.y_abs(0.5, 1.1)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportAbs>>> FloatField.register_lookup(Abs)>>> # Get vectors inside the unit cube>>> vectors=Vector.objects.filter(x__abs__lt=1,y__abs__lt=1)

`ACos`¶

classACos(expression, **extra)[source]¶

Returns the arccosine of a numeric field or expression. The expression value must be within the range -1 to 1.

Usage example:

>>> fromdjango.db.models.functionsimportACos>>> Vector.objects.create(x=0.5,y=-0.9)>>> vector=Vector.objects.annotate(x_acos=ACos("x"),y_acos=ACos("y")).get()>>> vector.x_acos,vector.y_acos(1.0471975511965979, 2.6905658417935308)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportACos>>> FloatField.register_lookup(ACos)>>> # Get vectors whose arccosine is less than 1>>> vectors=Vector.objects.filter(x__acos__lt=1,y__acos__lt=1)

`ASin`¶

classASin(expression, **extra)[source]¶

Returns the arcsine of a numeric field or expression. The expression value must be in the range -1 to 1.

Usage example:

>>> fromdjango.db.models.functionsimportASin>>> Vector.objects.create(x=0,y=1)>>> vector=Vector.objects.annotate(x_asin=ASin("x"),y_asin=ASin("y")).get()>>> vector.x_asin,vector.y_asin(0.0, 1.5707963267948966)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportASin>>> FloatField.register_lookup(ASin)>>> # Get vectors whose arcsine is less than 1>>> vectors=Vector.objects.filter(x__asin__lt=1,y__asin__lt=1)

`ATan`¶

classATan(expression, **extra)[source]¶

Returns the arctangent of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportATan>>> Vector.objects.create(x=3.12,y=6.987)>>> vector=Vector.objects.annotate(x_atan=ATan("x"),y_atan=ATan("y")).get()>>> vector.x_atan,vector.y_atan(1.2606282660069106, 1.428638798133829)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportATan>>> FloatField.register_lookup(ATan)>>> # Get vectors whose arctangent is less than 2>>> vectors=Vector.objects.filter(x__atan__lt=2,y__atan__lt=2)

`ATan2`¶

classATan2(expression1, expression2, **extra)[source]¶

Returns the arctangent of expression1/expression2.

Usage example:

>>> fromdjango.db.models.functionsimportATan2>>> Vector.objects.create(x=2.5,y=1.9)>>> vector=Vector.objects.annotate(atan2=ATan2("x","y")).get()>>> vector.atan20.9209258773829491

`Ceil`¶

classCeil(expression, **extra)[source]¶

Returns the smallest integer greater than or equal to a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportCeil>>> Vector.objects.create(x=3.12,y=7.0)>>> vector=Vector.objects.annotate(x_ceil=Ceil("x"),y_ceil=Ceil("y")).get()>>> vector.x_ceil,vector.y_ceil(4.0, 7.0)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportCeil>>> FloatField.register_lookup(Ceil)>>> # Get vectors whose ceil is less than 10>>> vectors=Vector.objects.filter(x__ceil__lt=10,y__ceil__lt=10)

`Cos`¶

classCos(expression, **extra)[source]¶

Returns the cosine of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportCos>>> Vector.objects.create(x=-8.0,y=3.1415926)>>> vector=Vector.objects.annotate(x_cos=Cos("x"),y_cos=Cos("y")).get()>>> vector.x_cos,vector.y_cos(-0.14550003380861354, -0.9999999999999986)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportCos>>> FloatField.register_lookup(Cos)>>> # Get vectors whose cosine is less than 0.5>>> vectors=Vector.objects.filter(x__cos__lt=0.5,y__cos__lt=0.5)

`Cot`¶

classCot(expression, **extra)[source]¶

Returns the cotangent of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportCot>>> Vector.objects.create(x=12.0,y=1.0)>>> vector=Vector.objects.annotate(x_cot=Cot("x"),y_cot=Cot("y")).get()>>> vector.x_cot,vector.y_cot(-1.5726734063976826, 0.642092615934331)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportCot>>> FloatField.register_lookup(Cot)>>> # Get vectors whose cotangent is less than 1>>> vectors=Vector.objects.filter(x__cot__lt=1,y__cot__lt=1)

`Degrees`¶

classDegrees(expression, **extra)[source]¶

Converts a numeric field or expression from radians to degrees.

Usage example:

>>> fromdjango.db.models.functionsimportDegrees>>> Vector.objects.create(x=-1.57,y=3.14)>>> vector=Vector.objects.annotate(x_d=Degrees("x"),y_d=Degrees("y")).get()>>> vector.x_d,vector.y_d(-89.95437383553924, 179.9087476710785)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportDegrees>>> FloatField.register_lookup(Degrees)>>> # Get vectors whose degrees are less than 360>>> vectors=Vector.objects.filter(x__degrees__lt=360,y__degrees__lt=360)

`Exp`¶

classExp(expression, **extra)[source]¶

Returns the value of e (the natural logarithm base) raised to the power of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportExp>>> Vector.objects.create(x=5.4,y=-2.0)>>> vector=Vector.objects.annotate(x_exp=Exp("x"),y_exp=Exp("y")).get()>>> vector.x_exp,vector.y_exp(221.40641620418717, 0.1353352832366127)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportExp>>> FloatField.register_lookup(Exp)>>> # Get vectors whose exp() is greater than 10>>> vectors=Vector.objects.filter(x__exp__gt=10,y__exp__gt=10)

`Floor`¶

classFloor(expression, **extra)[source]¶

Returns the largest integer value not greater than a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportFloor>>> Vector.objects.create(x=5.4,y=-2.3)>>> vector=Vector.objects.annotate(x_floor=Floor("x"),y_floor=Floor("y")).get()>>> vector.x_floor,vector.y_floor(5.0, -3.0)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportFloor>>> FloatField.register_lookup(Floor)>>> # Get vectors whose floor() is greater than 10>>> vectors=Vector.objects.filter(x__floor__gt=10,y__floor__gt=10)

`Ln`¶

classLn(expression, **extra)[source]¶

Returns the natural logarithm a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportLn>>> Vector.objects.create(x=5.4,y=233.0)>>> vector=Vector.objects.annotate(x_ln=Ln("x"),y_ln=Ln("y")).get()>>> vector.x_ln,vector.y_ln(1.6863989535702288, 5.4510384535657)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportLn>>> FloatField.register_lookup(Ln)>>> # Get vectors whose value greater than e>>> vectors=Vector.objects.filter(x__ln__gt=1,y__ln__gt=1)

`Log`¶

classLog(expression1, expression2, **extra)[source]¶

Accepts two numeric fields or expressions and returns the logarithm of the second to base of the first.

Usage example:

>>> fromdjango.db.models.functionsimportLog>>> Vector.objects.create(x=2.0,y=4.0)>>> vector=Vector.objects.annotate(log=Log("x","y")).get()>>> vector.log2.0

`Mod`¶

classMod(expression1, expression2, **extra)[source]¶

Accepts two numeric fields or expressions and returns the remainder of the first divided by the second (modulo operation).

Usage example:

>>> fromdjango.db.models.functionsimportMod>>> Vector.objects.create(x=5.4,y=2.3)>>> vector=Vector.objects.annotate(mod=Mod("x","y")).get()>>> vector.mod0.8

`Pi`¶

classPi(**extra)[source]¶

Returns the value of the mathematical constant π.

`Power`¶

classPower(expression1, expression2, **extra)[source]¶

Accepts two numeric fields or expressions and returns the value of the first raised to the power of the second.

Usage example:

>>> fromdjango.db.models.functionsimportPower>>> Vector.objects.create(x=2,y=-2)>>> vector=Vector.objects.annotate(power=Power("x","y")).get()>>> vector.power0.25

`Radians`¶

classRadians(expression, **extra)[source]¶

Converts a numeric field or expression from degrees to radians.

Usage example:

>>> fromdjango.db.models.functionsimportRadians>>> Vector.objects.create(x=-90,y=180)>>> vector=Vector.objects.annotate(x_r=Radians("x"),y_r=Radians("y")).get()>>> vector.x_r,vector.y_r(-1.5707963267948966, 3.141592653589793)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportRadians>>> FloatField.register_lookup(Radians)>>> # Get vectors whose radians are less than 1>>> vectors=Vector.objects.filter(x__radians__lt=1,y__radians__lt=1)

`Random`¶

classRandom(**extra)[source]¶

Returns a random value in the range 0.0≤x<1.0.

`Round`¶

classRound(expression, precision=0, **extra)[source]¶

Rounds a numeric field or expression to precision (must be an integer) decimal places. By default, it rounds to the nearest integer. Whether half values are rounded up or down depends on the database.

Usage example:

>>> fromdjango.db.models.functionsimportRound>>> Vector.objects.create(x=5.4,y=-2.37)>>> vector=Vector.objects.annotate(x_r=Round("x"),y_r=Round("y",precision=1)).get()>>> vector.x_r,vector.y_r(5.0, -2.4)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportRound>>> FloatField.register_lookup(Round)>>> # Get vectors whose round() is less than 20>>> vectors=Vector.objects.filter(x__round__lt=20,y__round__lt=20)

`Sign`¶

classSign(expression, **extra)[source]¶

Returns the sign (-1, 0, 1) of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportSign>>> Vector.objects.create(x=5.4,y=-2.3)>>> vector=Vector.objects.annotate(x_sign=Sign("x"),y_sign=Sign("y")).get()>>> vector.x_sign,vector.y_sign(1, -1)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportSign>>> FloatField.register_lookup(Sign)>>> # Get vectors whose signs of components are less than 0.>>> vectors=Vector.objects.filter(x__sign__lt=0,y__sign__lt=0)

`Sin`¶

classSin(expression, **extra)[source]¶

Returns the sine of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportSin>>> Vector.objects.create(x=5.4,y=-2.3)>>> vector=Vector.objects.annotate(x_sin=Sin("x"),y_sin=Sin("y")).get()>>> vector.x_sin,vector.y_sin(-0.7727644875559871, -0.7457052121767203)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportSin>>> FloatField.register_lookup(Sin)>>> # Get vectors whose sin() is less than 0>>> vectors=Vector.objects.filter(x__sin__lt=0,y__sin__lt=0)

`Sqrt`¶

classSqrt(expression, **extra)[source]¶

Returns the square root of a nonnegative numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportSqrt>>> Vector.objects.create(x=4.0,y=12.0)>>> vector=Vector.objects.annotate(x_sqrt=Sqrt("x"),y_sqrt=Sqrt("y")).get()>>> vector.x_sqrt,vector.y_sqrt(2.0, 3.46410)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportSqrt>>> FloatField.register_lookup(Sqrt)>>> # Get vectors whose sqrt() is less than 5>>> vectors=Vector.objects.filter(x__sqrt__lt=5,y__sqrt__lt=5)

`Tan`¶

classTan(expression, **extra)[source]¶

Returns the tangent of a numeric field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportTan>>> Vector.objects.create(x=0,y=12)>>> vector=Vector.objects.annotate(x_tan=Tan("x"),y_tan=Tan("y")).get()>>> vector.x_tan,vector.y_tan(0.0, -0.6358599286615808)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportFloatField>>> fromdjango.db.models.functionsimportTan>>> FloatField.register_lookup(Tan)>>> # Get vectors whose tangent is less than 0>>> vectors=Vector.objects.filter(x__tan__lt=0,y__tan__lt=0)

Text functions¶

`Chr`¶

classChr(expression, **extra)[source]¶

Accepts a numeric field or expression and returns the text representation of the expression as a single character. It works the same as Python’s chr() function.

Like Length, it can be registered as a transform on IntegerField. The default lookup name is chr.

Usage example:

>>> fromdjango.db.models.functionsimportChr>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.filter(name__startswith=Chr(ord("M"))).get()>>> print(author.name)Margaret Smith

`Concat`¶

classConcat(*expressions, **extra)[source]¶

Accepts a list of at least two text fields or expressions and returns the concatenated text. Each argument must be of a text or char type. If you want to concatenate a TextField() with a CharField(), then be sure to tell Django that the output_field should be a TextField(). Specifying an output_field is also required when concatenating a Value as in the example below.

This function will never have a null result. On backends where a null argument results in the entire expression being null, Django will ensure that each null part is converted to an empty string first.

Usage example:

>>> # Get the display name as "name (goes_by)">>> fromdjango.db.modelsimportCharField,ValueasV>>> fromdjango.db.models.functionsimportConcat>>> Author.objects.create(name="Margaret Smith",goes_by="Maggie")>>> author=Author.objects.annotate(... screen_name=Concat("name",V(" ("),"goes_by",V(")"),output_field=CharField())... ).get()>>> print(author.screen_name)Margaret Smith (Maggie)

`Left`¶

classLeft(expression, length, **extra)[source]¶

Returns the first length characters of the given text field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportLeft>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(first_initial=Left("name",1)).get()>>> print(author.first_initial)M

`Length`¶

classLength(expression, **extra)[source]¶

Accepts a single text field or expression and returns the number of characters the value has. If the expression is null, then the length will also be null.

Usage example:

>>> # Get the length of the name and goes_by fields>>> fromdjango.db.models.functionsimportLength>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(... name_length=Length("name"),goes_by_length=Length("goes_by")... ).get()>>> print(author.name_length,author.goes_by_length)(14, None)

It can also be registered as a transform. For example:

>>> fromdjango.db.modelsimportCharField>>> fromdjango.db.models.functionsimportLength>>> CharField.register_lookup(Length)>>> # Get authors whose name is longer than 7 characters>>> authors=Author.objects.filter(name__length__gt=7)

`Lower`¶

classLower(expression, **extra)[source]¶

Accepts a single text field or expression and returns the lowercase representation.

It can also be registered as a transform as described in Length.

Usage example:

>>> fromdjango.db.models.functionsimportLower>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(name_lower=Lower("name")).get()>>> print(author.name_lower)margaret smith

`LPad`¶

classLPad(expression, length, fill_text=Value(''), **extra)[source]¶

Returns the value of the given text field or expression padded on the left side with fill_text so that the resulting value is length characters long. The default fill_text is a space.

Usage example:

>>> fromdjango.db.modelsimportValue>>> fromdjango.db.models.functionsimportLPad>>> Author.objects.create(name="John",alias="j")>>> Author.objects.update(name=LPad("name",8,Value("abc")))1>>> print(Author.objects.get(alias="j").name)abcaJohn

`LTrim`¶

classLTrim(expression, **extra)[source]¶

Similar to Trim, but removes only leading spaces.

`MD5`¶

classMD5(expression, **extra)[source]¶

Accepts a single text field or expression and returns the MD5 hash of the string.

It can also be registered as a transform as described in Length.

Usage example:

>>> fromdjango.db.models.functionsimportMD5>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(name_md5=MD5("name")).get()>>> print(author.name_md5)749fb689816b2db85f5b169c2055b247

`Ord`¶

classOrd(expression, **extra)[source]¶

Accepts a single text field or expression and returns the Unicode code point value for the first character of that expression. It works similar to Python’s ord() function, but an exception isn’t raised if the expression is more than one character long.

It can also be registered as a transform as described in Length. The default lookup name is ord.

Usage example:

>>> fromdjango.db.models.functionsimportOrd>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(name_code_point=Ord("name")).get()>>> print(author.name_code_point)77

`Repeat`¶

classRepeat(expression, number, **extra)[source]¶

Returns the value of the given text field or expression repeated number times.

Usage example:

>>> fromdjango.db.models.functionsimportRepeat>>> Author.objects.create(name="John",alias="j")>>> Author.objects.update(name=Repeat("name",3))1>>> print(Author.objects.get(alias="j").name)JohnJohnJohn

`Replace`¶

classReplace(expression, text, replacement=Value(''), **extra)[source]¶

Replaces all occurrences of text with replacement in expression. The default replacement text is the empty string. The arguments to the function are case-sensitive.

Usage example:

>>> fromdjango.db.modelsimportValue>>> fromdjango.db.models.functionsimportReplace>>> Author.objects.create(name="Margaret Johnson")>>> Author.objects.create(name="Margaret Smith")>>> Author.objects.update(name=Replace("name",Value("Margaret"),Value("Margareth")))2>>> Author.objects.values("name")<QuerySet [{'name': 'Margareth Johnson'}, {'name': 'Margareth Smith'}]>

`Reverse`¶

classReverse(expression, **extra)[source]¶

Accepts a single text field or expression and returns the characters of that expression in reverse order.

It can also be registered as a transform as described in Length. The default lookup name is reverse.

Usage example:

>>> fromdjango.db.models.functionsimportReverse>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(backward=Reverse("name")).get()>>> print(author.backward)htimS teragraM

`Right`¶

classRight(expression, length, **extra)[source]¶

Returns the last length characters of the given text field or expression.

Usage example:

>>> fromdjango.db.models.functionsimportRight>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(last_letter=Right("name",1)).get()>>> print(author.last_letter)h

`RPad`¶

classRPad(expression, length, fill_text=Value(''), **extra)[source]¶

Similar to LPad, but pads on the right side.

`RTrim`¶

classRTrim(expression, **extra)[source]¶

Similar to Trim, but removes only trailing spaces.

`SHA1`, `SHA224`, `SHA256`, `SHA384`, and `SHA512`¶

classSHA1(expression, **extra)[source]¶

classSHA224(expression, **extra)[source]¶

classSHA256(expression, **extra)[source]¶

classSHA384(expression, **extra)[source]¶

classSHA512(expression, **extra)[source]¶

Accepts a single text field or expression and returns the particular hash of the string.

They can also be registered as transforms as described in Length.

Usage example:

>>> fromdjango.db.models.functionsimportSHA1>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(name_sha1=SHA1("name")).get()>>> print(author.name_sha1)b87efd8a6c991c390be5a68e8a7945a7851c7e5c

PostgreSQL

The pgcrypto extension must be installed. You can use the CryptoExtension migration operation to install it.

Oracle

Oracle doesn’t support the SHA224 function.

`StrIndex`¶

classStrIndex(string, substring, **extra)[source]¶

Returns a positive integer corresponding to the 1-indexed position of the first occurrence of substring inside string, or 0 if substring is not found.

Usage example:

>>> fromdjango.db.modelsimportValueasV>>> fromdjango.db.models.functionsimportStrIndex>>> Author.objects.create(name="Margaret Smith")>>> Author.objects.create(name="Smith, Margaret")>>> Author.objects.create(name="Margaret Jackson")>>> Author.objects.filter(name="Margaret Jackson").annotate(... smith_index=StrIndex("name",V("Smith"))... ).get().smith_index0>>> authors=Author.objects.annotate(smith_index=StrIndex("name",V("Smith"))).filter(... smith_index__gt=0... )<QuerySet [<Author: Margaret Smith>, <Author: Smith, Margaret>]>

Warning

In MySQL, a database table’s collation determines whether string comparisons (such as the expression and substring of this function) are case-sensitive. Comparisons are case-insensitive by default.

`Substr`¶

classSubstr(expression, pos, length=None, **extra)[source]¶

Returns a substring of length length from the field or expression starting at position pos. The position is 1-indexed, so the position must be greater than 0. If length is None, then the rest of the string will be returned.

Usage example:

>>> # Set the alias to the first 5 characters of the name as lowercase>>> fromdjango.db.models.functionsimportLower,Substr>>> Author.objects.create(name="Margaret Smith")>>> Author.objects.update(alias=Lower(Substr("name",1,5)))1>>> print(Author.objects.get(name="Margaret Smith").alias)marga

`Trim`¶

classTrim(expression, **extra)[source]¶

Returns the value of the given text field or expression with leading and trailing spaces removed.

Usage example:

>>> fromdjango.db.models.functionsimportTrim>>> Author.objects.create(name=" John ",alias="j")>>> Author.objects.update(name=Trim("name"))1>>> print(Author.objects.get(alias="j").name)John

`Upper`¶

classUpper(expression, **extra)[source]¶

Accepts a single text field or expression and returns the uppercase representation.

It can also be registered as a transform as described in Length.

Usage example:

>>> fromdjango.db.models.functionsimportUpper>>> Author.objects.create(name="Margaret Smith")>>> author=Author.objects.annotate(name_upper=Upper("name")).get()>>> print(author.name_upper)MARGARET SMITH

Window functions¶

There are a number of functions to use in a Window expression for computing the rank of elements or the Ntile of some rows.

`CumeDist`¶

classCumeDist(*expressions, **extra)[source]¶

Calculates the cumulative distribution of a value within a window or partition. The cumulative distribution is defined as the number of rows preceding or peered with the current row divided by the total number of rows in the frame.

`DenseRank`¶

classDenseRank(*expressions, **extra)[source]¶

Equivalent to Rank but does not have gaps.

`FirstValue`¶

classFirstValue(expression, **extra)[source]¶

Returns the value evaluated at the row that’s the first row of the window frame, or None if no such value exists.

`Lag`¶

classLag(expression, offset=1, default=None, **extra)[source]¶

Calculates the value offset by offset, and if no row exists there, returns default.

default must have the same type as the expression, however, this is only validated by the database and not in Python.

MariaDB and default

MariaDB doesn’t support the default parameter.

`LastValue`¶

classLastValue(expression, **extra)[source]¶

Comparable to FirstValue, it calculates the last value in a given frame clause.

`Lead`¶

classLead(expression, offset=1, default=None, **extra)[source]¶

Calculates the leading value in a given frame. Both offset and default are evaluated with respect to the current row.

default must have the same type as the expression, however, this is only validated by the database and not in Python.

MariaDB and default

MariaDB doesn’t support the default parameter.

`NthValue`¶

classNthValue(expression, nth=1, **extra)[source]¶

Computes the row relative to the offset nth (must be a positive value) within the window. Returns None if no row exists.

Some databases may handle a nonexistent nth-value differently. For example, Oracle returns an empty string rather than None for character-based expressions. Django doesn’t do any conversions in these cases.

`Ntile`¶

classNtile(num_buckets=1, **extra)[source]¶

Calculates a partition for each of the rows in the frame clause, distributing numbers as evenly as possible between 1 and num_buckets. If the rows don’t divide evenly into a number of buckets, one or more buckets will be represented more frequently.

`PercentRank`¶

classPercentRank(*expressions, **extra)[source]¶

Computes the relative rank of the rows in the frame clause. This computation is equivalent to evaluating:

(rank - 1) / (total rows - 1)

The following table explains the calculation for the relative rank of a row:

Row #	Value	Rank	Calculation	Relative Rank
1	15	1	(1-1)/(7-1)	0.0000
2	20	2	(2-1)/(7-1)	0.1666
3	20	2	(2-1)/(7-1)	0.1666
4	20	2	(2-1)/(7-1)	0.1666
5	30	5	(5-1)/(7-1)	0.6666
6	30	5	(5-1)/(7-1)	0.6666
7	40	7	(7-1)/(7-1)	1.0000

`Rank`¶

classRank(*expressions, **extra)[source]¶

Comparable to RowNumber, this function ranks rows in the window. The computed rank contains gaps. Use DenseRank to compute rank without gaps.

`RowNumber`¶

classRowNumber(*expressions, **extra)[source]¶

Computes the row number according to the ordering of either the frame clause or the ordering of the whole query if there is no partitioning of the window frame.

Database Functions¶

Comparison and conversion functions¶

Cast¶

Coalesce¶

Collate¶

Greatest¶

JSONObject¶

Least¶

NullIf¶

Date functions¶

Extract¶

DateField extracts¶

DateTimeField extracts¶

Now¶

Trunc¶

DateField truncation¶

DateTimeField truncation¶

TimeField truncation¶

Math Functions¶

Abs¶

ACos¶

ASin¶

ATan¶

ATan2¶

Ceil¶

Cos¶

Cot¶

Degrees¶

Exp¶

Floor¶

Ln¶

Log¶

Mod¶

Pi¶

Power¶

Radians¶

Random¶

Round¶

Sign¶

Sin¶

Sqrt¶

Tan¶

Text functions¶

Chr¶

Concat¶

Left¶

Length¶

Lower¶

LPad¶

LTrim¶

MD5¶

Ord¶

Repeat¶

Replace¶

Reverse¶

Right¶

RPad¶

RTrim¶

SHA1, SHA224, SHA256, SHA384, and SHA512¶

StrIndex¶

Substr¶

Trim¶

Upper¶

Window functions¶

CumeDist¶

DenseRank¶

FirstValue¶

Lag¶

LastValue¶

Lead¶

NthValue¶

Ntile¶

PercentRank¶

Rank¶

RowNumber¶

Additional Information

Support Django!

Contents

Getting help

Download:

`Cast`¶

`Coalesce`¶

`Collate`¶

`Greatest`¶

`JSONObject`¶

`Least`¶

`NullIf`¶

`Extract`¶

`DateField` extracts¶

`DateTimeField` extracts¶

`Now`¶

`Trunc`¶

`DateField` truncation¶

`DateTimeField` truncation¶

`TimeField` truncation¶

`Abs`¶

`ACos`¶

`ASin`¶

`ATan`¶

`ATan2`¶

`Ceil`¶

`Cos`¶

`Cot`¶

`Degrees`¶

`Exp`¶

`Floor`¶

`Ln`¶

`Log`¶

`Mod`¶

`Pi`¶

`Power`¶

`Radians`¶

`Random`¶

`Round`¶

`Sign`¶

`Sin`¶

`Sqrt`¶

`Tan`¶

`Chr`¶

`Concat`¶

`Left`¶

`Length`¶

`Lower`¶

`LPad`¶

`LTrim`¶

`MD5`¶

`Ord`¶

`Repeat`¶

`Replace`¶

`Reverse`¶

`Right`¶

`RPad`¶

`RTrim`¶

`SHA1`, `SHA224`, `SHA256`, `SHA384`, and `SHA512`¶

`StrIndex`¶

`Substr`¶

`Trim`¶

`Upper`¶

`CumeDist`¶

`DenseRank`¶

`FirstValue`¶

`Lag`¶

`LastValue`¶

`Lead`¶

`NthValue`¶

`Ntile`¶

`PercentRank`¶

`Rank`¶

`RowNumber`¶