Search code examples
sqlalchemyflask-sqlalchemyweek-number

Find rows having a certain Week Number in a date using (Flask) SQLAlchemy

Using sqlalchemy, resp. flask_sqlalchemy and sqlite I would like to query all rows which have the date field from last week. Best would even be where the date column values are in a certain week of the year:

  • reporting_date = db.Column(db.Date, nullable=False, index=True)

thinking of something like (pseudo code):

  • found_rows = Table.query.filter_by(strftime('%W',reporting_date)==7).all()
Solution

  • Solved my issue. My pseudo code was almost correct so here the working statement:

    from sqlalchemy import func
DailyReportItem.query.filter(func.strftime('%W',DailyReportItem.reporting_date)=="07").all()

    wich, in sqlite translates to the following:

    SELECT *
FROM daily_reports
WHERE strftime(?, daily_reports.reporting_date) = ?
('%W', '07')

    the issue was basically only that Week Numbers have leading zeros and are Strings