Mongodb request for month, day, year ... date and time

Question

I use mongodb and I store datetime in my database this way

for the date "11/17/2011 18:00" I kept:

date = datetime.datetime(2011, 11, 17, 18, 0) db.mydatabase.mycollection.insert({"date" : date}) 

I would like to make such a request

 month = 11 db.mydatabase.mycollection.find({"date.month" : month}) 

or

 day = 17 db.mydatabase.mycollection.find({"date.day" : day}) 

Does anyone know how to make this request?

Answer 1

Dates are stored in time stamp format. If you want everything that belongs to a certain month, a request for the beginning and end of the month.

 var start = new Date(2010, 11, 1); var end = new Date(2010, 11, 30); db.posts.find({created_on: {$gte: start, $lt: end}}); //taken from http://cookbook.mongodb.org/patterns/date_range/ 

Answer 2

You cannot directly query mongodb collections by date components, such as day or month. But it can be used with the special expression $ where javascript

 db.mydatabase.mycollection.find({$where : function() { return this.date.getMonth() == 11} }) 

or simply

 db.mydatabase.mycollection.find({$where : 'return this.date.getMonth() == 11'}) 

(But I prefer the first)

Check the shell commands below to get date parts

 >date = ISODate("2011-09-25T10:12:34Z") > date.getYear() 111 > date.getMonth() 8 > date.getdate() 25 

EDIT:

Use $ where only if you have no other choice. This is due to performance issues. Please check out the comments below by @kamaradclimber and @dcrosta. I will open this post so that other people know about it.

and see the link $ where Classes and functions in the queries for more information

Answer 3

how about saving the month in your own property, since you need to request it? less elegant than $where , but most likely better since it can be indexed.

Answer 4

If you want to search for documents related to a specific month, be sure to complete the query as follows:

 // Anything greater than this month and less than the next month db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lt: new Date(2015, 7, 1)}}); 

Avoid as many queries as possible, as shown below.

 // This may not find document with date as the last date of the month db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lt: new Date(2015, 6, 30)}}); // don't do this too db.posts.find({created_on: {$gte: new Date(2015, 6, 1), $lte: new Date(2015, 6, 30)}}); 

Answer 5

You can start the aggregate operation using the aggregate() function, which accepts $redact , which uses the date aggregation operators in the condition expression:

 var month = 11; db.collection.aggregate([ { "$redact": { "$cond": [ { "$eq": [ { "$month": "$createdAt" }, month ] }, "$$KEEP", "$$PRUNE" ] } } ]) 

For another request

 var day = 17; db.collection.aggregate([ { "$redact": { "$cond": [ { "$eq": [ { "$dayOfMonth": "$createdAt" }, day ] }, "$$KEEP", "$$PRUNE" ] } } ]) 

---

Using OR

 var month = 11, day = 17; db.collection.aggregate([ { "$redact": { "$cond": [ { "$or": [ { "$eq": [ { "$month": "$createdAt" }, month ] }, { "$eq": [ { "$dayOfMonth": "$createdAt" }, day ] } ] }, "$$KEEP", "$$PRUNE" ] } } ]) 

---

Using AND

 var month = 11, day = 17; db.collection.aggregate([ { "$redact": { "$cond": [ { "$and": [ { "$eq": [ { "$month": "$createdAt" }, month ] }, { "$eq": [ { "$dayOfMonth": "$createdAt" }, day ] } ] }, "$$KEEP", "$$PRUNE" ] } } ]) 

The $redact includes the functionality of the $project and $match pipeline and will return all documents matching the condition using $$KEEP and discard those that are not using the $$PRUNE variable.

Answer 6

You can find records by month, day, year, and date using date aggregation operators like $ dayOfYear, $ dayOfWeek, $ month, $ year , etc.

As an example, if you want all orders created in April 2016, you can use below.

 db.getCollection('orders').aggregate( [ { $project: { doc: "$$ROOT", year: { $year: "$created" }, month: { $month: "$created" }, day: { $dayOfMonth: "$created" } } }, { $match : { "month" : 4, "year": 2016 } } ] ) 

Here a field of type date is created in the documents, and we used $$ ROOT to transfer the whole other field for subsequent design to the next stage and give us all the details of the documents.

You can optimize the above request according to your needs, just give an example. To learn more about date aggregation operators, visit the link .

Answer 7

You can use the MongoDB_DataObject shell to execute a query like the one below:

 $model = new MongoDB_DataObject('orders'); $model->whereAdd('MONTH(created) = 4 AND YEAR(created) = 2016'); $model->find(); while ($model->fetch()) { var_dump($model); } 

OR, similarly, using a direct query string:

 $model = new MongoDB_DataObject(); $model->query('SELECT * FROM orders WHERE MONTH(created) = 4 AND YEAR(created) = 2016'); while ($model->fetch()) { var_dump($model); }