CODEWITHSUNDEEP
regexmongodb
Shashank
Shashank

Reputation: 782

Regex in Mongodb for ISO Date field

How can I pick all the dates with time value as 00:00:00 despite the date value? Regex doesn't work for me.

{
    "_id" : ObjectId("59115a92bbf6401d4455eb21"),
    "name" : "sfdfsdfsf",
    "create_date" : ISODate("2013-05-13T02:34:23.000Z"),
}

something like : 

db.myCollection.find({"create_date": /*T00:00:00.000Z/ })

Upvotes: 0

Views: 3468

Answers (2)

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

From MongoDB version >= 4.4 we can write custom filters using $function operator.

Note: Donot forget to chage the timezone to your requirement. Timezone is not mandatory.

let timeRegex = /.*T00:00:00.000Z$/i;

db.myCollection.find({
  $expr: {
    $function: {
      body: function (createDate, timeRegex) {
        return timeRegex.test(createDate);
      },
      args: [{ $dateToString: { date: "$create_date", timezone: "+0530" } }, timeRegex],
      lang: "js"
    }
  }
});

Upvotes: 0

Ra Ka
Ra Ka

Reputation: 3055

You need to first convert created date into string of time, and if time is 00:00:00:000, then include the document. 

db.test.aggregate([
  // Part 1: Project all fields and add timeCriteria field that contain only time(will be used to match 00:00:00:000 time)
  {
    $project: {
      _id: 1,
      name: "$name",
      create_date: "$create_date",
      timeCriteria: {
        $dateToString: {
          format: "%H:%M:%S:%L",
          date: "$create_date"
        }
      }
    }
  },
  // Part 2: match the time
  {
    $match: {
      timeCriteria: {
        $eq: "00:00:00:000"
      }
    }
  },
  // Part 3: re-project document, to exclude timeCriteria field.
  {
    $project: {
      _id: 1,
      name: "$name",
      create_date: "$create_date"
    }
  }
]);

Upvotes: 2

Related Questions