Aggregate or group by via custom time format on timestamp or date?

[jcjp]

How do you aggregate or groupBy a query via a specific format of a timestamp or date? Let's say I want to get the sum of all the income that was generated on yearly, monthly, daily or hourly, etc. basis using the same timestamp model for example I have a column createdAt. I don't see it anywhere in the documentation do you support this type of query of function?

Somehow similar to this #1452 but without hard coding the sql statement or is this the only way? Sorry I came from Prisma and looking some alternatives since this is not supported at the moment by Prisma: prisma/prisma#6653. Feel free to close if you think this is not appropriate.

[Zefty]

I have same question here. Any ideas how this is resolved without the error
"column ... must appear in the GROUP BY clause or be used in an aggregate function"

[acharlop]

I was able to do something similar to what you are asking about:

import {count, sql, sum} from 'drizzle-orm'
import {purchases} from '~/db/schema'

const dateSql = sql`TO_CHAR(${purchases.createAt}, 'YYYY-MM')`

const data = await db
    .select({
        date: dateSql.mapWith(String),
        totalPrice: sum(purchases.netPrice).mapWith(Number),
        purchasesCount: count(),
    })
    .from(purchases)
    .groupBy(dateSql)
    .orderBy(dateSql)