I am writing an aggregation using C# MongoDb driver and want to code
$dateToString: { format: "%d%m%Y", date: "$date", timezone: "+04:30" }
with the timezone parameter for grouping by on date part of the field.
My document is something like this
{
"_id" : ObjectId("5ade8bfc6b941c7726a54f01"),
"event" : "FormSubmit"
"recordLastUpdated" : ISODate("2018-04-24T01:44:28.040Z"),
}
I am trying to get events grouped by date only. The browser will be sending the date from a different timezone. So i need to add the offset at the time of group by.
Right now I am creating a datetime using the parts of the date and adding the offset in minutes. But this does not seem to be intuitive.
var aggregate = _context.PartnerEventTracking.Aggregate()
.Match(x => x.recordLastUpdated >= startDate && x.recordLastUpdated <= endDate)
.Group(x => new
{
x.@event,
date = new DateTime(x.recordLastUpdated.Year, x.recordLastUpdated.Month, x.recordLastUpdated.Day,
x.recordLastUpdated.Hour, x.recordLastUpdated.Minute +4, 0).ToString(datePattern)
}, z => new
{
key = z.Key,
count = z.Sum(a => 1)
})
.Group(x => x.key.@event, y => new
{
@event = y.Key,
eventCount = y.Sum(z => z.count),
dateCount = y.Select(z => new
{
z.count,
z.key.date
}).ToList()
})
.Project(m => new
{
m.dateCount,
eventName = m.@event,
m.eventCount
})
.SortBy(x => x.eventName);
How can I achieve the below code
db.PartnerEventTracking.aggregate([
{
"$match": {
"recordLastUpdated": {
"$gte": ISODate("2020-12-27T18:30:00Z"),
"$lte": ISODate("2021-01-03T18:30:00Z")
}
}
},
{
"$group":
{
"_id": {
"event": "$event",
"date": { "$dateToString": { "format": "%m/%d/%Y", "date": "$recordLastUpdated", timezone: "+05:30" } }
}, "count": { "$sum": 1 }
}
},
{
"$group": {
"_id": "$_id.event",
"eventCount": { "$sum": "$count" }, "dateCount": { "$push": { "count": "$count", "date": "$_id.date" } }
}
},
{ "$project": { "dateCount": "$dateCount", "eventName": "$_id", "eventCount": "$eventCount", "_id": 0 } },
{ "$sort": { "eventName": 1 } }])
I need help with below. The "+5:30" is just an example. It can be any other value also
"date": { "$dateToString": { "format": "%m/%d/%Y", "date": "$recordLastUpdated", timezone: "+05:30" } }