Mongo query to search between given date range while date is stored as string in db

user_5553202 0
Viewed 375

I have db schema that has string date format("date":"2020-09-01 16:07:45"). I need to search between given date range, I know this is possible if we're using ISO date format but I'm not sure if we can query with date format being string.

I have tried the following query, it doesn't seem to show accurate results.

db.daily_report_latest.find({"date":{$gte: "2021-01-01 00:00:00", $lte:"2021-03-01 00:00:00"}},{"date":1})

Is there any alternative to this? Appreciate your help.

mongodb-query mongodb
3 Answers

You're right, you can't query a date field with a string, but you can just cast it to date type like so:

Mongo Shell:

db.daily_report_latest.find({
    "date": {$gte: ISODate("2021-01-01T00:00:00Z"), $lte: ISODate("2021-03-01T00:00:00Z")}
}, {"date": 1});

For nodejs:

db.daily_report_latest.find({
    "date": {$gte: new Date("2021-01-01 00:00:00"), $lte: new Date("2021-03-01 00:00:00")}
}, {"date": 1});

For any other language just check what the mongo driver date type is and do the same.

Note that the mongo shell isn't able to parse the string input in the format you provided, you should read here about the supported formats and transform your string pre-query like I did. Another thing to consider for the nodejs usecase is timezones, the string will be parsed as the machine current timezone so again you need to adjust to that.

user_107703700

So in the both the solutions, first typecast the date field in DB to date and then compare it with your input date range.

SOLUTION #1: For MongoDB Version >= 4.0 using $toDate.

db.daily_report_latest.find(
    {
        $expr: {
            $and: [
                { $gte: [{ $toDate: "$date" }, new Date("2021-01-01 00:00:00")] },
                { $lte: [{ $toDate: "$date" }, new Date("2021-03-01 00:00:00")] }
            ]
        }
    },
    { "date": 1 }
)

SOLUTION #2: For MongoDb version >= 3.6 using $dateFromString.

db.daily_report_latest.find(
    {
        $expr: {
            $and: [
                { $gte: [{ $dateFromString: { dateString: "$date" }}, new Date("2021-01-01 00:00:00")] },
                { $lte: [{ $dateFromString: { dateString: "$date" }}, new Date("2021-03-01 00:00:00")] }
            ]
        }
    },
    { "date": 1 }
)
user_50704600

You can use $dateFromString feature of aggregation. (Documentation)

pipeline = []
pipeline.append({"$project": {document: "$$ROOT", "new_date" : { "$dateFromString": { "dateString": '$date', "timezone": 'America/New_York' }}}})
pipeline.append({"$match":{"new_date": {"$gte": ISODate("2021-01-01 00:00:00"), "$lte":ISODate("2021-03-01 00:00:00")}}})

data = db.daily_report_latest.aggregate(pipeline=pipeline)
user_65033290
Related