How to Query Mongodb Objectid by Date

Can I query MongoDB ObjectId by date?

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

mongodb compass query with objectId in date range

You need to pass a date object to the ObjectId.fromDate, not a string. Try this:

ObjectId.fromDate(new Date('2019-01-01'))

This function works only in the shell and doesn't exist in the drivers.

EDIT after comments:

Here is a solution that works in Compass as well:

{ 
    $expr: { 
        $gte: [
            {"$toDate":"$_id"}, 
            ISODate("2021-01-01T00:00:00.000Z") 
        ]
    } 
}

Keep in mind, however, that it requires a version of mongo of 4.0+. You can checkout the docs here.

Also, checkout this related topic: Can I query MongoDB ObjectId by date?

It is not about Compass, but it provides solutions for generating the ObjectIds from a date without being dependent on ObjectId.fromDate().

Get the timestamp associated with a MongoDB ObjectId in Snowflake

My bad, I didn't get that a MongoDB ObjectId has the create timestamp embedded in the Id.

You can decode the timestamp with this:

SELECT TO_TIMESTAMP_NTZ(TO_NUMBER(SUBSTR(ObjectId, 1, 8), 'XXXXXXXX'))
FROM (SELECT '5d8dc6e0a26870000101c53d' ObjectId)

=> 2019-09-27 08:22:56.000

The id consists of the following parts:

1. The first 4 bytes representing the seconds since the unix epoch
2. The next 3 bytes are the machine identifier
3. The next 2 bytes consists of process id
4. The last 3 bytes are a random counter value

The list above (hinting about half the answer) taken from "How to convert from Timestamp to Mongo ObjectID"

---

My old wrong answer:

You can't retrieve a date from an object id. The reason why your Python code example works is that you have an entire object that has an id and other properties including generation_time.

If you just extract the id property from MongoDB then you don't have the other properties.

Date range query on ObjectId in MongoDB using Spring REST pagination

You can try something like

Calling Code:

ObjectId idMin = new ObjectId(new Date("2018/1/1"));
ObjectId idMax = new ObjectId(new Date("2018/2/1"));
Pageable pages = new PageRequest(1, 2);

Page<User> results = repository.findByObjectIdsAndPages(idMin, idMax, pages);

Repository

 @Query("{_id:{$gt: ?0, $lt: ?1}}")
 Page<User> findByObjectIdsAndPages(ObjectId idMin, ObjectId idMax, Pageable pages);

How to retrieve the date from a MongoDB ObjectId using SQL

This can be achieved as follows (assuming objectId is a string) in MySQL:

SELECT FROM_UNIXTIME(
    CAST(CONV(SUBSTR(objectId, 1, 8), 16, 10) AS UNSIGNED)
) FROM table

It works as follows:

• SUBSTR(objectId, 1, 8) takes the first 8 characters from the hexadecimal objectId string
• CONV(..., 16, 10) converts the hexadecimal number into a decimal number and returns it as a string (which represents the UNIX timestamp)
• CAST (...) AS UNSIGNED converts the timestamp string to an unsigned integer
• FROM_UNIXTIME(...) converts the timestamp integer into the date

Note that by default the displayed date will be based on your system's timezone settings.

---

Related Topics