Flask-sqlalchemy query datetime intervals

The following should also work:

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import INTERVAL
from sqlalchemy.sql.functions import concat

Notes.query\
    .filter(
        Notes.added_at >= (func.now() - func.cast(concat(8, ' HOURS'), INTERVAL))
    )\
    .limit(num)

It has the nice property that 8 can be replaced with a value from inside the database, e.g., if you joined in another table with dynamic intervals. I gave this answer also here.

I always have Python's datetime library get me the "now" and "8 hours ago", then just do a filter using the datetimes:

from datetime import datetime, timedelta

now = datetime.now()
eight_hours_ago = now - timedelta(hours=8)

Notes.query.filter(Notes.added_at > eight_hours_ago).filter(Notes.added_at < now).all()

You can try something like

Notes.query.order_by(desc(Notes.added_at)).filter(
    Notes.added_at >= text('NOW() - INTERVAL 8 HOURS').limit(num)

As I only use pure sqlalchemy I tested this out with this syntax:

>>> from sqlalchemy import text
>>> # s is a standard sqlalchemy session created from elsewhere.
>>> print s.query(Notes).order_by(desc(Notes.added_at)).filter(
...     Notes.added_at >= text('NOW() - INTERVAL 8 HOURS'))
SELECT notes.id AS notes_id, notes.notes AS notes_notes, notes.added_at AS notes_added_at 
FROM notes 
WHERE notes.added_at >= NOW() - INTERVAL 8 HOURS ORDER BY notes.added_at DESC

Reason for using text for that section is simply because NOW() and INTERVAL usage is not consistent across all sql implementations (certain implementations require the use of DATEADD to do datetime arithmetic, and while sqlalchemy does support the Interval type it's not really well documented, and also on my brief testing with it it doesn't actually do what you needed (using example from this answer, for both sqlite and MySQL). If you intend to use the SQL backend as an ordered (but dumb) data store you can just construct the actual query from within Python, perhaps like so:

q = s.query(Notes).order_by(desc(Notes.added_at)).filter(
    Notes.added_at >= (datetime.utcnow() - timedelta(3600 * 8))
)

Some people dislike this as some databases (like postgresql) can deal with datetime better than Python (such as timedelta is ignorant of leap years, for instance).

Can also try

from sqlalchemy import func, text

@staticmethod
def newest(num):
    return Notes.query.filter(Notes.added_at >= (func.date_sub(func.now(),  text('INTERVAL  8 HOUR')).order_by(desc(Notes.added_at)).limit(num)

OR

from datetime import datetime, timedelta
from dateutil import tz 

@staticmethod
def newest(num):
    recent = datetime.now(tz=tz.tzlocal()) - timedelta(hours=8)

    return Notes.query.filter(Notes.added_at >= recent).order_by(desc(Notes.added_at)).limit(num)

SQLAlchemy automatically adapts Python timedelta instances to correct interval queries. To use them you will need to rewrite your query in such a way that either the left-hand-side or right-hand-side of the comparison operator is an interval. That way it becomes comparable with timedeltas.

You query currently reads like this:

WHERE added_at >= now() - INTERVAL 8 HOUR

On both sides of the comparator >= you have absolute timestamps. And this is difficult to translate to SQLAlchemy. You can rewrite it like this:

WHERE now() - added_at >= INTERVAL 8 HOUR

This gives you intervals on both sides. This is now pretty easy to translate to SQLAlchemy:

from datetime import timedelta
from sqlalchemy import func

class Notes(db.Model):
    @staticmethod
    def newest(num):
        query = Notes.query.filter(
            func.now() - Notes.added_at >= timedelta(hours=8)
        )
        return query.order_by(desc(Notes.added_at)).limit(num)

Compared to the existing answers, this...

• ... ensures that you use DB-internal timestamps (as opposed to using datetime.now() from Python)
• ... does not require any type-casting
• ... does not use string-concatenations
• ... does not use raw text()