SQLalchemy filter cast datetime by day of week

user_7828747 0
Viewed 1628

I have a Datetime column and I am trying to filter that column by everything occurring on a Saturday.

I can filter Datetime by casting it to a date and comparing it to a date string like this:

session.query(SQL_table).filter(cast(SQL_table.datetime_column, Date) == '2020-7-1').all()

But how do I say I want that column to return everything that is on a certain day of the week? I tried this:

session.query(SQL_table).filter(cast(SQL_table.datetime_column, Date).strftime('%A') == 'Saturday').all()

This gives me the error:

AttributeError: Neither 'Cast' object nor 'Comparator' object has an attribute 'strftime'

So how do I cast the column to a weekday and filter it by a specific day then?

python-datetime sqlalchemy python
2 Answers

To solve your problem you can specify function which users use to extract day of week in your SQL dialect using function generator from sqlalchemy package. For example, Transact-SQL has format() function, Oracle SQL has to_char(date_column, 'D') function for day's of week extraction. Using this approach you can specify custom function that will be suitable for your SQL dialect.

from sqlalchemy import func

day_of_week_num = 6
day_of_week_str = 'Sunday'

# Example for Transact-SQL
session.query(SQL_table).filter(func.format(SQL_table.datetime_column, 'dddd') == day_of_week_str).all()

# Example for Oracle SQL
session.query(SQL_table).filter(func.to_char(SQL_table.datetime_column, 'D') == day_of_week_num).all()
user_51074880

Try using func.dayofweek

from sqlalchemy import func

...

# 1 = Sunday, 2 = Monday, ..., 7 = Saturday.
# Therefore, to get Saturday:

session.query(SQL_table).filter(func.dayofweek(
                    SQL_table.datetime_column) == 7).all()
user_14516490
Related