CODEWITHSUNDEEP
javascriptmongodb
Zach
Zach

Reputation: 19112

Can I query MongoDB ObjectId by date?

I know that ObjectIds contain the date they were created on. Is there a way to query this aspect of the ObjectId?

Upvotes: 179

Views: 122082

Answers (13)

Karthickkumar Nagaraj
Karthickkumar Nagaraj

Reputation: 577

how to find Find the Command (this date[2015-1-12] to this Date[2015-1-15]):

db.collection.find({
  _id: {
    $gt: ObjectId(Math.floor((new Date('2015/1/12'))/1000).toString(16) + "0000000000000000"), 
    $lt: ObjectId(Math.floor((new Date('2015/1/15'))/1000).toString(16) + "0000000000000000")
  }
}).pretty()

Count the Command (this date[2015-1-12] to this Date[2015-1-15]):

db.collection.count({
  _id: {
    $gt: ObjectId(Math.floor((new Date('2015/1/12'))/1000).toString(16) + "0000000000000000"), 
    $lt: ObjectId(Math.floor((new Date('2015/1/15'))/1000).toString(16) + "0000000000000000")
  }
})

Remove the Command (this date[2015-1-12] to this Date[2015-1-15]):

db.collection.remove({
  _id: {
    $gt: ObjectId(Math.floor((new Date('2015/1/12'))/1000).toString(16) + "0000000000000000"), 
    $lt: ObjectId(Math.floor((new Date('2015/1/15'))/1000).toString(16) + "0000000000000000")
  }
})

Upvotes: 20

VIKAS KOHLI
VIKAS KOHLI

Reputation: 8470

Yes you can query object by date using MongoDB inserted ID

db.collectionname.find({_id: {$lt: ObjectId.fromDate( new ISODate("TZformat") ) } });

let's suppose users is my collection and I want all users created less than 05 January 2018

db.users.find({_id: {$lt: ObjectId.fromDate( new ISODate("2018-01-05T00:00:00.000Z") ) } });

For running from a query we can use like

db.users.find({_id: {$lt: ObjectId.fromDate(new Date((new Date().getTime() - (1 * 3 * 60 * 60 * 1000))) ) } })

All the users from the current time - 3 hours

Upvotes: 14

Beez
Beez

Reputation: 2101

A Solution Filtering within MongoDB Compass.

Based on versions:

  • Compass version: 1.25.0
  • MongoDB version: 4.2.8

Option 1:

@s7vr 's answer worked perfectly for me. You can paste this into the Filter field:

{$expr: { $and: [ {$gte: [{$toDate: "$_id"}, ISODate('2021-01-01')]}, {$lt: [{$toDate: "$_id"}, ISODate('2021-02-01')]} ] } }

Option 2:

I also found this to work (remember that the Date's month parameter is 0-based indexing so January is 0):

{_id: {$gte: ObjectId(Date(2021, 0, 1) / 1000), $lt: ObjectId(Date(2021, 1, 1) / 1000) } }

Option 3:

Equivalent with ISODate:

{_id: {$gte: ObjectId(ISODate('2021-01-01') / 1000), $lt: ObjectId(Date('2021-02-01') / 1000) } }

After writing this post, I decided to run the Explain on these queries. Here's the skinny on performance:

  • Option 1: 39 ms, 0 indexes used, 30 ms in COLLSCAN
  • Option 2: 0 ms, _id index used
  • Option 3: 1 ms, _id index used, 1 ms in FETCH

Based on my rudimentary analysis, it appears that option 2 is the most efficient. I will use Option 3, personally, as it is a little cleaner to use ISODate rather than remembering 0-based month indexing in the Date object.

Upvotes: 5

Leftium
Leftium

Reputation: 17943

Popping Timestamps into ObjectIds covers queries based on dates embedded in the ObjectId in great detail.

Briefly in JavaScript code:

/* This function returns an ObjectId embedded with a given datetime */
/* Accepts both Date object and string input */

function objectIdWithTimestamp(timestamp) {
    /* Convert string date to Date object (otherwise assume timestamp is a date) */
    if (typeof(timestamp) == 'string') {
        timestamp = new Date(timestamp);
    }

    /* Convert date object to hex seconds since Unix epoch */
    var hexSeconds = Math.floor(timestamp/1000).toString(16);

    /* Create an ObjectId with that hex timestamp */
    var constructedObjectId = ObjectId(hexSeconds + "0000000000000000");

    return constructedObjectId
}


/* Find all documents created after midnight on May 25th, 1980 */
db.mycollection.find({ _id: { $gt: objectIdWithTimestamp('1980/05/25') } });

Upvotes: 252

Lakhani Aliraza
Lakhani Aliraza

Reputation: 477

In rails mongoid you can query using

  time = Time.utc(2010, 1, 1)
  time_id = ObjectId.from_time(time)
  collection.find({'_id' => {'$lt' => time_id}})

Upvotes: 1

s7vr
s7vr

Reputation: 75994

You can use $convert function to extract the date from ObjectId starting in 4.0 version.

Something like

$convert: { input: "$_id", to: "date" }

You can query on date comparing between start and end time for date.

db.collectionname.find({
  "$expr":{
    "$and":[
      {"$gte":[{"$convert":{"input":"$_id","to":"date"}}, ISODate("2018-07-03T00:00:00.000Z")]},
      {"$lte":[{"$convert":{"input":"$_id","to":"date"}}, ISODate("2018-07-03T11:59:59.999Z")]}
    ]
  }
})

OR

You can use shorthand $toDate to achieve the same.

db.collectionname.find({
  "$expr":{
    "$and":[
      {"$gte":[{"$toDate":"$_id"}, ISODate("2018-07-03T00:00:00.000Z")]},
      {"$lte":[{"$toDate":"$_id"},ISODate("2018-07-03T11:59:59.999Z")]}
    ]
  }
})

Upvotes: 36

AbdelHady
AbdelHady

Reputation: 9712

If you want to make a range query, you can do it like in this post. For example querying for a specific day (i.e. Apr 4th 2015):

> var objIdMin = ObjectId(Math.floor((new Date('2015/4/4'))/1000).toString(16) + "0000000000000000")
> var objIdMax = ObjectId(Math.floor((new Date('2015/4/5'))/1000).toString(16) + "0000000000000000")
> db.collection.find({_id:{$gt: objIdMin, $lt: objIdMax}}).pretty()

Upvotes: 5

Murali
Murali

Reputation: 309

To get last 60 days old documents in mongo collection i used below query in shell.

db.collection.find({_id: {$lt:new ObjectId( Math.floor(new Date(new Date()-1000*60*60*24*60).getTime()/1000).toString(16) + "0000000000000000" )}})

Upvotes: 7

radtek
radtek

Reputation: 36370

In pymongo, it can be done this way:

import datetime
from bson.objectid import ObjectId
mins = 15
gen_time = datetime.datetime.today() - datetime.timedelta(mins=mins) 
dummy_id = ObjectId.from_datetime(gen_time)
result = list(db.coll.find({"_id": {"$gte": dummy_id}}))

Upvotes: 48

jksdua
jksdua

Reputation: 788

Using inbuilt function provided by mongodb drivers in in Node.js lets you query by any timestamp:

var timestamp = Date.now();
var objectId = ObjectID.createFromTime(timestamp / 1000);

Alternatively, to search for records before the current time, you can simply do:

var objectId = new ObjectID(); // or ObjectId in the mongo shell

Source: http://mongodb.github.io/node-mongodb-native/api-bson-generated/objectid.html

Upvotes: 44

Neo-coder
Neo-coder

Reputation: 7840

Using MongoObjectID you should also find results as given below

db.mycollection.find({ _id: { $gt: ObjectId("5217a543dd99a6d9e0f74702").getTimestamp().getTime()}});

Upvotes: 2

rxmnnxfpvg
rxmnnxfpvg

Reputation: 31033

Since the first 4 bytes of an ObjectId represent a timestamp, to query your collection chronologically, simply order by id:

# oldest first; use pymongo.DESCENDING for most recent first
items = db.your_collection.find().sort("_id", pymongo.ASCENDING)

After you get the documents, you can get the ObjectId's generation time like so:

id = some_object_id
generation_time = id.generation_time

Upvotes: 16

user1092803
user1092803

Reputation: 3297

From the documentation:

o = new ObjectId()
date = o.getTimestamp()

this way you have date that is a ISODate.

Look at http://www.mongodb.org/display/DOCS/Optimizing+Object+IDs#OptimizingObjectIDs-Extractinsertiontimesfromidratherthanhavingaseparatetimestampfield. for more information

Upvotes: 4

Related Questions