Question 180512 in Mongodb Question posted in
The official documentation can be found here.

How to get week wise data based on UTC date time in mongodb aggregate

I have used mongoose nodejs to get the week wise data. I have data stored in UTC format, the sample data will be like below

Steps: {
     "quantity": 25,
     "duration": 100,
     "endTime": "2023-05-18 01:25:53 +0000",
     "startTime": "2023-05-18 01:25:52 +0000"
},
leadId: 10

From the set of data I am trying to get the week wise data while user pass timezone as IST, I need to get the data. For example, if user request data from 01-05-2023 to 31-05-2023 I need to query based on startTime from 30-04-2023 18:30:00 to 30-05-2023 18:29:59 (IST is +05:30).

I am trying achieve with following aggregate query

activities.aggregate([
   { '$addFields': { date: { '$toDate': '$Steps.startTime' } } },
   {
      '$match': {
         Steps: { '$ne': null },
         date: { '$gte': "2023 - 04 - 30T18: 30: 00.000Z", '$lte': "2023 - 05 - 29T18: 29: 59.000Z" }, leadId: 38047
      }
   },
   {
      '$group': {
         _id: { startTime: '$Steps.startTime', endTime: '$Steps.endTime' },
         doc: { '$last': '$$ROOT' }
      }
   },
   {
      '$group': {
         _id: { '$week': '$doc.date' },
         quantity: { '$sum': { '$convert': { input: '$doc.Steps.quantity', to: 'double', onNull: 0, onError: '' } } },
         duration: { '$sum': { '$convert': { input: '$doc.Steps.duration', to: 'double', onNull: 0, onError: '' } } }
      }
   },
   { '$group': { _id: null, docs: { '$push': '$$ROOT' } } },
   {
      '$project': {
         docs: {
            '$map': {
               input: { '$range': [{ '$week': "2023 - 04 - 30T18: 30: 00.000Z" }, { '$week': "2023 - 06 - 04T00: 00: 00.000Z" }] },
               as: 'weekNumber',
               in: {
                  '$let': {
                     vars: { index: { '$indexOfArray': ['$docs._id', '$$weekNumber'] } },
                     in: {
                        '$cond': {
                           if: { '$eq': ['$$index', -1] },
                           then: { _id: '$$weekNumber', quantity: 0, duration: 0 },
                           else: { '$arrayElemAt': ['$docs', '$$index'] }
                        }
                     }
                  }
               }
            }
         }
      }
   }, 
   { '$unwind': '$docs' },
   { '$replaceRoot': { newRoot: '$docs' } },
   {
      '$project': {
         _id: '$_id',
         quantity: { '$ifNull': ['$quantity', 0] },
         duration: { '$ifNull': ['$duration', 0] },
         weekOfMonth: 1
      }
   }
])

Here I am expecting query to run for current week from 27-05-2023 18:30:00 to 03-05-2023 18:29:59. But query is running for the current week from 28-05-2023 00:00:00 to 03-05-2023 11:59:59. Not converted datetime inside week range, it took data basis only. But I am expecting this to run based on date time with data conversion.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have edited the query like below

        
    {
      $match: {
        "Steps": { $ne: null },
         "Steps.startTime": {
            $gte: new Date("2023-05-01T00:00:00+05:30"),
            $lt: new Date("2023-05-31T00:00:00+05:30")
         },
         leadId: 36
      }
   },


   { $group: {
    _id: { startTime: '$Workout.startTime', endTime: '$Workout.endTime' }, doc: {'$last': '$$ROOT'}
   } },

    {
      "$group": {
        _id: {
          $dateTrunc: {
             date: "$doc.Steps.startTime",
             unit: "week",
             timezone: "+05:30",
             startOfWeek: "sunday"
          }
       },

        docs: { $addToSet: '$doc' }
        
      }
    },

    {
      $unwind: '$docs'
    },
    {
      $project: {
        _id: '$_id',
        quantity: {
          $toDouble: {
            $ifNull: ['$docs.Steps.quantity', 0]
          }
        },
        duration: {
          $toDouble: {
            $ifNull: ['$docs.Steps.duration', 0]
          }
        }
      }
    },

    {
      $group: {
        _id: '$_id',
        quantity: { $sum: '$quantity' },
        duration: { $sum: '$duration' }
      }
    },


    {
      $sort: { '_id': 1 }
    },
    

    {
      $addFields: {
        startDate: new Date('2023-04-30T18:30:00.000Z'),
        endDate: new Date('2023-05-30T18:30:00.000Z'),
        step: 7 * 24 * 60 * 60 * 1000, // Step size in milliseconds (1 week)
        bounds: 'full'
      }
    },
    
    {
      $addFields: {
        rangeStart: {
          $dateToParts: {
            date: '$startDate',
            timezone: '+00:00'
          }
        },
        rangeEnd: {
          $dateToParts: {
            date: '$endDate',
            timezone: '+00:00'
          }
        }
      }
    },

    {
      $addFields: {
        range: {
          $range: [
            0,
            {
              $ceil: {
                $divide: [
                  {
                    $subtract: [
                      {
                        $multiply: ['$rangeEnd.year', 12],
                      },
                      {
                        $add: [
                          {
                            $multiply: ['$rangeStart.year', 12],
                          },
                          {
                            $subtract: [
                              '$rangeEnd.month',
                              '$rangeStart.month',
                            ],
                          },
                        ],
                      },
                    ],
                  },
                  12,
                ],
              },
            },
            1,
          ],
        },
      },
    }
  ])```

but range have empty array even if I the following sample document in my db


Steps: {
     "quantity": 25,
     "duration": 100,
     "endTime": "2023-05-18T01:25:53.000+0000",
     "startTime": "2023-05-18T01:25:52.000+0000"
},
leadId: 10

  2. 0

    You should never store date values as stings, it’s design flaw. Store always proper Date objects.

    With Date values, the $match could be simply

       {
      $match: {
         "Steps.startTime": {
            $gte: ISODate("2023-05-01T00:00:00+05:30"),
            $lt: ISODate("2023-05-31T00:00:00+05:30")
         },
         leadId: 38047
      }
   }

    You can group directly by week:

       {
      $group: {
         _id: {
            $dateTrunc: {
               date: "$Steps.startTime",
               unit: "week",
               timezone: "+05:30",
               startOfWeek: "monday"
            }
         },
         quantity: { '$sum': '$Steps.quantity' },
         duration: { '$sum': '$Steps.duration' }
      }
   }

    And to fill missing values with 0, I would suggest $densify and $fill

       {
      $densify: {
         field: "Steps.startTime",
         range: { step: 1, unit: "week", bounds: "full" }
      }
   },
   { $fill: { output: { quantity: 0, duration: 0 } } },

    With only one input document and without expected result, it is a bit difficult for me to provide the full aggregation pipeline. There would be too many options.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search