Search code examples
pythonsqldatabasesqlitepeewee

Get range of dates across multiple years

I have a sqllite table which contains a datetime col of dates and col of real numbers. Any ideas on how i can query to get all all the years within that date range

Note the dates are stored as yyyy-mm-dd For example if I had a table with all the dates from Jan 1 1990 - Dec 31 2021 and I wanted between Feb 1 and Feb 2 I would get

2022-02-01
2022-02-02
2021-02-01
2021-02-02
... 
1991-02-01
1991-02-02
1990-02-01
1990-02-02

or if i query Jan 31 to Feb 2 I would get

2022-01-31
2022-02-01
2022-02-02
2021-01-31
2021-02-01
2021-02-02
... 
1991-01-31
1991-02-01
1991-02-02
1991-01-31
1991-02-01
1991-02-02

I'm trying to do this using peewee for python but I'm not even sure how to write this sql statement. From what I've seen sql has a between statement but this wont work as it would give me only one year when I want every record in the database between a given range.

Solution

  • Since it appears that the author's sample data posted originally was not representative of the actual data, and the ACTUAL data is stored in YYYY-MM-DD, then it is quite simple using sqlite's strftime():

    start = '01-31'
end = '02-02'
query = (Reg.select()
         .where(fn.strftime('%m-%d', Reg.date).between(start, end)))
for row in query:
    print(row.date)

    Should print something like:

    2018-01-31
2018-02-01
2018-02-02
2019-01-31
...