sqlalchemy select by date column only x newset days

Question

suppose I have a table MyTable with a column some_date (date type of course) and I want to select the newest 3 months data (or x days). What is the best way to achieve this?

Please notice that the date should not be measured from today but rather from the date range in the table (which might be older then today)

I need to find the maximum date and compare it to each row - if the difference is less than x days, return it.

All of this should be done with sqlalchemy and without loading the entire table. What is the best way of doing it? must I have a subquery to find the maximum date? How do I select last X days?

Any help is appreciated.

EDIT: The following query works in Oracle but seems inefficient (is max calculated for each row?) and I don't think that it'll work for all dialects:

select * from my_table where (select max(some_date) from my_table) - some_date < 10

Answer 1

I ended up doing two selects - one to get the max date and another to get the data

using the datediff recipe from this thread I added a datediff function and using the query q = session.query(MyTable).filter(datediff(max_date, some_date) < 10)

I still don't think this is the best way, but untill someone proves me wrong, it will have to do...

Answer 2

You can do this in a single query and without resorting to creating datediff.

Here is an example I used for getting everything in the past day:

    one_day = timedelta(hours=24)
    one_day_ago = datetime.now() - one_day
    Message.query.filter(Message.created > one_day_ago).all()

You can adapt the timedelta to whatever time range you are interested in.

UPDATE

Upon re-reading your question it looks like I failed to take into account the fact that you want to compare two dates which are in the database rather than today's day. I'm pretty sure that this sort of behavior is going to be database specific. In Postgres, you can use straightforward arithmetic.

Operations with DATEs
1. The difference between two DATES is always an INTEGER, representing the number of DAYS difference
DATE '1999-12-30' - DATE '1999-12-11' = INTEGER 19
You may add or subtract an INTEGER to a DATE to produce another DATE
DATE '1999-12-11' + INTEGER 19 = DATE '1999-12-30'

You're probably using timestamps if you are storing dates in postgres. Doing math with timestamps produces an interval object. Sqlalachemy works with timedeltas as a representation of intervals. So you could do something like:

one_day = timedelta(hours=24)
Model.query.join(ModelB, Model.created - ModelB.created < interval)

I haven't tested this exactly, but I've done things like this and they have worked.