CODEWITHSUNDEEP
sqlmongodbmongodb-query
Hendrik
Hendrik

Reputation: 71

MongoDB Now > Interval Like SQL

I used mysql before mongodb and I must convert the following sql query to mongo:

WHERE NOW() > (Field + INTERVAL 3 DAY)

I just find a way to check if a date is between to dates but this isn't a solution for me.

Sample document:

{
    "_id" : ObjectId("54cd524be811410c1048a9ef"),
    "UserID" : 0,
    "root" : {
        "files" : [ 
            {
                "FileID" : 1,
                "UploadStatus" : "Unfinished",
                "FileName" : "",
                "FileSize" : 3.2,
                "FileKey" : "",
                "StartUploadRequest" : "2015-01-11 16:43:11",
                "ServerID" : 0,
                "Path" : [                         
                ]
            }
        ]
    }
}

Upvotes: 3

Views: 443

Answers (2)

Ian Mercer
Ian Mercer

Reputation: 39297

Simply adjust now by 3 days and then query against that:

db.collection.find({root.files.StartUploadRequest:
     {$lt : new Date(new Date()-(3 * 24 * 3600 * 1000))}})

Upvotes: 1

Disposer
Disposer

Reputation: 6371

I really need your sample mongo document but I assumed you have something like this in your collection:

{
    _id = "item0",
    Field : ISODate("2014-11-10T00:00:00.000Z"),
}

Then:

db.collection.aggregate(
[    
    { $project : { 'comp' : { $add : ['$Field', (3 * 24 * 3600 * 1000)] }, Field : 1 } },
    { $match : { 'comp' : { $lt : new Date()} } },
]).result

I above query new Date() will give us Now and (3 * 24 * 3600 * 1000) will produce 3 day offset in milliseconds.

Update:

Based on your sample denouement the query should be:

db.collection.aggregate(
[    
    { $unwind : '$root.files'}, 
    { $project : { 'comp' : { $add : ['$root.files.StartUploadRequest', (3 * 24 * 3600 * 1000)] }, 'root.files': 1 } },
    { $match : { 'comp' : { $lt : new Date()} } },
]).result

Upvotes: 2

Related Questions