How to extract correct data from Sqlite database using Python?
Question: I have a database of people names and their birthdays. The format of birthday
is `mm/dd/yyyy`, like "3/13/1960".

I want to extract a list of people who are born after a specific date. I
called this date "base".

The program that you see below, firstly creates a DB of people (to simulate
the real DB that I want to work with), and then extracts the required list.
The problem is that the result is not as I expect:

    
    
    import datetime as dt
    import peewee as pw
    db = pw.SqliteDatabase('people1.db')
    
    class Person(pw.Model):
        name = pw.CharField()
        birthday = pw.DateField(formats=['%m/%d/%Y'])
        class Meta:
            database = db # This model uses the "people.db" database.
    
    db.create_tables([Person])
    
    
    bob0 = Person(name='Bob0', birthday='4/13/1940')
    bob1 = Person(name='Bob1', birthday='5/13/1950')
    bob2 = Person(name='Bob2', birthday='3/13/1960')
    bob3 = Person(name='Bob3', birthday='3/13/1970')
    bob4 = Person(name='Bob4', birthday='3/13/1980')
    bob5 = Person(name='Bob5', birthday='3/13/1990')
    
    base = Person(name="base", birthday='3/13/1960')
    
    bob0.save()
    bob1.save()
    bob2.save()
    bob3.save()
    bob4.save()
    bob5.save()
    base.save()
    
    for item in Person.select().where(Person.birthday > base.birthday):
        print item.name , item.birthday
    

Output:

    
    
    >>> ================================ RESTART ================================
    >>> 
    Bob0 1940-04-13
    Bob1 1950-05-13
    Bob3 1970-03-13
    Bob4 1980-03-13
    Bob5 1990-03-13
    >>> 
    

As you see above, base = `3/13/1960`. So I shouldn't have _Bob0_ and _Bob1_ in
output! How can I handle it?

_Note that, I don't want to change the format of birthdays in the database. I
also don't want to fetch all the rows and check them later! I want to fetch
the required rows only._


Answer: SQlite stores date-times as strings. So as other have suggested in comments
and other [answers](http://stackoverflow.com/a/1933735/248296) you should use
a different format for storing dates, so that "date ordering and lexical
ordering work out the same":

    
    
    import datetime as dt
    import peewee as pw
    
    db = pw.SqliteDatabase('people1.db')
    
    class Person(pw.Model):
        name = pw.CharField()
        birthday = pw.DateField(formats=['%Y-%m-%d'])
        class Meta:
            database = db # This model uses the "people.db" database.
    
    db.create_tables([Person])
    
    
    Person.create(name='Bob0', birthday=dt.date(1940, 4, 13))
    Person.create(name='Bob1', birthday=dt.date(1950, 5, 13))
    Person.create(name='Bob2', birthday=dt.date(1960, 3, 13))
    Person.create(name='Bob3', birthday=dt.date(1970, 3, 13))
    Person.create(name='Bob4', birthday=dt.date(1980, 3, 13))
    Person.create(name='Bob5', birthday=dt.date(1990, 3, 13))
    
    base = Person.create(name="base", birthday=dt.date(1960, 3, 13))
    
    for item in Person.select().where(Person.birthday > base.birthday):
        print item.name , item.birthday
    

This gives:

    
    
    Bob3 1970-03-13
    Bob4 1980-03-13
    Bob5 1990-03-13
    

**UPDATE**

I haven't noticed your comment that you don't want to change the database.

Here is a crazy way to extract parts of the date:

    
    
    SELECT
        birthday,
        CAST(substr(birthday, 1, instr(birthday, '/') - 1) AS integer),
        CAST(substr(substr(birthday, instr(birthday, '/') + 1), 1, instr(substr(birthday, instr(birthday, '/') + 1), '/') - 1) AS integer),
        CAST(substr(birthday, instr(birthday, '/') + instr(substr(birthday, instr(birthday, '/') + 1), '/') + 1) AS integer)
    FROM person
    

which on my test data gives:

    
    
    4/13/1940   4   13  1940
    12/13/1950  12  13  1950
    3/3/1960    3   3   1960
    3/25/1970   3   25  1970
    3/13/1980   3   13  1980
    3/13/1990   3   13  1990
    3/13/1960   3   13  1960
    

You can use these expressions to compare them with parts of the given date:

    
    
    query = """
    SELECT *
    FROM person
    WHERE
        (
            substr('0000' || CAST(substr(birthday, instr(birthday, '/') + instr(substr(birthday, instr(birthday, '/') + 1), '/') + 1) AS integer), -4, 4) || '-' || -- year
            substr('00' || CAST(substr(birthday, 1, instr(birthday, '/') - 1) AS integer), -2, 2) || '-' || -- month
            substr('00' || CAST(substr(substr(birthday, instr(birthday, '/') + 1), 1, instr(substr(birthday, instr(birthday, '/') + 1), '/') - 1) AS integer), -2, 2) -- day
        ) > '1960-03-03'
    """
    for item in Person.raw(query):
        print item.name, item.birthday
    

I am reconstructing ISO date here and use it for comparison.

