如何在MongoDB中查询日期字符串格式的日期范围？Node.js/Mongoose求助

Hey there! I’ve run into this exact issue before, so let’s break down why your query isn’t returning the expected results and how to fix it.

The Core Problem #

Your createdDate is stored as a string (like "Sat Dec 28 2019"), but MongoDB’s $gte/$lt operators compare strings lexicographically (by character order), not chronologically. For example, "Sun Dec 29 2019" would come before "Sat Dec 28 2019" in string comparison—total chaos for date ranges!

Solution 1: Convert Dates to MongoDB Date Type (Recommended) #

The best long-term fix is to update your collection to store createdDate as proper MongoDB Date objects. This lets you use date operators efficiently and leverage indexes for faster queries.

Step 1: Batch-convert existing string dates

Run this script (either in Node.js with Mongoose or directly in the MongoDB shell) to convert all string-formatted createdDate fields:

Node.js/Mongoose Script:

const Order = require('./models/Order'); // Import your Order model

async function convertDateFields() {
  try {
    // Find all orders with string-type createdDate
    const orders = await Order.find({ createdDate: { $type: 'string' } });
    
    for (const order of orders) {
      // Convert the string to a Date object (JS can parse this format natively)
      order.createdDate = new Date(order.createdDate);
      await order.save();
    }
    
    console.log('Successfully converted all date strings to Date objects!');
  } catch (err) {
    console.error('Error converting dates:', err);
  }
}

convertDateFields();

MongoDB Shell Command:

db.orders.updateMany(
  { createdDate: { $type: "string" } },
  [ { $set: { createdDate: { $toDate: "$createdDate" } } } ]
)

Step 2: Update your query to use Date objects

Now modify your aggregation $match stage to convert the incoming request dates to Date objects too:

$match: {
  "orders.kitchen": mongoose.Types.ObjectId(req.body.kitchenId),
  "orders.createdDate": {
    $gte: new Date(req.body.startDate),
    $lt: new Date(req.body.endDate)
  }
}

Solution 2: On-the-Fly Date Conversion (Temporary Fix) #

If you can’t modify the database right now (e.g., production constraints), you can convert the string dates to Date objects during the aggregation pipeline. Note: This won’t use indexes, so it’s slow for large datasets.

Use $addFields to create a converted date field, then $match against it:

const aggregationPipeline = [
  {
    $addFields: {
      "orders.convertedCreatedDate": { $toDate: "$orders.createdDate" }
    }
  },
  {
    $match: {
      "orders.kitchen": mongoose.Types.ObjectId(req.body.kitchenId),
      "orders.convertedCreatedDate": {
        $gte: new Date(req.body.startDate),
        $lt: new Date(req.body.endDate)
      }
    }
  }
];

const filteredOrders = await Order.aggregate(aggregationPipeline);

Quick Note on Input Date Format #

Make sure req.body.startDate and req.body.endDate are in a format that JavaScript’s new Date() can parse (e.g., "2019-12-28" or "Sat Dec 28 2019"). If they’re in a weird format, use a library like date-fns or moment.js to parse them correctly first.

内容的提问来源于stack exchange，提问作者Abdur Rehman