CODEWITHSUNDEEP
pythonmongodbdatetimemongodb-querypymongo
kschaeffler
kschaeffler

Reputation: 4193

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

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?

Upvotes: 56

Views: 135630

Answers (7)

chridam
chridam

Reputation: 103305

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.

Upvotes: 14

CEDA
CEDA

Reputation: 55

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

Upvotes: -1

Puneet Singh
Puneet Singh

Reputation: 3543

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.

Upvotes: 7

Ujjwal Ojha
Ujjwal Ojha

Reputation: 1360

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

Upvotes: 12

RameshVel
RameshVel

Reputation: 65857

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

Upvotes: 33

RubyTuesdayDONO
RubyTuesdayDONO

Reputation: 2378

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.

Upvotes: 13

DrColossos
DrColossos

Reputation: 12988

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/

Upvotes: 63

Related Questions