Opened 8 years ago

Closed 5 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:

Description

With DATABASE_ENGINE = 'sqlite3' and the following foo/models.py:

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 follow-up: Changed 8 years ago by mattmcc

  • 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 8 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');
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');
1

comment:3 Changed 8 years ago by mtredinnick

  • Keywords qs-rf added

comment:4 Changed 8 years ago by ubernostrum

  • Resolution set to duplicate
  • Status changed from new to closed

Closing in favor of #3689, which has patches.

comment:5 Changed 5 years ago by anonymous

  • Has patch set
  • Patch needs improvement set
  • Resolution duplicate deleted
  • Status changed from closed to reopened

in db/backends/sqlite3/base.py 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 http://www.sqlite.org/lang_datefunc.html

the path of #3689 don't solve the problem

comment:6 Changed 5 years ago by mk

  • Resolution set to worksforme
  • Status changed from reopened to closed

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

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