Group by month and year from date field

Question

From Data in the following Table Loan_Amendment:

    ID     |     Amount     | AddDeduct | AmendDate  
-----------+----------------+-----------+------------
 LT0000160 | 1000.000000000 | 2         | 2018-02-08
 LT0000170 | 1500.00000000  | 1         | 2018-02-10
 LN0000577 | 1000.000000000 | 1         | 2018-03-20
 LN0000587 | 2000.000000000 | 2         | 2018-03-20

I tried to select sum Amount field which have the same year and month of AmendDate.

Therefore, I tried select query in SQLALchemy as the following like this:

AmendObj = db.session.query(func.sum(Loan_Amendment.Amount).label('Amount'),
                            Loan_Amendment.AmendDate).\
    group_by(Loan_Amendment.AmendDate).\
    first()

However, from the above query I have tried I can only group by exact same date (year, month, and day) of AmendDate.

Updated:

My model look like below:

class Loan_Amendment(Object):
    ID              =   db.Column(db.String(13), primary_key=True)
    AddDeduct       =   db.Column(db.String(1))
    Amount          =   db.Column(db.Numeric(25, 9))
    AmendDate       =   db.Column(db.String(20))

Currently, I' using Flask-SQLAlchemy==2.0 and SQLAlchemy==0.9.9 and DB type is postgresql.

Which way I can select sum and group by only month and year from the AmendDate?

Answer 1

Use the date_trunc() function to truncate your dates to month precision:

month = func.date_trunc('month', Loan_Amendment.AmendDate)

AmendObj = db.session.query(func.sum(Loan_Amendment.Amount).label('Amount'),
                            month).\
    group_by(month).\
    first()
    # Did you mean all()?

Another option that's less DB dependent is to use EXTRACT():

from sqlalchemy import extract

AmendObj = db.session.query(func.sum(Loan_Amendment.Amount).label('Amount'),
                            extract('year', Loan_Amendment.AmendDate),
                            extract('month', Loan_Amendment.AmendDate)).\
    group_by(extract('year', Loan_Amendment.AmendDate),
             extract('month', Loan_Amendment.AmendDate)).\
    first()

---

Since your date columns are actually text, you need a cast first. If you're using a version of SQLAlchemy that has the ColumnElement.cast() shortcut, 1.0.7 or above, then just

func.date_trunc('month', Loan_Amendment.AmendDate.cast(Date))

but if not, then

from sqlalchemy import cast

func.date_trunc('month', cast(Loan_Amendment.AmendDate, Date))

Naturally the same applies if using EXTRACT.

---

A hacky solution given your model and data would be to simply take the substring that includes the year and month part and group by it:

month = func.substr(Loan_Amendment.AmendDate, 1, 7)