Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Flask-sqlalchemy query datetime intervals

I have defined a table with flask-sqlalchemy. Displayed below.

class Notes(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    notes = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    added_at = db.Column(db.DateTime, default=db.func.now())

@staticmethod
def newest(num):
    return Notes.query.order_by(desc(Notes.added_at)).limit(num)

I'm attempting to write a query that is to replace and already existing direct query, which looks like this.

select notes,user,added_at from notes where added_at >= now() - INTERVAL 8 HOUR;

However based on the documentation that I can find, I'm not able to find a method to do the same. I'm able to make simpler queries, but I'm struggling to recreate what's pretty simple in sql itself.

I'm more than willing to read some documentation surrounding it, wasn't able to precisely nail that down either. Any direction you could provide would be awesome.

like image 496
Harry Avatar asked May 28 '15 01:05

Harry


2 Answers

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()
like image 87
TheGrimmScientist Avatar answered Sep 27 '22 19:09

TheGrimmScientist


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.

like image 34
André C. Andersen Avatar answered Sep 27 '22 20:09

André C. Andersen