How to query date by year?-CodePudding

Following query in SQLite Studio for a date in 2021 or 2022 returns all rows correspondingly:

select * FROM daily_reports
WHERE strftime('%Y', daily_reports.reporting_date) in ('2021','2022');

Same query in Python returns no rows and the where clause created is ... where 1 = 0:

q = ...    
q = q.filter((func.strftime('%Y', DailyReportItem.reporting_date) in ['2021','2022']))

How to make it work?

CodePudding user response：

The problem is that the statement you passed into .filter() does not get converted into SQL, it is executed as false in Python right away and then passed down to .filter() as argument.

The way you do the IN operator in SQLAlchemy is via DailyReportItem.reporting_date.in_(['2021', '2022']).

So in your case it should be something like this: q.filter(func.strftime('%Y', DailyReportItem.reporting_date).in_(['2021', '2022']))

I would rather just check if date is bigger than the beginning of the year 2021 and lower than the end of 2022. This way you would also get a better use of indexes.