7

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?

SS_Rebelious
  • 1,274
  • 2
  • 19
  • 35

1 Answers1

11

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)

coleifer
  • 24,887
  • 6
  • 60
  • 75
  • I have used peewee to create and populate database and peewee stores dates as `dd-mm-YYYY` in the database – SS_Rebelious Sep 06 '16 at 20:18
  • That's very odd, peewee will store them as YYYY-mm-dd by default. At any rate, that is why your code is broken. – coleifer Sep 07 '16 at 12:25
  • .between() works great, could not find it in the peewee documentation. Thank you – Radek Aug 26 '18 at 20:59
  • Docs: http://docs.peewee-orm.com/en/latest/peewee/query_operators.html#query-operators – coleifer Aug 30 '18 at 21:08
  • 1
    Note: If your records include time, be aware that the end of the `.between()` range is not included, SQL `BETWEEN` is not half-open. Some more info [here](https://softwareengineering.stackexchange.com/questions/160191/why-is-sqls-between-inclusive-rather-than-half-open). – tiktuk Dec 27 '21 at 17:41
  • Truncate to day in that case, like [here](https://stackoverflow.com/a/52196070/111510). – tiktuk Dec 27 '21 at 19:54