Opened 6 years ago

Closed 6 years ago

Last modified 4 years ago

#14016 closed (worksforme)

SQLite3 problem with date comparison

Reported by: anonymous Owned by: tzulberti
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords: sqlite, date comparison
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I've hit a bug using Django 1.2.1.

I've a model with date types. Django generates a query like this that returns the correct results:

SELECT xxx FROM "table" WHERE ("table"."pub_date" >= 2010-01-24 );

If I switch to "lt" or "lte" it gives me nothing, because sqlite seems to want "'" around the date. You can see for yourself in the following test case run with SQLite 3.6.16 and 3.7.0.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

$ sqlite3 prova.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( d date null);
sqlite> insert into foo(d) values( '2009-09-09' );
sqlite> select * from foo where (d >= 2007-01-01);
2009-09-09
sqlite> select * from foo where (d <= 2010-01-01);
sqlite> select * from foo where (d <= '2010-01-01');
2009-09-09
sqlite>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

$ sqlite3 prova.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( d date null);
sqlite> insert into foo(d) values( '2009-09-09' );
sqlite> select * from foo where (d >= 2007-01-01);
2009-09-09
sqlite> select * from foo where (d <= 2010-01-01);
sqlite> select * from foo where (d <= '2010-01-01');
2009-09-09
sqlite>

Attachments (1)

ticket_14016.diff (1.3 KB) - added by tzulberti 6 years ago.

Download all attachments as: .zip

Change History (4)

comment:1 Changed 6 years ago by tzulberti

Owner: set to tzulberti

Changed 6 years ago by tzulberti

Attachment: ticket_14016.diff added

comment:2 Changed 6 years ago by tzulberti

Resolution: worksforme
Status: newclosed

I don't have that problem. Feel free to change the change anything.

The patch is an example I run against django code.

Models:

class Reporter(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)

    def __unicode__(self):
        return u"%s %s" % (self.first_name, self.last_name)


class Article(models.Model):
    headline = models.CharField(max_length=100)
    pub_date = models.DateField()
    reporter = models.ForeignKey(Reporter)

    def __unicode__(self):
        return self.headline

The test:

    def test_regression_14016(self):
        reporter = models.Reporter.objects.create(first_name='Django',
                                                  last_name='Spring')
        article = models.Article.objects.create(
                            reporter=reporter,
                            pub_date=datetime.date.today())


        self.assertEquals(len(models.Article.objects.all()), 1)
        articles = models.Article.objects.filter(
                            pub_date__gte = datetime.date.today())
        self.assertEquals(len(articles), 1)
        articles = models.Article.objects.filter(
                            pub_date__lte = datetime.date.today())
        self.assertEquals(len(articles), 1)

comment:3 Changed 4 years ago by Anssi Kääriäinen

Component: ORM aggregationDatabase layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top