Get range of dates across multiple years-CodePudding

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

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

feb 1 2022
feb 2 2022
feb 1 2021
feb 2 2021
... 
feb 1 1991
feb 2 1991
feb 1 1990
feb 2 1990

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

jan 31 2022
feb 1 2022
feb 2 2022
jan 31 2021
feb 1 2021
feb 2 2021
... 
jan 31 1991
feb 1 1991
feb 2 1991
jan 31 1990
feb 1 1990
feb 2 1990

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.

CodePudding user response：

To get the years within a date range in a SQLite table, you can use the strftime function to extract the year from the date column and then use the distinct keyword to only return unique years.

SELECT DISTINCT strftime('%Y', date_column) as year
FROM table_name
WHERE date_column BETWEEN '2022-01-01' AND '2022-12-31'

CodePudding user response：

Consider filtering query by building dates with date() keeping year of current date. Date ranges across years may need to be split with a self-join or union:

SELECT * 
FROM my_table
WHERE my_date BETWEEN date(strftime('%Y', my_date) ||'-01-31')
                  AND date(strftime('%Y', my_date) ||'-02-02')