convert selected datetime to date in sqlalchemy

Question

I have a database of test records with one column 'test_time' defined as datetime. I want to query how many distinct dates there are as I want to dump test results to csv according to dates. I now have the following:

distinct_dates = list(session.query(Test_Table.test_time).distinct())

But this gives me a list of datetime not date. Certainly I can convert it in Python but when I am using sqlite. I did this SELECT DISTINCT DATE(test_time) FROM Test_Table. I cannot come up with the equivalent in sqlalchemy.

Answer 1

That would be by using the cast() expression:

from sqlalchemy import cast, Date

distinct_dates = session.query(cast(Test_Table.test_time, Date)).distinct().all()

or the shortcut method:

distinct_dates = session.query(Test_Table.test_time.cast(Date)).distinct().all()

and if using SQLite, give this a shot:

distinct_dates = session.query(func.DATE(Test_Table.test_time)).distinct().all()