NodeJS MongoDB: Querying ISO Date

Question

I have a collection of ISO dates stored in MongoDB as strings, like so:

{ "date": "2014-12-12T03:33:33.333Z" },
{ "date": "2014-12-13T03:33:33.333Z" }

In the console, I can query these perfectly using

{ "date": ISODate("2014-12-12T03:44:00.000Z") }

However, I'm using the NodeJS driver, and I cannot use ISODate. I have found several questions pertaining to this problem on here, but none of the proposed solutions seem to work. For instance:

// These does not find any matches
db.find({ "date": new Date("2014-12-12T03:44:00.000Z") })
db.find({ "date": { '$eq': '2014-12-12T03:44:00.000Z' } })
db.find({ "date": { '$eq': new Date('2014-12-12T03:44:00.000Z') } })

//This throws an error stating $date is not an operator
db.find({ "date": { '$date': '2014-12-12T03:44:00.000Z' } })

Why are these queries failing?

Edit: Here's another sample, straight from the database:

{
    "_id": "5a7e88f34b5916723589183f",
    "date": "2014-12-12T03:42:00.000Z",
    "granularity": 180
}

EDIT 2: This query produces the following error MongoError: $dateFromString requires that 'dateString' be a string, found: date with value 2014-12-12T03:44:00.000Z

async loadCandle(date, granularity) {        
        date = date + ''; //Aded to ensure date is a string, but still get the error.
        var candle = await this.data.collection('dates').findOne( 
            { $expr : 
                {$eq : 
                    [
                        {$dateFromString : {dateString : "$date"}}, 
                        new Date("2014-12-12T03:33:33.333Z") //Normally would pass in the `date` variable here
                    ]
                } });

Saravana · Accepted Answer · 2018-02-10T05:02:49.023

1

because $date is not an operator

you need to use $dateFromString to convert string date to ISODate for comparison

db.datez.find(
    {$expr : 
        {$eq : 
            [
                {$dateFromString : {dateString : "$date"}}, 
                new Date("2014-12-12T03:33:33.333Z")
            ]
        }
    }
)

using aggregation

db.datez.aggregate([
    {$match : 
        {$expr : 
            {$eq : 
                [
                    {$dateFromString : {dateString : "$date"}}, 
                    new Date("2014-12-12T03:33:33.333Z")
                ]
            }
        }
    }
])

collection

> db.datez.find()
{ "_id" : ObjectId("5a7e795e80aae386f73cf0fe"), "date" : "2014-12-12T03:33:33.333Z" }
{ "_id" : ObjectId("5a7e795e80aae386f73cf0ff"), "date" : "2014-12-13T03:33:33.333Z" }
>

result

> db.datez.find({$expr : {$eq : [{$dateFromString : {dateString : "$date"}}, new Date("2014-12-12T03:33:33.333Z")]}})
{ "_id" : ObjectId("5a7e795e80aae386f73cf0fe"), "date" : "2014-12-12T03:33:33.333Z" }

edited Feb 10 '18 at 05:02

answered Feb 10 '18 at 04:55

Saravana

12,647
2
39
57

Having a weird issue with this. If I copy your `aggregate` verbatim, it gives me the error :`$dateFromString requires that 'dateString' be a string , found: date with value ...`. The date value doesn't match up with the proper date. If i change `$date` to `$dateStr` or something else it doesn't throw any errors but does not return anything. – w0f Feb 10 '18 at 06:12
@w0ffen you've mentioned you're storing dates as Strings? can you post the output of `typeof db.datez.findOne().date` ? – Saravana Feb 10 '18 at 06:16
Yes, I have updated the main post with an actual entry stored. Is there any possibility that somewhere in the query `$date` is being parsed/replaced with something else? Because I've made sure that the date is a string, but when the error throws, It is with a different date and I cannot discern why. And this happens when I statically define, like you put in your answer, `new Date("2014-12-12T03:33:33.333Z")`. – w0f Feb 10 '18 at 06:21
what version of mongo are you using? can you also post your query? – Saravana Feb 10 '18 at 06:37
I am using MongoDB v3.6.2, and I've added my query to the original post – w0f Feb 10 '18 at 17:36
`data.collection('dates').findOne({$expr : {$eq : [ {$dateFromString : {dateString : "$date"}}, new Date("2014-12-12T03:33:33.333Z") ] } })` can you try this – Saravana Feb 10 '18 at 17:46
Oops, my bad. Last night I was modifying it to try and get the error to cease. What I had in the OP stopped the error from occurring but was returning no results. Your code produces the `$dateFromString requires that 'dateString' be a string, found: date with value 2014-12-12T03:44:00.000Z` error, even when the date is specified as `new Date("2014-12-12T03:33:33.333Z")` – w0f Feb 10 '18 at 17:50
A quick followup: I have since changed my code dealing with inserting the date records to parse the string to a `Date()` object before inserting, and now a simple `findOne({date: date}); works perfectly. Thank you for the help. – w0f Feb 10 '18 at 18:21

score 1 · Answer 2 · answered Aug 07 '20 at 08:25

You can use $dateToString operator which generate the string date of any specified format, which can be compared later.

For string comparison of date, input format should be YYYY-MM-DD, any other format would get fail for ranges date queries

Let me explain through example:

Here is my collection in mongoDb :

{
    "_id" : ObjectId("5f2d0a0c632ec022e08c3191"),
    "date" : ISODate("2020-07-12T00:00:00Z")
}
{
    "_id" : ObjectId("5f2d0a12632ec022e08c3192"),
    "date" : ISODate("2020-07-13T00:00:00Z")
}

Now the query to be fired from Node for comparison of such stored ISODates is as follow

db.collection.aggregate(
    [
        {
            $addFields: {
                formattedDate: { // An extra field "formattedDate" is added in each document which can be compared later through pipeline using $match
                    $dateToString: {
                        format: "%Y-%m-%d",
                        date: "$date" // in "$date" date is variable from db
                    }
                }
            }
        },
        {
            $match: {
                formattedDate: {
                    $eq: "2020-07-12" // here you can provide your input date yyyy-mm-dd
                }
            }
        }
    ]
)

So for above query you will get output as

{
    "_id" : ObjectId("5f2d0a0c632ec022e08c3191"),
    "date" : ISODate("2020-07-12T00:00:00Z"),
    "formattedDate" : "2020-07-12"
}

Hope this will help you or somebody else!

NodeJS MongoDB: Querying ISO Date

2 Answers2

Linked