Sqlite3: how to match on a range of month/year as separate text fields

Question

This is sqlite3 which excludes other solutions I've read here for other SQL implementations.

I have a table "DR" which has, among other data, columns for "month" (1-12 without the zero padding) and "year" (yyyy) which are both text columns.

I want to retrieve all of the lines where the month/year are between two particular pairings, eg from year 2017 month 6 through year 2020 month 4. Those 4 values are also coming in (from python) as text with no padding on the month. I want to be sure to also handle the case of less than a full year, eg 2017 month 2 through 2017 month 10.

I figured out how to do it by brute force with an absurd number of ORs and never touching an actual date-related function, but I was wondering if there was a more elegant way to do it that took advantage of sql actually understanding a date being greater or lesser than another date with rolling month numbers across year boundaries.

Thank you for any help you can provide!

Have you read through the documentation? Does sqlite support date objects/fields? SQLite and Python types .. sqlite.org/lang_datefunc.html — wwii
I imagine you would need to create a separate field/column that combines the month and year of each row into an 'YYYY-MM-01' string. Lots of results searching with python sqlite3 dates site:stackoverflow.com — wwii
Comparison of dates stored as separate fields- from the SQLite docs. — wwii
The "comparison of dates stored as separate fields" almost perfectly does what I need, but there's something very confusing going on. — Melissa Belvadi
The "comparison of dates stored as separate fields" is exactly what I needed! I did discover that I have to include fake day numbers as it would lose the last month in the end point of the between if I didn't. So this works: WITH myconstants as (select '2017' as startyear, '6' as startmonth, '2020' as endyear, '4' as endmonth) select distinct month, year from DR where (year, month,'1') BETWEEN ((select startyear from myconstants),(select startmonth from myconstants),'1') AND ((select endyear from myconstants),(select endmonth from myconstants),'28') order by year, month; — Melissa Belvadi

BenWS BenWS · Accepted Answer · 2020-05-24T15:22:23

If understand your question correctly - you're looking to apply a date range filter when your table only contains month and year numbers?

You can create an auxiliary 'dates' table to join to your CR table:

create table digits (value integer);

-- create auxiliary digits table (for generating dates)
insert into digits values (1),(2),(3),(4),(5),(6),(7),(8),(9);

create table numbers (value integer);

-- create auxiliary numbers table (for generating dates)
insert into numbers (value)
select d1.value + d2.value * 10 + d3.value *100 as value
from digits as d1
    cross join digits as d2
    cross join digits as d3;

-- create auxliary dates table
create table dates (month_begin_date, month_number, year_number);
insert into dates (
    month_begin_date
    ,month_number
    ,year_number)
select date('now', '-'|| value || ' day')
, cast(strftime('%m',date('now', '-'|| value || ' day')) as integer) as month_number
, cast(strftime('%Y',date('now', '-'|| value || ' day')) as Integer) as year_number
from numbers
where cast(strftime('%d',date('now', '-'|| value || ' day')) as integer) = 1;

create table CR (month_number integer, year_number integer, some_column varchar(100));

insert into CR values 
    (1, 2020, 'a'),
    (2, 2020, 'b'),
    (3, 2020, 'c'),
    (4, 2020, 'd');

-- join 'CR' table to auxiliary table here for final filtering
select dates.month_begin_date, dates.month_number, dates.year_number, CR.some_column
from dates
    inner join CR 
        on CR.month_number = dates.month_number
        and CR.year_number = dates.year_number
where dates.month_begin_date > '1/1/2020' and dates.month_begin_date < '3/1/2020';

Sqlite3: how to match on a range of month/year as separate text fields

2 Answers