Sqlalchemy; count items between two dates over a time period-CodePudding

My postgres DB has a Price table where I store price data for a bunch of products. For each Price object I store when it was created (Price.timestamp), and whenever there is a new price for the product, I create a new Price object, and for the old Price object I store when it ended (Price.end_time). Both times are datetime objects.

Now, I want to count how many Prices there are at over a time period. Easy I thought, so I did the query below:

trunc_date = db.func.date_trunc('day', Price.timestamp)

query = db.session.query(trunc_date, db.func.count(Price.id))
query = query.order_by(trunc_date.desc())
query = query.group_by(trunc_date)
prices_count = query.all()

Which is great, but only counts how many prices were new/created for each day. So what I thought I could do, was to filter so that I would get prices where the trunc_date is between the beginning and the end for the Price, like below:

query = query.filter(Price.timestamp < trunc_date < Price.time_ended)

But apparently you are not allowed to use trunc_date this way. Can anyone help me with how I am supposed to write my query?

CodePudding user response：

Have you tried separating the conditions inside the filter?

query = db.session.\
    query(trunc_date, db.func.count(Price.id)).\
    filter(
        (Price.timestamp < trunc_date),
        (trunc_date < Price.time_ended)
        ).\
    group_by(trunc_date).\
    order_by(trunc_date.desc()).\
    all()

CodePudding user response：

you can use

trunc_date.between(Price.timestamp, Price.time_ended)