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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use MongoDB DataObject wrapper to perform such query like below    model   new MongoDB DataObject  orders      model- gt whereAdd  MONTH created    4 AND YEAR created    2016      model- gt find     while   model- gt fetch          var dump  model       OR  similarly  using direct query string    model   new MongoDB DataObject      model- gt query  SELECT   FROM orders WHERE MONTH created    4 AND YEAR created    2016     while   model- gt fetch          var dump  model

User · Answer

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

User · Answer

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   gt date   ISODate  2011-09-25T10 12 34Z    gt  date getYear   111  gt  date getMonth   8  gt  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

[python] Query Mongodb on month, day, year... of a datetime

Examples related to python

Examples related to mongodb

Examples related to datetime

Examples related to mongodb-query

Examples related to pymongo