How could i get the sqlite date delta in SQLAlchemy
I have a sqlite table with a column final_date in INTEGER type (Use as date). I want to calculate number of days from this column to now. In sqlite, i can run SQL with help of function (julianday) as following.
select (julianday(final_date) - julianday('now')) AS days from bond where days > 0 109.407374872826 482.407374872826 488.407374872826 ....
However, in SQLAlchemy, code as following can not get the accurate number of days.
today = datetime.today().date()
query = session.query( Bond.final_date - today )
I wonder whether there is an effective way to apply function julianday to SQLAlchemy code or whether there is an alternative native approach in SQLAlchemy to accomplish this.
If you have control on database and data - you may want to update schema to define column as sqlalchemy.types.DateTime. That use datetime module to make calculations with dates.
Or you can use sqlalchemy.sql.func to access julianday() sqlite function.
from sqlalchemy.sql import func q = session.query( func.julianday( Bond.final_date) - func.julianday("now")) for row in q.all(): print row
Please note that the second approach uses sqlite specific function and won't work with other database systems.