How to Perform Group By with Date Extraction in Prisma

[Chandan-Kalita]

Problem

Performing a group by operation in Prisma that involves date extraction from a timestamp column ("createdAt") in PostgreSQL is currently challenging. The goal is to sum a specific column ("totalFare") grouped by the month extracted from the "createdAt" timestamp.

Suggested solution

I would like to be able to write a query in Prisma that performs a group by operation based on the month extracted from a timestamp column ("createdAt") and then calculates the sum of a specific column ("totalFare") for each month.

Alternatives

To achieve the desired functionality, a workaround involves using a raw SQL query alongside Prisma's capabilities. This allows incorporating the EXTRACT function for date extraction within the GROUP BY clause.

Additional context

Currently, Prisma's groupBy method does not directly support the EXTRACT function for date extraction within the groupBy clause. Utilizing raw SQL queries introduces the risk of SQL injection, so caution must be exercised when implementing this approach. The ability to handle date extraction within Prisma's groupBy method would streamline query operations and enhance compatibility with PostgreSQL-specific functionalities.