Opened 14 years ago

Closed 14 years ago

Last modified 12 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: no UI/UX: no

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 14 years ago.

Download all attachments as: .zip

Change History (4)

comment:1 by tzulberti, 14 years ago

Owner: set to tzulberti

by tzulberti, 14 years ago

Attachment: ticket_14016.diff added

comment:2 by tzulberti, 14 years ago

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 by Anssi Kääriäinen, 12 years ago

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