SQLAlchemy group_by date and aggregate on count, how to fill in missing dates

Question

I have a sqlalchemy query that looks like this.

First I group my Pomo model by time stamp, then I group by the date that the Pomo was created.

db.session.query(Pomo.timestamp, sa.func.count(Pomo.id))\
               .group_by(sa.func.date(Pomo.timestamp)).all()

This returns data that looks like this

[(datetime.datetime(2018, 3, 2, 0, 0), 1),
(datetime.datetime(2018, 3, 7, 0, 0), 1),
(datetime.datetime(2018, 3, 8, 0, 0), 6)]

How can I fill in the dates so that the output is something like

[(datetime.datetime(2018, 3, 2, 0, 0), 1),
(datetime.datetime(2018, 3, 3, 0, 0), 0),
(datetime.datetime(2018, 3, 4, 0, 0), 0),
(datetime.datetime(2018, 3, 5, 0, 0), 0),
(datetime.datetime(2018, 3, 6, 0, 0), 0),
(datetime.datetime(2018, 3, 7, 0, 0), 1),
(datetime.datetime(2018, 3, 8, 0, 0), 6)]

Have you had a look at this: https://stackoverflow.com/questions/346132/postgres-how-to-return-rows-with-0-count-for-missing-data? — Ilja Everilä, Mar 12 '18 at 18:18

score 9 · Answer 1 · answered Mar 12 '18 at 18:31

Use generate_series() to generate all dates in the desired range and left join the data, coalescing missing values to 0:

In [24]: series = db.session.query(
    ...:         db.func.generate_series(db.func.min(Pomo.timestamp),
    ...:                                 db.func.max(Pomo.timestamp),
    ...:                                 timedelta(days=1)).label('ts')).\
    ...:     subquery()
    ...:                             

In [25]: values = db.session.query(Pomo.timestamp,
    ...:                           db.func.count(Pomo.id).label('cnt')).\
    ...:     group_by(Pomo.timestamp).\
    ...:     subquery()

In [26]: db.session.query(series.c.ts,
    ...:                  db.func.coalesce(values.c.cnt, 0)).\
    ...:     outerjoin(values, values.c.timestamp == series.c.ts).\
    ...:     order_by(series.c.ts).\
    ...:     all()
    ...: 
Out[26]: 
[(datetime.datetime(2018, 3, 2, 0, 0), 1),
 (datetime.datetime(2018, 3, 3, 0, 0), 0),
 (datetime.datetime(2018, 3, 4, 0, 0), 0),
 (datetime.datetime(2018, 3, 5, 0, 0), 0),
 (datetime.datetime(2018, 3, 6, 0, 0), 0),
 (datetime.datetime(2018, 3, 7, 0, 0), 1),
 (datetime.datetime(2018, 3, 8, 0, 0), 6)]

SQLAlchemy group_by date and aggregate on count, how to fill in missing dates

1 Answers1