2

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" } }
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Does [this answer](https://stackoverflow.com/a/48535842/634824) address your issue? Or are you asking specifically about the C# code? – Matt Johnson-Pint Jan 04 '21 at 20:48
  • @MattJohnson-Pint I need the code in C# . I am unable to figure out how to add timezone param to $dateToString in C# or something equivalent. – rakesh raman Jan 05 '21 at 05:17

0 Answers0