Question 114454 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Sqlalchemy; count items between two dates over a time period

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?

Data example:

Price.id    Price.timestamp    Price.time_ended
1           2022-18-09         2022-26-09
2           2022-13-09         2022-20-09

The query result i would like to get is:

2022-27-09; 0
2022-26-09; 1
2022-25-09; 1
...
2022-20-09; 2
2022-19-09; 2
2022-18-09; 2
2022-17-09; 1
...
2022-12-09; 0
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I figured it out.

    First I created a date range by using a subquery.

    todays_date = datetime.today() - timedelta(days = 1)
numdays = 360
min_date = todays_date - timedelta(days = numdays)

date_series = db.func.generate_series(min_date , todays_date, timedelta(days=1))
trunc_date = db.func.date_trunc('days', date_series)
subquery = db.session.query(trunc_date.label('day')).subquery()

    Then I used the subquery as input in my original query, and I was finally able to filter on the dates from the subquery.

    query = db.session.query(subquery.c.day, db.func.count(Price.id))
query = query.order_by(subquery.c.day.desc())
query = query.group_by(subquery.c.day)
query = query.filter(Price.timestamp < subquery.c.day)
query = query.filter(Price.time_ended > subquery.c.day)

    Now, query.all() will give you a nice list that counts the prices for each day specified in the date_series.


  2. 0

    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()
    Login or Signup to reply.
  3. 0

    You can use this solution:

    trunc_date.between(Price.timestamp, Price.time_ended)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search