Retrieve data from MongoDB that is within the past week in ISO string format

Question

Retrieve data from MongoDB that is within the past week in ISO string format

My goal is to retrieve data from MongoDB that is exactly a week old.

The specific field I am focusing on is -

{
 _id:821398723913,
 closed_at:"2020-06-10T01:43:59-04:00"
}

I am looking to fetch all objects where the 'closed_at' field falls within a week's time frame. Despite searching online, none of the suggested solutions seem to be effective.

Edit:

Here are some attempts I have made so far -

db.Orders.aggregate([{
            $project: {
                date: {
                    $dateFromString: {
                        dateString: '$date'
                    }
                }
            }
        }, {
            $match: {
                "closed_at": {
                    $lt: lastDayWeek,
                    $gt: firstDayWeek
                }
            }
        }]);

and

Orders.find({'closed_at':  {
                $gte: new Date((new Date().getTime() - (15 * 24 * 60 * 60 * 1000)))
            }, 'cancelled_at': null}).sort({_id: 1});

I require the data to be fetched from Monday to Monday and I have been attempting to use $isoWeek without success so far

javascript mongodb mongoose

Answer 1

Answer №1

Here is a solution you can try out:

db.Orders.aggregate([
  {
    "$addFields": {
      "closed_at_iso": {
        "$toDate": "$closed_at"
      }
    }
  },
  {
    $match: {
      "closed_at_iso": {
        $gte: new Date(new Date() - 7 * 24 * 60 * 60 * 1000)
      }
    }
  }
])

The challenge here lies in the fact that the date field is stored as a string, so we need to convert it to an ISODate format first before using the $match operator.

Edit:

If you want to retrieve data between last week's Monday and this Monday, you can use the following query:

var start = new Date(2020, 5, 1), // date of last Monday
    end = new Date(2020, 5, 8);   // date of this Monday

db.collection.aggregate([
  {
    "$addFields": {
      "closed_at_iso": {
        "$toDate": "$closed_at"
      }
    }
  },
  {
    $match: {
      "closed_at_iso": {
        $gte: start,
        $lte: end
      }
    }
  }
])

If you prefer a dynamic approach to get the dates for last Monday and this Monday, you can use the function below:

function getMonday(d) {
  d = new Date(d);
  var day = d.getDay(), diff = d.getDate() - day + (day == 0 ? -6 : 1);
  return new Date(d.setDate(diff));
}       

var start = getMonday(new Date().setDate(new Date().getDate() - 7))
var end = getMonday(new Date())

console.log(start)
console.log(end)

Answer 2

Here is a solution you can try out:

db.Orders.aggregate([
  {
    "$addFields": {
      "closed_at_iso": {
        "$toDate": "$closed_at"
      }
    }
  },
  {
    $match: {
      "closed_at_iso": {
        $gte: new Date(new Date() - 7 * 24 * 60 * 60 * 1000)
      }
    }
  }
])

The challenge here lies in the fact that the date field is stored as a string, so we need to convert it to an ISODate format first before using the $match operator.

Edit:

If you want to retrieve data between last week's Monday and this Monday, you can use the following query:

var start = new Date(2020, 5, 1), // date of last Monday
    end = new Date(2020, 5, 8);   // date of this Monday

db.collection.aggregate([
  {
    "$addFields": {
      "closed_at_iso": {
        "$toDate": "$closed_at"
      }
    }
  },
  {
    $match: {
      "closed_at_iso": {
        $gte: start,
        $lte: end
      }
    }
  }
])

If you prefer a dynamic approach to get the dates for last Monday and this Monday, you can use the function below:

function getMonday(d) {
  d = new Date(d);
  var day = d.getDay(), diff = d.getDate() - day + (day == 0 ? -6 : 1);
  return new Date(d.setDate(diff));
}       

var start = getMonday(new Date().setDate(new Date().getDate() - 7))
var end = getMonday(new Date())

console.log(start)
console.log(end)

Answer 3

Answer №2

Successfully resolved the issue with the help of palash's solution

let oneWeekAgo = new Date(new Date().getTime() - 60 * 60 * 24 * 7 * 1000)
            , dayOfWeek = oneWeekAgo.getDay()
            , daysToMonday = oneWeekAgo.getDate() - dayOfWeek + (dayOfWeek === 0 ? -6 : 1)
            , previousMonday = new Date(oneWeekAgo.setDate(daysToMonday))
            , previousSunday = new Date(oneWeekAgo.setDate(daysToMonday + 6));


        const orders = await Orders.aggregate([
            {
                '$addFields': {
                    'created_at_iso': {
                        '$toDate': '$created_at'
                    }
                }
            },
            {
                $match: {
                    'created_at_iso': {
                        $gte: previousMonday,
                        $lte: previousSunday
                    }
                }
            }
        ]);

Answer 4

Successfully resolved the issue with the help of palash's solution

let oneWeekAgo = new Date(new Date().getTime() - 60 * 60 * 24 * 7 * 1000)
            , dayOfWeek = oneWeekAgo.getDay()
            , daysToMonday = oneWeekAgo.getDate() - dayOfWeek + (dayOfWeek === 0 ? -6 : 1)
            , previousMonday = new Date(oneWeekAgo.setDate(daysToMonday))
            , previousSunday = new Date(oneWeekAgo.setDate(daysToMonday + 6));


        const orders = await Orders.aggregate([
            {
                '$addFields': {
                    'created_at_iso': {
                        '$toDate': '$created_at'
                    }
                }
            },
            {
                $match: {
                    'created_at_iso': {
                        $gte: previousMonday,
                        $lte: previousSunday
                    }
                }
            }
        ]);

Retrieve data from MongoDB that is within the past week in ISO string format

Answer №1

Answer №2

Similar questions

Using Crawler4j, Jsoup, and JavaScript to retrieve modified attribute values

Trigger next animation after the completion of current animation using Jquery animate callback

Creating a unique theme export from a custom UI library with Material-UI

Leveraging JQuery for activating an event with Bootstrap Scrollspy

Animating a dotted border path in SVG for a progress bar effect

A distinctive noise is heard when hovering over multiple instances of a div

The absence of HttpOnly Secure Cookies being transmitted

How can I pass props from a custom _app.js file to the <Navbar> component in Next.js?

Removing Click event upon button click - Implementing Vue and Vuetify

Is it possible to update the value of an element using JavaScript?

Issue: React child components cannot be objects (received: object with keys)

Updating an array element in MongoDB by setting it to a value from another array

Every Dynamic Post automatically defaults to the initial object

Can someone please explain how to include a custom icon on Select component in Mantine without using an image from Tabler Icons library?

What is the best way to integrate server-side Joi validation with redux-form for seamless functionality?

Obtain the parameter of a parent function that runs asynchronously

Is it possible for me to transform this code into a useful helper function?

Obtain the model value using Yii jQuery and then proceed to submit the form

Is there a pure JavaScript solution to replace jQuery's .prev() function?

Is there a way to replicate input data from one website onto a totally separate platform?