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
Convert a 1D Array to a 2D Array in Numpy
Restricting the Value in Tkinter Entry Widget
How to Use Multiple Requests and Pass Items in Between Them in Scrapy Python
Python Daemon and Systemd Service
How to Access a Function Inside a Function
How to Add Hours to Current Time in Python
Validation of a Password - Python
How to Log Server Errors on Django Sites
Django JSONfield Inside Arrayfield
Why Doesn't Django's Model.Save() Call Full_Clean()
SchröDinger's Variable: the _Class_ Cell Magically Appears If You'Re Checking for Its Presence
Managing Dynamic Plotting in Matplotlib Animation Module
Pandas Read CSV File with Float Values Results in Weird Rounding and Decimal Digits