CODEWITHSUNDEEP
mongodbmongodb-query
Niyojita
Niyojita

Reputation: 31

MongoDB - How to get last month records

How can I fetch last month's records in MongoDB?

Following is the MYSQL query used to do the same:

SELECT * FROM table
WHERE YEAR(createdon) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(createdon) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

Upvotes: 2

Views: 7380

Answers (2)

Yong Shun
Yong Shun

Reputation: 51240

Solution 1: $where operator for db.collection.find()

Supported version: Before 4.4

You can use $where which is supported with Javascript function().

db.collection.find({
  $where: function() { 
    var currentDate = new Date(); 
    var lastMonthDate = new Date(currentDate.setMonth(currentDate.getMonth() - 1)); 

    return this.createdOn.getFullYear() === lastMonthDate.getFullYear() 
      && this.createdOn.getMonth() === lastMonthDate.getMonth(); 
  }
})

Note: Wrap the function with the quotes in Mongo Playground is due to reported Github issue.

Sample Solution 1 on Mongo Playground

Solution 2: With $dateAdd for db.collection.aggregate()

Supported version: 5.0

With $dateAdd is introduced in version 5.0, you can perform the query as below:

  1. $match:

    • With $match pipeline, using $expr query expression to include the aggregation operation.

    • $and operator to ensure both expressions are fulfilled.

    • Using $dateAdd operator to add month (unit) by -1 (amount) to get previous month date.

    • $month and $year are for getting the month and year value from date respectively.

    • And perform $eq operator to check both $month and $year from createdOn are matched with both $month and $year from previous month date respectively.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            "$eq": [
              {
                $month: "$createdOn"
              },
              {
                $month: {
                  $dateAdd: {
                    startDate: new Date(),
                    unit: "month",
                    amount: -1
                  }
                }
              }
            ]
          },
          {
            "$eq": [
              {
                $year: "$createdOn"
              },
              {
                $year: {
                  $dateAdd: {
                    startDate: new Date(),
                    unit: "month",
                    amount: -1
                  }
                }
              }
            ]
          }
        ]
      }
    }
  }
])

Sample Solution 2 on Mongo Playground

Solution 3: With $dateFromParts for db.collection.aggregate()

Supported version: From 3.6

You may also apply with $dateFromParts which is suggested by this Jira comment.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            "$eq": [
              {
                $month: "$createdOn"
              },
              {
                $month: {
                  $let: {
                    "vars": {
                      "todayDate": new Date()
                    },
                    "in": {
                      $dateFromParts: {
                        year: {
                          $year: "$$todayDate"
                        },
                        month: {
                          $subtract: [
                            {
                              $month: "$$todayDate"
                            },
                            1
                          ]
                        },
                        day: {
                          $dayOfMonth: "$$todayDate"
                        }
                      }
                    }
                  }
                }
              }
            ]
          },
          {
            "$eq": [
              {
                $year: "$createdOn"
              },
              {
                $year: {
                  $let: {
                    "vars": {
                      "todayDate": new Date()
                    },
                    "in": {
                      $dateFromParts: {
                        year: {
                          $year: "$$todayDate"
                        },
                        month: {
                          $subtract: [
                            {
                              $month: "$$todayDate"
                            },
                            1
                          ]
                        },
                        day: {
                          $dayOfMonth: "$$todayDate"
                        }
                      }
                    }
                  }
                }
              }
            ]
          }
        ]
      }
    }
  }
])

Sample Solution 3 on Mongo Playground

Upvotes: 2

Victor.parracant
Victor.parracant

Reputation: 11

You can select the records between two dates using the $gte and $lte options. Example:

db.getCollection('your-collection-name').find({
  {createdAt:{$gte:ISODate(“2020-03-01”),$lt:ISODate(“2021-03-31”)}}
})

more information about these operators:

Upvotes: 1

Related Questions