TypeORM group by based on date only, ignoring time

Question

I want to group my data based on only the createdAt date part ignoring the time, but i can't figure out how to.

Here's my code, which groups the data by date as well as the time component of createdAt:

 let res = await this.transactionRepository
      .createQueryBuilder('transaction')
      .select(["SUM(transaction.price) AS priceSum", "transaction.createdAt"])
      .where("transaction.businessId = :businessId", {businessId: businessId}) 
      .groupBy('transaction.createdAt')
      .getRawMany();

Output:

[
    { priceSum: 20, date: 2024-08-09 12:00:00},
    { priceSum: 10, date: 2024-08-09 14:20:00},

    { priceSum: 50, date: 2024-08-20 05:20:00}
]

Expected:

[
    { priceSum: 40, date: 2024-08-09},

    { priceSum: 50, date: 2024-08-20}
]

Answer 1

You can use DATE() to extract the date from the dateTime of createdAt

 let res = await this.transactionRepository
      .createQueryBuilder('transaction')
      .select(["SUM(transaction.price) AS priceSum", "DATE(transaction.createdAt)"])
      .where("transaction.businessId = :businessId", {businessId: businessId}) 
      .groupBy('DATE(transaction.createdAt)')
      .getRawMany();