Query Mongodb on Month, Day, Year... of a Datetime

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

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/

Datetime to string in mongodb where only month and year are in data

Mongo requires the "date" part to fully exist, this means it expects to get at least a year, a month and a day.

So you can either pad your string with 01 and provide the format option:

db.collection.aggregate([
  {
    $addFields: {
      date: {
        "$dateFromString": {
          "dateString": {
            "$concat": [
              "01",
              "$date"
            ]
          },
          "format": "%d%m%Y"
        }
      }
    }
  }
])

Mongo Playground

Or use $dateFromParts instead:

db.collection.aggregate([
  {
    $addFields: {
      date: {
        "$dateFromParts": {
          year: {
            "$toInt": {
              "$substr": [
                "$date",
                2,
                4
              ]
            }
          },
          month: {
            $toInt: {
              $substr: [
                "$date",
                0,
                2
              ]
            }
          }
        }
      }
    }
  }
])

Mongo Playground

---

Bonus - This requirement is not stated explicitly in the documentation but can be found in the source code:

if (s->time->y != TIMELIB_UNSET && s->time->m != TIMELIB_UNSET &&
    s->time->d != TIMELIB_UNSET &&
    !timelib_valid_date( s->time->y, s->time->m, s->time->d)) {
    add_pbf_warning(s, TIMELIB_WARN_INVALID_DATE, "The parsed date was invalid", string, ptr);
}

MongoDB Query by DateTime with Python

When you say the object is stored as datetime.datetime, to what are you referring? A custom object?

Per the Mongo docs, this is the only date object they support explicity:
https://docs.mongodb.com/v3.4/reference/method/Date/

From the Docs:

Date() Returns a date either as a string or as a Date object.

Date() returns the current date as a string in the mongo shell. new
Date() returns the current date as a Date object. The mongo shell
wraps the Date object with the ISODate helper. The ISODate is in UTC.
You can specify a particular date by passing to the Date() method a
datetime string. For example:

new Date("") which returns the ISODate with the specified
date. new Date("") which specifies the datetime
in local datetime and returns the ISODate with the specified datetime
in UTC. new Date("") which specifies the
datetime in UTC and returns the ISODate with the specified datetime in
UTC.

To create a query to search on a Date field in mongo, you would instatiate an ISODate like this

db.collection.find({"datefield" : ISODate("YYYY-MM-DDTHH:MM:SS")})

Generally, this will be of somewhat limited use, since the time is measured in milliseconds, so you'll likely need to do a range query similar to:

db.collection.find({"datefield" : {"$gte" : <beginning_of_day_as_ISODate>, "$lte" : <end_of_day_as_ISODate>})

For example:

{createdDate : {$gte : ISODate("2017-12-06T00:00:00"), $lte : ISODate("2017-12-06T23:59:59")}}

If you are using a custom date object, or storing the date in some non-standard format, your query will need to be tailored to that object.

Find objects between two dates MongoDB

Querying for a Date Range (Specific Month or Day) in the MongoDB Cookbook has a very good explanation on the matter, but below is something I tried out myself and it seems to work.

items.save({
    name: "example",
    created_at: ISODate("2010-04-30T00:00:00.000Z")
})
items.find({
    created_at: {
        $gte: ISODate("2010-04-29T00:00:00.000Z"),
        $lt: ISODate("2010-05-01T00:00:00.000Z")
    }
})
=> { "_id" : ObjectId("4c0791e2b9ec877893f3363b"), "name" : "example", "created_at" : "Sun May 30 2010 00:00:00 GMT+0300 (EEST)" }

Based on my experiments you will need to serialize your dates into a format that MongoDB supports, because the following gave undesired search results.

items.save({
    name: "example",
    created_at: "Sun May 30 18.49:00 +0000 2010"
})
items.find({
    created_at: {
        $gte:"Mon May 30 18:47:00 +0000 2015",
        $lt: "Sun May 30 20:40:36 +0000 2010"
    }
})
=> { "_id" : ObjectId("4c079123b9ec877893f33638"), "name" : "example", "created_at" : "Sun May 30 18.49:00 +0000 2010" }

In the second example no results were expected, but there was still one gotten. This is because a basic string comparison is done.

Extract month and year from mongodb Datetime filed and Group_by in Django

after a long time searching, i found the answer and i want to share it with others.
in my situation i should use aggregate() to extract year and month from dateTime field and then group on them link.

$month --> extract month from date
$year  --> extract year from date
$group --> group_by command
$sum   --> for counting something (here +1)

so, i changed my query like below:

    pipeline = [
    {
        "$group":{
            "_id":{
                "year": {"$year": "$date"},
                "month":{"$month":"$date"}
                },
                "total": { "$sum": 1 }
                }
    }]

and my python Query is like this:

PortScanModel.objects.aggregate(*pipeline)

Finish! this command first extract year and month from date then group_by year and month and counts the rows. now i have the count of them per year-month and i can plot a chart of usage.

Query Mongodb on specific or range month without year of a datetime

$month is an aggregation operator, so it can only be used with aggregate, not find:

db.test.aggregate([
    {$project: {
        id: 1,
        date: 1,
        month: {$month: '$date'}
    }},
    {$match: {month: 11}},
    {$project: {
        id: 1,
        date: 1
    }}
])

Result:

{
    "result" : [ 
        {
            "_id" : ObjectId("5453d0b73712b66352662484"),
            "id" : 1,
            "date" : ISODate("2014-11-01T00:00:00.000Z")
        }, 
        {
            "_id" : ObjectId("5453d0b73712b66352662486"),
            "id" : 3,
            "date" : ISODate("2012-11-21T00:00:00.000Z")
        }
    ],
    "ok" : 1
}

---

Related Topics