Query Mongodb on month, day, year... of a datetime

Question

I'm using mongodb and I store datetime in my database in this way

for a date "17-11-2011 18:00" I store:

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

I would like to do a request like that

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

or

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

anyone knows how to do this query?

Answer 1

Dates are stored in their timestamp format. If you want everything that belongs to a specific month, query for the start and the 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 straightly query mongodb collections by date components like day or month. But its possible by using the special $where javascript expression

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 one)

Check out the below shell commands to get the parts of date

>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. It comes with the performance problems. Please check out the below comments by @kamaradclimber and @dcrosta. I will let this post open so the other folks get the facts about it.

and check out the link $where Clauses and Functions in Queries for more info

Answer 3

Use the $expr operator which allows the use of aggregation expressions within the query language. This will give you the power to use the Date Aggregation Operators in your query as follows:

month = 11
db.mydatabase.mycollection.find({ 
    "$expr": { 
        "$eq": [ { "$month": "$date" }, month ] 
    } 
})

or

day = 17
db.mydatabase.mycollection.find({ 
    "$expr": { 
        "$eq": [ { "$dayOfMonth": "$date" }, day ] 
    } 
})

---

You could also run an aggregate operation with the aggregate() function that takes in a $redact pipeline:

month = 11
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [ { "$month": "$date" }, month ] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

For the other request

day = 17
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [ { "$dayOfMonth": "$date" }, day ] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

---

Using OR

month = 11
day = 17
db.mydatabase.mycollection.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$or": [ 
                        { "$eq": [ { "$month": "$date" }, month ] },
                        { "$eq": [ { "$dayOfMonth": "$date" }, 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 operator incorporates the functionality of $project and $match pipeline and will return all documents match the condition using $$KEEP and discard from the pipeline those that don't match using the $$PRUNE variable.

Answer 4

how about storing the month in its own property since you need to query for it? less elegant than $where, but likely to perform better since it can be indexed.

Answer 5

If you want to search for documents that belong to a specific month, make sure to query like this:

// 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 quering like below as much as possible.

// 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 6

You can find record by month, day, year etc of dates by Date Aggregation Operators, like $dayOfYear, $dayOfWeek, $month, $year etc.

As an example if you want all the orders which are created in April 2016 you can use below query.

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

Here created is a date type field in documents, and $$ROOT we used to pass all other field to project in next stage, and give us all the detail of documents.

You can optimize above query as per your need, it is just to give an example. To know more about Date Aggregation Operators, visit the link.

Answer 7

You can use MongoDB_DataObject wrapper to perform such query like 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 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);
}