Querying#

You filter, order, select and group a partial date field in the database with its year, month and day, and with ranges whose bounds are partial dates. A partial date is uncertain, so a filter can meet a date that might or might not be what you asked for. This page says which dates each filter includes, so you can check a query before you rely on it, and it gives recipes for the other rules, including a date axis and a drilldown. To offer these filters to the people using a site, see filtering with django-filter.

Set up the examples#

The examples use this model, with collected indexed.

from django.db import models

from partial_dates import PartialDateField


class Sample(models.Model):
    collected = PartialDateField(null=True, blank=True, db_index=True)

They run against these rows, one sample for each value, and one sample holding no date.

from partial_dates import PartialDate

for text in [
    "1990", "1990-05", "1990-05-31", "1990-06", "1990-06-01", "1990-06-14",
    "1990-07", "1990-12", "1991", "1991-03", "1991-03-15", "1991-04",
    "1991-06", "1991-06-02", None,
]:
    Sample.objects.create(collected=text)

Every result below is shown as the dates in it, in order, by this function. It names nulls_last, so the sample holding no date comes last on both PostgreSQL and SQLite.

>>> from django.db.models import F, Q
>>> def show(queryset):
...     ordered = queryset.order_by(F("collected").asc(nulls_last=True))
...     return [
...         None if sample.collected is None else str(sample.collected)
...         for sample in ordered
...     ]

Filter by year, month or day#

year, month and day read the recorded parts of a date. Each takes a whole number and finds the rows whose part equals it. A date whose part is unknown is never found.

>>> show(Sample.objects.filter(collected__year=1990))
['1990', '1990-05', '1990-05-31', '1990-06', '1990-06-01', '1990-06-14',
 '1990-07', '1990-12']
>>> show(Sample.objects.filter(collected__month=6))
['1990-06', '1990-06-01', '1990-06-14', '1991-06', '1991-06-02']
>>> show(Sample.objects.filter(collected__day=14))
['1990-06-14']

The field registers these as the transforms Year, Month and Day from partial_dates.lookups, so you never import them. Every date has a year, so year finds a date at every precision. 1990 has no month, so month=6 leaves it out. 1990-06 has no day, so day=14 leaves it out, and day=1 finds 1990-06-01 and not 1990-06.

The table shows what each filter does with a date at each precision.

Row

year=1990

month=6

day=14

month__isnull=True

day__isnull=True

1990

yes

no

no

yes

yes

1990-06

yes

yes

no

no

yes

1990-06-14

yes

yes

yes

no

no

1991

no

no

no

yes

yes

no date

no

no

no

yes

yes

Each part compares as a number, so Django’s comparisons work on it.

>>> show(Sample.objects.filter(collected__month__gte=6, collected__year=1990))
['1990-06', '1990-06-01', '1990-06-14', '1990-07', '1990-12']
>>> show(Sample.objects.filter(collected__month__in=[3, 4]))
['1991-03', '1991-03-15', '1991-04']
>>> show(Sample.objects.filter(collected__year__range=(1991, 1991), collected__day=2))
['1991-06-02']

A number that no date can hold finds nothing and raises nothing.

>>> Sample.objects.filter(collected__month=13).exists()
False
>>> Sample.objects.filter(collected__year=10000).exists()
False

Exclude a part#

A date whose part is unknown is in neither filter(month=6) nor exclude(month=6). Saying that a year-only date is not in June would claim something nobody recorded. day works the same way.

>>> show(Sample.objects.exclude(collected__month=6))
['1990-05', '1990-05-31', '1990-07', '1990-12', '1991-03', '1991-03-15',
 '1991-04', None]

The result holds no 1990 and no 1991. It holds the sample with no date, as exclude does on any Django field that allows NULL. To ask for the dates whose month is unknown, say so with month__isnull.

Find dates by precision#

month__isnull and day__isnull select by precision. month__isnull=True finds the dates recorded to the year, and the samples holding no date.

>>> show(Sample.objects.filter(collected__month__isnull=True))
['1990', '1991', None]
>>> show(
...     Sample.objects.filter(
...         collected__month__isnull=False, collected__day__isnull=True
...     )
... )
['1990-05', '1990-06', '1990-07', '1990-12', '1991-03', '1991-04', '1991-06']

Filter by a range#

gt, gte, lt, lte and range take a partial date as the bound, written as a PartialDate or as its ISO form, at any precision. Each row is compared by the order of its ISO form, which is the order sorted() gives the values: a year sorts before the months in it, and a month before the days in it.

>>> show(Sample.objects.filter(collected__gte="1990-06", collected__lt="1990-12"))
['1990-06', '1990-06-01', '1990-06-14', '1990-07']
>>> show(Sample.objects.filter(collected__gte=PartialDate(1991, 3), collected__lte="1991-04"))
['1991-03', '1991-03-15', '1991-04']

A bound names a position in that order and not a period. lte="1990-06" finds 1990 and 1990-06 and leaves out 1990-06-14, which sorts after it.

>>> show(Sample.objects.filter(collected__lte="1990-06"))
['1990', '1990-05', '1990-05-31', '1990-06']

range takes two bounds and finds the same rows as gte of the first with lte of the second. The bounds can differ in precision and in form. A range whose first bound sorts after its second finds nothing.

>>> show(Sample.objects.filter(collected__range=("1990-06", PartialDate(1990, 6, 14))))
['1990-06', '1990-06-01', '1990-06-14']
>>> show(Sample.objects.filter(collected__range=("1991", "1990")))
[]

in finds the rows equal to one of the values and nothing else, so 1990 does not find 1990-06.

>>> show(Sample.objects.filter(collected__in=["1990", PartialDate(1990, 6)]))
['1990', '1990-06']

A sample holding no date is never found by a bound, and exclude of a bound returns it.

>>> show(Sample.objects.exclude(collected__gte="1990-06"))
['1990', '1990-05', '1990-05-31', None]

A bound that is not a partial date is refused before any query runs, with the same error the value gives. That covers text such as 1990-6, a datetime.date and a number. A real date is refused because it carries a day that may never have been recorded.

>>> Sample.objects.filter(collected__gte="1990-6")
Traceback (most recent call last):
    ...
ValueError: Not a partial date in ISO form: '1990-6'
>>> import datetime
>>> Sample.objects.filter(collected__gte=datetime.date(1990, 6, 1))
Traceback (most recent call last):
    ...
ValueError: Not a partial date in ISO form: datetime.date(1990, 6, 1)

Check a bound at month precision#

The table shows what each comparison does with a row at each precision, for a bound of 1990-06. The rows are in sort order. 1990 is the uncertain match: its period is partly inside and partly outside what each comparison asks for, and it sorts before the bound. So gt and gte leave it out, and lt and lte include it.

Bound

Row

gt

gte

lt

lte

1990-06

1990

no

no

yes

yes

1990-06

1990-05

no

no

yes

yes

1990-06

1990-05-31

no

no

yes

yes

1990-06

1990-06

no

yes

no

yes

1990-06

1990-06-01

yes

yes

no

no

1990-06

1990-06-14

yes

yes

no

no

1990-06

1990-07

yes

yes

no

no

1990-06

1991

yes

yes

no

no

Check a bound at year or day precision#

The same rule holds at the other two precisions of bound.

Bound

Row

gte

lte

1990

1990

yes

yes

1990

1990-06

yes

no

1990

1990-06-14

yes

no

1990-06-14

1990

no

yes

1990-06-14

1990-06

no

yes

1990-06-14

1990-06-14

yes

yes

Take in the whole of a month or year#

Because a bound is a position, lte="1991-03" leaves out 1991-03-15. To take in the whole of the month or year that an upper bound names, bound it by the position that follows it.

  • For a month, use lt with the next month, or lte with its last day.

  • For a year, use lt with the next year, or lte with its last day. Or filter on the part: year__lte=2020 finds every date in 2020 or before, at any precision.

>>> show(Sample.objects.filter(collected__gte="1991-03", collected__lt="1991-04"))
['1991-03', '1991-03-15']
>>> show(Sample.objects.filter(collected__gte="1991-03", collected__lte="1991-03-31"))
['1991-03', '1991-03-15']
>>> show(Sample.objects.filter(collected__year__lte=1990))
['1990', '1990-05', '1990-05-31', '1990-06', '1990-06-01', '1990-06-14',
 '1990-07', '1990-12']

Add every uncertain date to a range#

A range from 1990-06-14 to 1991-03 leaves out 1990 and 1990-06, because both sort before the lower bound, though each might hold a day in the range. To add them, add the lower bound’s enclosing periods with in: 1990 for a bound of 1990-06, and 1990 and 1990-06 for a bound of 1990-06-14.

>>> low, high = "1990-06-14", "1991-03"
>>> show(Sample.objects.filter(collected__range=(low, high)))
['1990-06-14', '1990-07', '1990-12', '1991', '1991-03']
>>> show(
...     Sample.objects.filter(
...         Q(collected__range=(low, high)) | Q(collected__in=["1990", "1990-06"])
...     )
... )
['1990', '1990-06', '1990-06-14', '1990-07', '1990-12', '1991', '1991-03']

Leave every uncertain date out of a range#

The same range includes 1991, because it sorts before the upper bound, though its days might all fall after March. To leave it out, exclude the upper bound’s enclosing periods with in: 1991 for a bound of 1991-03, and 1991 and 1991-03 for a bound of 1991-03-15.

>>> show(
...     Sample.objects.filter(collected__range=(low, high)).exclude(
...         collected__in=["1991"]
...     )
... )
['1990-06-14', '1990-07', '1990-12', '1991-03']

Find June of any year#

Filter on the month. It finds every date recorded to the month or the day in a June, in any year.

>>> show(Sample.objects.filter(collected__month=6))
['1990-06', '1990-06-01', '1990-06-14', '1991-06', '1991-06-02']

Dates recorded to the year only are left out, because their month is unknown. 1990 might have been a June, and the filter does not claim it was. To list them next to the June dates, ask for them with month__isnull.

>>> show(Sample.objects.filter(Q(collected__month=6) | Q(collected__month__isnull=True)))
['1990', '1990-06', '1990-06-01', '1990-06-14', '1991', '1991-06', '1991-06-02', None]

Order by the date#

Order by the field and the dates come in the order sorted() gives them: a year, then the months in it, and each month before its days. Put a minus in front for the reverse.

>>> def listed(queryset):
...     return [
...         None if sample.collected is None else str(sample.collected)
...         for sample in queryset
...     ]
>>> in_1991 = Sample.objects.filter(collected__year=1991)
>>> listed(in_1991.order_by("collected"))
['1991', '1991-03', '1991-03-15', '1991-04', '1991-06', '1991-06-02']
>>> listed(in_1991.order_by("-collected"))
['1991-06-02', '1991-06', '1991-04', '1991-03-15', '1991-03', '1991']

Ordering by the field uses the index on the column, on both databases.

Say where a row holding no date goes#

A sample holding no date has NULL in the column, and PostgreSQL and SQLite sort NULL in opposite places. SQLite puts it first when ascending. PostgreSQL puts it last. A query that meets such a row gives different results on the two databases, unless it names nulls_first or nulls_last. Every example below that can meet one names it.

>>> listed(Sample.objects.order_by(F("collected").asc(nulls_first=True)))[:3]
[None, '1990', '1990-05']
>>> listed(Sample.objects.order_by(F("collected").asc(nulls_last=True)))[-3:]
['1991-06', '1991-06-02', None]
>>> listed(Sample.objects.order_by(F("collected").desc(nulls_last=True)))[-3:]
['1990-05', '1990', None]

Say where a date with an unknown part goes#

Ordering by year, month or day is ordering by a number. A date whose part is unknown has no number, so it is empty in the same way as a row holding no date, and it sorts where each database sorts NULL. Name nulls_first or nulls_last here too.

>>> listed(
...     Sample.objects.filter(collected__year=1991).order_by(
...         F("collected__month").asc(nulls_last=True), "collected"
...     )
... )
['1991-03', '1991-03-15', '1991-04', '1991-06', '1991-06-02', '1991']

Count by year, month and day#

Select a part with values_list and count each value with annotate. A date appears under the part it has and under None for a part it lacks, so a year-only date is counted in its year and in no month.

>>> from django.db.models import Count
>>> list(
...     Sample.objects.values_list("collected__year")
...     .annotate(total=Count("pk"))
...     .order_by(F("collected__year").asc(nulls_last=True))
... )
[(1990, 8), (1991, 6), (None, 1)]
>>> list(
...     Sample.objects.filter(collected__year=1991)
...     .values_list("collected__month")
...     .annotate(total=Count("pk"))
...     .order_by(F("collected__month").asc(nulls_last=True))
... )
[(3, 2), (4, 1), (6, 2), (None, 1)]
>>> list(
...     Sample.objects.filter(collected__year=1990, collected__month=6)
...     .values_list("collected__day")
...     .annotate(total=Count("pk"))
...     .order_by(F("collected__day").asc(nulls_last=True))
... )
[(1, 1), (14, 1), (None, 1)]

The last group holds 1990-06, which is in June of 1990 and has no day. Filter on day__isnull=False to count only the dates recorded to the day.

Put dates on a date axis#

A chart axis needs real dates. The field registers the transform Start from partial_dates.lookups as start, so you never import it. start gives each partial date as the datetime.date its period starts on: 1990 is 1 January 1990, 1990-06 is 1 June 1990, and 1990-06-14 is itself.

>>> [
...     (day.isoformat(), total)
...     for day, total in Sample.objects.filter(collected__year=1991)
...     .values_list("collected__start")
...     .annotate(total=Count("pk"))
...     .order_by("collected__start")
... ]
[('1991-01-01', 1), ('1991-03-01', 1), ('1991-03-15', 1), ('1991-04-01', 1),
 ('1991-06-01', 1), ('1991-06-02', 1)]

A year-only date sits on 1 January of its year. That is the first day of its period and not a claim that anything happened that day. To leave the year-only dates off a month axis, keep only the dates with a month, and to leave off the month-only dates too, keep only the dates with a day.

>>> [
...     (day.isoformat(), total)
...     for day, total in Sample.objects.filter(collected__month__isnull=False)
...     .values_list("collected__start")
...     .annotate(total=Count("pk"))
...     .order_by("collected__start")
... ][:3]
[('1990-05-01', 1), ('1990-05-31', 1), ('1990-06-01', 2)]
>>> [
...     (day.isoformat(), total)
...     for day, total in Sample.objects.filter(collected__day__isnull=False)
...     .values_list("collected__start")
...     .annotate(total=Count("pk"))
...     .order_by("collected__start")
... ]
[('1990-05-31', 1), ('1990-06-01', 1), ('1990-06-14', 1), ('1991-03-15', 1),
 ('1991-06-02', 1)]

1990-06 and 1990-06-01 share a start, so the first query counts them together. They stay different values: start is for placing a date on an axis, and the field still tells them apart.

start compares with real dates, so a window on the axis is an ordinary filter.

>>> import datetime
>>> show(
...     Sample.objects.filter(
...         collected__start__gte=datetime.date(1990, 6, 1),
...         collected__start__lt=datetime.date(1990, 7, 1),
...     )
... )
['1990-06', '1990-06-01', '1990-06-14']

Order by start with the field as the second key, so dates that share a start have a fixed order.

>>> listed(
...     Sample.objects.filter(collected__start=datetime.date(1990, 6, 1)).order_by(
...         "collected__start", "collected"
...     )
... )
['1990-06', '1990-06-01']

Drill down from a year to a day#

A drilldown counts by year, then by the months of the year the reader picks, then by the days of the month they pick. Each step is the count above with the earlier choices as filters.

  1. Count by year with values_list("collected__year").

  2. Filter on the chosen year and count by collected__month.

  3. Filter on the chosen year and month and count by collected__day.

Each step has a None group for the dates that stop at the level above. Show it as “month unknown” or “day unknown”, or drop it with month__isnull=False or day__isnull=False.

Check what each precision gives#

The table shows what each part gives for a date at each precision.

Date

year

month

day

start

1990

1990

none

none

1990-01-01

1990-06

1990

6

none

1990-06-01

1990-06-14

1990

6

14

1990-06-14

no date

none

none

none

none

Check which queries use an index#

When the column has an index, a range filter such as gte with lte, a filter on one year with year=1990, and ordering by the field itself use it on PostgreSQL and SQLite. Filters on month and day, and comparisons of year other than equality, read the whole table. “June of any year” is the usual example. Whether a column has an index stays your decision.

A filter on one year uses the index because YearExact, the exact lookup of Year, compares the stored text with the year’s first and last possible text, 1990 and 1990-99, instead of reading the year out of every row. Comparing the year with another field, as in year=F("other__year"), takes Django’s own comparison.

Leave out Django’s text lookups#

Django gives every text-based field lookups such as contains, startswith and regex. They stay as Django gives them and read the stored text. This page does not cover them. Use the filters above to ask about a year, a month, a day or a range.

Why a bound is a position in the sort order#

A filter compares text, and partial dates are uncertain, so some matches are uncertain too. This package gives every comparison one rule, the order of the ISO form, and states the result at each bound. The order is the one PartialDate sorts in, so every filter and every sort agree. A date whose period contains the bound sorts before it, so gt and gte leave it out and lt and lte include it. ADR 0001 records the decision.

Why a year sits on 1 January on a date axis#

A chart axis holds real dates and a partial date is not one. start has to put a year somewhere, and the first day of its period is the one place that is always inside it and never later than anything the date could mean. It is a position on the axis, not a record of when the thing happened. end is not offered, because a period’s last day is no more a claim than its first, and a date axis needs one end of the period.

Why empty values sort where the database puts them#

The package stores NULL for a row holding no date, and year, month, day and start give NULL for a part that is not recorded. Which end of a sort NULL takes is the database’s decision: SQLite first, PostgreSQL last when ascending. The package does not override it, so that ordering by a partial date behaves like ordering by any other nullable column in your project. The cost is that you name nulls_first or nulls_last wherever the result must be the same on both.