Opened 9 years ago

Closed 6 years ago

#5365 closed (worksforme)

sqlite3+DateField: field__year doesn't work.

Reported by: Jakub Wilk <django@…> Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: qs-rf
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: UI/UX:


With DATABASE_ENGINE = 'sqlite3' and the following foo/

from django.db import models

class Foo(models.Model):
        date = models.DateField()

I try:

>>> from datetime import date
>>> from foo.models import Foo
Foo(None, date(2007, 1, 1)).save()
>>> Foo.objects.all()
[<Foo: Foo object>]
>>> Foo.objects.filter(date__range = (date(2007, 1, 1), date(2007, 12, 31)))
[<Foo: Foo object>]
>>> Foo.objects.filter(date__year = 2007)

Change History (6)

comment:1 Changed 9 years ago by Matt McClanahan

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

The query does work, however it appears that SQLite is performing an exclusive BETWEEN rather than an inclusive one. This is inconsistent with other DB systems, and is possibly a SQLite bug.

comment:2 in reply to:  1 Changed 9 years ago by Jakub Wilk <django@…>

Replying to mattmcc:

The query does work, however it appears that SQLite is performing an exclusive BETWEEN rather than an inclusive one.

Nope, this is not the case. date__range also uses a BETWEEN expression but it works fine. The problem is that SQLite doesn't have a real date/datetime type.

sqlite> SELECT date('2007-01-01');
sqlite> SELECT datetime('2007-01-01');
2007-01-01 00:00:00
sqlite> SELECT 1 WHERE date('2007-01-01') > date('2007-01-01');
sqlite> SELECT 1 WHERE datetime('2007-01-01') > date('2007-01-01');

comment:3 Changed 9 years ago by Malcolm Tredinnick

Keywords: qs-rf added

comment:4 Changed 9 years ago by James Bennett

Resolution: duplicate
Status: newclosed

Closing in favor of #3689, which has patches.

comment:5 Changed 6 years ago by anonymous

Has patch: set
Patch needs improvement: set
Resolution: duplicate
Status: closedreopened

in db/backends/sqlite3/ the time is incorrect.

def year_lookup_bounds(self, value):
    first = '%s-01-01'
    second = '%s-12-31 23:59:59.999999'
    return [first % value, second % value]

the correct format is '%s-12-31T23:59:59.999999' according to

the path of #3689 don't solve the problem

comment:6 Changed 6 years ago by Matthias Kestenholz

Resolution: worksforme
Status: reopenedclosed

Cannot reproduce, please provide a testcase if this is still a problem.

Note: See TracTickets for help on using tickets.
Back to Top