How to delete docs in mongodb with a date condition in a string field?

Question

I wanted to drop documents which is less than 60 days. pls see the sample doc

{
   "_id" : ObjectId("5c4abd3e29cb6d223043c036"),
   "UniqueStoryID" : "4018-1548377034",
   "LastUpdated" : 148401982,
   "StartRunTime" : "01/25/2019 00:43:54"
}

The date field StartRunTime is string.

How i can query with $lte ?

yoour objectid will have a timestamp in it and you can filter by that. otherwise, transform your data? For example, the above ObjectId is dated as : 2019-01-25 07:39:42 — pandaadb, Mar 27 '19 at 12:21
You can read all about it here: https://stackoverflow.com/questions/8749971/can-i-query-mongodb-objectid-by-date — pandaadb, Mar 27 '19 at 12:36

score 0 · Answer 1 · answered Mar 27 '19 at 12:22

0

You can let mongodb do that for you with TTL indexes. Or store StartRunTime as date instead of string. Than you can query with $lte over it.

answered Mar 27 '19 at 12:22

Vladyslav Usenko

2,296
8
18

score 0 · Answer 2 · answered Mar 27 '19 at 12:26

Store the date in a datetime object and not string. If you store date in string it will only do a lexicographical comparison and trust me you don't want that. Always store as a datetime object.

Coming back to the question(given that date is in datetime object)

old_date = current_date - 60days
<Collection>.delete_many({StartRunTime: {'$lt': old_date}})

Or you can use MongoDB TTL Indexes which will automatically delete a document after 60days(you will have to configure that) and for that you'll not have to store StartRunTime as a datetime object, you can keep it's value anything you want.

score 0 · Answer 3 · answered Mar 28 '19 at 15:04

You can accomplish this using the $where operator (which accepts a JS function):

db.collection.deleteMany({
    $where: function () {
        var date = new Date(this.StartRunTime);
        return Math.round((Date.now() - date.valueOf()) / (1000 * 60 * 60 * 24)) > 60;
    }
})

Notes:

We divide the time difference by 1000 * 60 * 60 * 24 because it is milliseconds and you want to calculate difference in days.
If you do not pass a timezone in the date string to new Date(...), it will be parsed in the machine's local time zone. If you want it to be parsed as GMT you can concat GMT at the end of the date string, like so: new Date(this.StartRunTime + " GMT").

IMPORTANT NOTE: The $where operator cannot make use of indexes so it will probably lead to bad performance. I am only suggesting this solution because it answers your question. I think a better solution would be to store the date as a proper Date type.

How to delete docs in mongodb with a date condition in a string field?

3 Answers3