CODEWITHSUNDEEP
mongodbmongodb-query
Sravan
Sravan

Reputation: 2019

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

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.

Upvotes: 5

Views: 6121

Answers (4)

Jason
Jason

Reputation: 1028

I came across this issue and found a very elegant solution, without having to fuss with date format object types and simply keeping them as strings. Per Mongo documentation, "By default, MongoDB uses the simple binary comparison to compare strings." This means that date strings can be compared correctly with Mongo's $gte and $lte, but they have to be the exact same length for this to work, and the best way to do this is to store the date in ISO string format, that includes T and Z, something like "2023-01-22T05:00:00.000Z".

You can do this by using javascript's plain new Date().toISOString() method, or moment's moment().toISOString(). The reason why the author's code did not work is because the date string is not formatted the same as the stored value. "2021-01-01 00:00:00" is missing T, and Z, etc. The beauty of the ISO string date is that it is inherently comparable to other ISO string dates, because it is ordered logically from year to milliseconds, and same length and syntax ensures it works every time. If you think about it, it's comparing every digit, from left to right, and whichever has higher digit first wins. (i.e. "9" > "0" == true) It doesn't create a situation where a space is being compared to 'T', etc. So the author's code would look something like:

const startDate = new Date("2021-01-01 00:00:00").toISOString();
const endDate = new Date("2021-03-01 00:00:00").toISOString();

db.daily_report_latest.find({"date":{$gte: startDate, $lte: endDate}},{"date":1})

Upvotes: 0

Dheemanth Bhat
Dheemanth Bhat

Reputation: 4452

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 }
)

Upvotes: 2

Shreyesh Desai
Shreyesh Desai

Reputation: 719

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)

Upvotes: 0

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

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.

Upvotes: 1

Related Questions