Why changing DateField formats in python peewee makes queries go wrong?

Question

I was working with peewee to work with a database and faced a problem when using formats for DateField. Here is my code:

import datetime as dt
import peewee as pw
db = pw.SqliteDatabase('people.db')

class Person(pw.Model):
    name = pw.CharField()
    birthday = pw.DateField()

    class Meta:
        database = db # This model uses the "people.db" database.

db.create_tables([Person])

uncle_bob = Person(name='Bob', birthday=dt.date(1960, 1, 21))
uncle_bob.save()

for item in Person.select().where(Person.birthday > dt.date(1980, 1, 1)):
    print item.birthday, item.birthday > dt.date(1980, 1, 1)

Everything works fine upto this point, and as expected nothing is printed. The problem starts when I put formats for DateField as follows:

import datetime as dt
import peewee as pw
db = pw.SqliteDatabase('people.db')

class Person(pw.Model):
    name = pw.CharField()
    birthday = pw.DateField(formats=['%d-%b-%Y'])

    class Meta:
        database = db # This model uses the "people.db" database.

db.create_tables([Person])

uncle_bob = Person(name='Bob', birthday='21-Jan-1960')
uncle_bob.save()

for item in Person.select().where(Person.birthday > dt.date(1980, 1, 1)):
    print item.birthday, item.birthday > dt.date(1980, 1, 1)

Although I expect nothing to be printed as before, it prints:

1960-01-21 False

Can anyone help me figure out what is going on and how I may fix it?

score 2 · Answer 1 · answered Sep 06 '15 at 23:59

You guys are insane. If you took one second to read the docs you wouldn't waste time posting these elaborate questions on SO.

SQLite stores dates as strings. Strings are sorted byte-wise. Using a format other than %Y-%m-%d will not sort the dates correctly.

So with SQLite, always store your dates as %Y-%m-%d (which is the peewee default anyways).

Ebrahim Ghasemi · Answer 2 · 2015-09-02T08:26:02.533

I add some lines to your programs as follows to find the point of problem :

First program:

import datetime as dt
import peewee as pw
db = pw.SqliteDatabase('people.db')

class Person(pw.Model):
    name = pw.CharField()
    birthday = pw.DateField()

    class Meta:
        database = db # This model uses the "people.db" database.

db.create_tables([Person])

uncle_bob = Person(name='Bob', birthday=dt.date(1960, 1, 21))
uncle_bob.save()

print "----------- Printing object details -----------"
print "Person.birthday    ---> ", Person.birthday, "  :::::  ",type(Person.birthday)
print "dt.date(1980, 1, 1)---> ", dt.date(1980, 1, 1), "  :::::  ",type(dt.date(1980, 1, 1))
print

print "----------- Printing comparison results -----------"
if Person.birthday > dt.date(1980, 1, 1):
    print "Person.birthday > dt.date(1980, 1, 1) ?", 'Yes!'
else:
    print "Person.birthday > dt.date(1980, 1, 1) ?", 'No!'
print

print "----------- Printing final output without using 'where()' method -----------"
for item in Person.select():
    print item.birthday, item.birthday > dt.date(1980, 1, 1)
print

print "----------- Printing final output using 'where()' method -----------"
for item in Person.select().where(Person.birthday > dt.date(1980, 1, 1)):
    print item.birthday, item.birthday > dt.date(1980, 1, 1)
print

print  "+++++++++++++++++"
print Person.birthday > dt.date(1980, 1, 1)

Its output:

>>> ================================ RESTART ================================
>>> 
----------- Printing object details -----------
Person.birthday    --->  <peewee.DateField object at 0x033B8970>   :::::   <class 'peewee.DateField'>
dt.date(1980, 1, 1)--->  1980-01-01   :::::   <type 'datetime.date'>

----------- Printing comparison results -----------
Person.birthday > dt.date(1980, 1, 1) ? Yes!

----------- Printing final output without using 'where()' method -----------
1960-01-21 False

----------- Printing final output using 'where()' method -----------

+++++++++++++++++
<peewee.Expression object at 0x033BCF90>
>>>

Second program:

import datetime as dt
import peewee as pw
db = pw.SqliteDatabase('people1.db')

class Person(pw.Model):
    name = pw.CharField()
    birthday = pw.DateField(formats=['%d-%b-%Y'])

    class Meta:
        database = db # This model uses the "people.db" database.

db.create_tables([Person])

uncle_bob = Person(name='Bob', birthday='21-Jan-1960')
uncle_bob.save()

print "----------- Printing object details -----------"
print "Person.birthday    ---> ", Person.birthday, "  :::::  ",type(Person.birthday)
print "dt.date(1980, 1, 1)---> ", dt.date(1980, 1, 1), "  :::::  ",type(dt.date(1980, 1, 1))
print

print "----------- Printing comparison results -----------"
if Person.birthday > dt.date(1980, 1, 1):
    print "Person.birthday > dt.date(1980, 1, 1) ?", 'Yes!'
else:
    print "Person.birthday > dt.date(1980, 1, 1) ?", 'No!'
print

print "----------- Printing final output without using 'where()' method -----------"
for item in Person.select():
    print item.birthday, item.birthday > dt.date(1980, 1, 1)
print

print "----------- Printing final output using 'where()' method -----------"
for item in Person.select().where(Person.birthday > dt.date(1980, 1, 1)):
    print item.birthday, item.birthday > dt.date(1980, 1, 1)
print

print  "+++++++++++++++++"
print Person.birthday > dt.date(1980, 1, 1)

Its output:

>>> ================================ RESTART ================================
>>> 
----------- Printing object details -----------
Person.birthday    --->  <peewee.DateField object at 0x032949D0>   :::::   <class 'peewee.DateField'>
dt.date(1980, 1, 1)--->  1980-01-01   :::::   <type 'datetime.date'>

----------- Printing comparison results -----------
Person.birthday > dt.date(1980, 1, 1) ? Yes!

----------- Printing final output without using 'where()' method -----------
1960-01-21 False

----------- Printing final output using 'where()' method -----------
1960-01-21 False

+++++++++++++++++
<peewee.Expression object at 0x03298FF0>
>>>

Well, it seems that there is something wrong with the where() method!

Thanks Abraham, I also think that there must be something wrong with source code of where( ) function. — Mehdi Jafarnia Jahromi, Sep 02 '15 at 04:58

Ebrahim Ghasemi · Answer 3 · 2015-09-02T08:35:19.147

Well, although I couldn't find out what is the origin of the problem that we found in my other answer, but I found a way to bypass the issue :

Simply replace formats=['%d-%b-%Y'] with formats=['%Y-%m-%d'] (And also other stuff related to the format, for sure):

An example:

import datetime as dt
import peewee as pw
db = pw.SqliteDatabase('people.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])

bob = Person(name = 'Bob', birthday = '1960-1-21')
james = Person(name = 'James', birthday = '1965-1-22')
steve = Person(name = 'Steve', birthday = '1970-1-20')
alex = Person(name = 'Alex', birthday = '1975-1-18')
alen = Person(name = 'Alen', birthday = '1961-12-28')
bob.save()
james.save()
steve.save()
alex.save()
alen.save()

for item in Person.select().where(Person.birthday > dt.date(1963,1,22)):
    print item.name,item.birthday, item.birthday > dt.date(1963,1,22)

Output:

>>> ================================ RESTART ================================
>>> 
James 1965-01-22 True
Steve 1970-01-20 True
Alex 1975-01-18 True
>>>

But why?

Sqlite does not have a native date-time format (it mimics it as a string), when we change the date format to "%Y-%m-%d"; this will automatically be sorted correctly as a string, which then will work for Sqlite.

I posted this as a new answer, because I wanted to keep the previous answer available for the future viewers (Maybe somebody can help us to detect the `where()` method problem), and also I wanted the answer to be as short and clear as possible simultaneously. :) — Ebrahim Ghasemi, Sep 02 '15 at 08:33

Why changing DateField formats in python peewee makes queries go wrong?

3 Answers3