I use the date-fns
library to work with dates. the code provided their methods.
prisma: 5.22.0
@prisma/client: 5.22.0
async getAllOnYear() {
const date = new Date()
const startYear = startOfYear(date)
const endYear = endOfYear(date)
console.log('Start Year:', startYear)
console.log('End Year:', endYear)
const monthlyStatisticsRaw = await this.prisma.productRelease.aggregateRaw({
pipeline: [
{
$match: {
marking: { $ne: EnumProductReleaseMarking.deleted },
created_at: {
$gte: startYear,
$lte: endYear
}
}
},
{
$group: {
_id: {
month: { $month: '$created_at' }
},
totalAmount: { $sum: '$total_amount' },
totalSale: { $sum: '$total_sale' },
totalSwap: { $sum: '$total_swap' },
totalBonus: { $sum: '$total_bonus' },
count: { $sum: 1 }
}
}
]
})
console.log('raw', monthlyStatisticsRaw)
}
console:
Start Year: 2023-12-31T19:00:00.000Z
End Year: 2024-12-31T18:59:59.999Z
raw []
prisma model:
model ProductRelease {
id String @id @default(auto()) @map("_id") @db.ObjectId
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
user User? @relation(fields: [userId], references: [id])
userId String? @map("user_id") @db.ObjectId
tag String @unique
status EnumProductReleaseStatus
marking EnumProductReleaseMarking @default(null)
totalAmount Float @map("total_amount")
totalSale Int @map("total_sale")
totalSwap Int @map("total_swap")
totalBonus Int @map("total_bonus")
@@map("product_release")
}
I tried different ways to convert the date to toISOString() and so on. I tried changing created_at to createdAt, but with no result.
if you delete created_at, everything works out:
Start Year: 2023-12-31T19:00:00.000Z
End Year: 2024-12-31T18:59:59.999Z
raw [
{
_id: { month: 11 },
totalAmount: 345664,
totalSale: 336,
totalSwap: 50,
totalBonus: 106,
count: 10
}
]
to complete the picture, I also throw off the data on request:
await this.prisma.productRelease.aggregateRaw({
pipeline: [
{
$project: { created_at: 1, marking: true }
},
{
$sort: { created_at: 1 }
}
]
})
result:
{
_id: { '$oid': '673c955783744a1eaf4e52ee' },
created_at: { '$date': '2024-11-19T13:40:38.298Z' },
marking: 'accounting'
},
{
_id: { '$oid': '673e5083a9570d92a284c2b7' },
created_at: { '$date': '2024-11-20T21:11:30.742Z' },
marking: 'null'
},
{
_id: { '$oid': '673f9542f9388c92967b84a8' },
created_at: { '$date': '2024-11-21T20:17:05.329Z' },
marking: 'null'
},
I was able to first output the working method to $expr
and then found the method for the usual $match
:
await this.prisma.productRelease.aggregateRaw({
pipeline: [
{
$match: {
marking: { $ne: EnumProductReleaseMarking.deleted },
created_at: {
$gte: { $date: startYear },
$lte: { $date: endYear }
}
}
},
{
$group: {
_id: {
month: { $month: '$created_at' }
},
totalAmount: { $sum: '$total_amount' },
totalSale: { $sum: '$total_sale' },
totalSwap: { $sum: '$total_swap' },
totalBonus: { $sum: '$total_bonus' },
count: { $sum: 1 }
}
}
]
})