How to query dates range using peewee?

Question

I have SQLite database with tables that contains dates. I want ot select records that falls in particular range, but I fail to write correct query.

# This query returns nothing
rows = model.select().where(  
                 (model.date.between(start_date, end_date)) & 
                 (model.name == point_name)
         ).tuples()


# This query returns nothing too
rows = model.select().where(  
                 (model.date > start_date) &
                 (model.date < end_date) &
                 (model.name == point_name)
         ).tuples()


# However tis one works:
rows = model.select().where(  
             (model.date > start_date) &
             (model.name == point_name)
         ).tuples()

Why my code is working when query dates that are either begger or smaller than the given and fails when I try to query range of dates?

Answer 1

How are you storing your dates in the database? Was this a pre-existing database, or did you use Peewee to insert all the records? I ask because SQLite can represent a date as either a string or a number (timestamp). If you are using a string, then the dates must sort lexicographically to make meaningful comparisons...that's why Peewee uses YYYY-mm-dd.

If your dates are sorted correctly, then you should be able to use .between() if you want inclusive endpoints. Otherwise you can do (date_field > low) & (date_field < high)