如何修改MongoDB日期格式？查询指定日期数据失败求助

Why Your Current Query Isn't Working #

Your dcre field stores a full ISO 8601 timestamp (e.g., "2018-05-29T14:04:54.324Z"), but you're trying to match it against a date-only string like "2018-05-29". Since these values aren't identical, no documents are returned. Here are three effective ways to fix this:

Solution 1: Query Using a Date Range

The most efficient approach is to target all timestamps that fall within the start and end of your desired date. This leverages any existing indexes on dcre and avoids modifying your data:

async find(dateStr) {
  // Convert input date string (supports "2018-05-29" or "2018/05/29") to a Date object
  const targetDate = new Date(dateStr);
  
  // Calculate start of the target date (UTC 00:00:00)
  const startOfDay = new Date(Date.UTC(
    targetDate.getUTCFullYear(),
    targetDate.getUTCMonth(),
    targetDate.getUTCDate()
  ));
  
  // Calculate start of the next day to define the upper bound
  const startOfNextDay = new Date(startOfDay);
  startOfNextDay.setUTCDate(startOfDay.getUTCDate() + 1);

  const res = await this.db.collection('table').find({
    dcre: {
      $gte: startOfDay.toISOString(),
      $lt: startOfNextDay.toISOString()
    }
  }).limit(500).toArray();
  
  return res;
}

Using UTC ensures consistency regardless of your server's local timezone.

Solution 2: Use Aggregation to Format Dates On-the-Fly

If you need to match against a specific date format (like "2018/05/29"), use MongoDB's aggregation framework to convert dcre to your desired format before matching:

async find(dateStr) {
  // Convert input date to the format you want to match
  const targetFormattedDate = new Date(dateStr)
    .toISOString()
    .split('T')[0]
    .replace(/-/g, '/'); // Gets "2018/05/29"

  const res = await this.db.collection('table').aggregate([
    {
      $addFields: {
        // Convert dcre string to Date, then format it to "YYYY/MM/DD"
        dcreFormatted: { 
          $dateToString: { 
            format: "%Y/%m/%d", 
            date: { $toDate: "$dcre" } 
          } 
        }
      }
    },
    { $match: { dcreFormatted: targetFormattedDate } },
    { $limit: 500 }
  ]).toArray();
  
  return res;
}

Note: $toDate is required to convert the string dcre to a Date object before applying $dateToString.

Solution 3: Precompute a Date-Only Field (Best for Frequent Queries)

If you regularly query by the date part of dcre, adding a dedicated field will simplify your queries and improve performance. First, update all existing documents to add the new field:

// Run this once to populate the dcre_date field
await this.db.collection('table').updateMany(
  {},
  [
    {
      $set: {
        dcre_date: { 
          $dateToString: { 
            format: "%Y-%m-%d", 
            date: { $toDate: "$dcre" } 
          } 
        }
      }
    }
  ]
);

Then your query becomes straightforward:

async find(date) {
  // Ensure input date matches the format of dcre_date
  const formattedDate = date.replace(/\//g, '-'); // Adjust if using slashes
  const res = await this.db.collection('table')
    .find({ dcre_date: formattedDate })
    .limit(500)
    .toArray();
  
  return res;
}

Add an index on dcre_date for faster query performance:

await this.db.collection('table').createIndex({ dcre_date: 1 });

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