CODEWITHSUNDEEP
mongodbepoch
user3010617
user3010617

Reputation: 137

Fetch documents from a MongoDB collection based on timestamp

I have a MongoDB collection with documents that look like this

{
    "_id" : ObjectId("5aab91b2caa256021558f3d2"),
    "Timestamp" : "2017-11-16T14:43:07.5357785+01:00",
    "status" : 1,
    "created_at" : 1521193394,
    "updated_at" : 1521193394,
    "deleted_at" : ""
}

Data gets entered into the collection every 15 minutes. Using the created_at field, which is in epoch time, I would like to find a way to fetch data at the top of every hour. So for example, data is entered at 12.00 12.15 12.30 12.45 13.00 13.15 13.30 13.45 14.00.

I would like to fetch entries from the collection that were entered at 12.00 13.00 and 14.00.

I am also open to suggestions as to whether or not using epoch time is the best way to go about it.

Upvotes: 3

Views: 1070

Answers (1)

RaR
RaR

Reputation: 3233

Using epoch time is really a good way to go.

Since you are stoing in seconds, every round hour can be divisible by 3600(seconds in hours) without remainder. You can make use of this property to find your documents.

db.collection.find({created_at: {$mod: [ 3600, 0 ]}});

According to $mod documentation, it will,

Select documents where the value of a field divided by a divisor has the specified remainder

We provided divisor as 3600 and remainder as 0. This should give what you expect.

To ignore seconds:

For this condition, mod(epoch, 3600) should be less than 59. This query can be formed using $expr of mongo 3.6

db.collection.find({$expr: {$lte: [{ $mod: [ '$created_at', 3600 ] }, 59]}});

Hope this helps!

Upvotes: 1

Related Questions