0

what i want is to get the users who have created there account in between the range of dates and also want to get the customer with order count greater then 1 for those new customers, so what i am doing is trying to get the data greater then the last month i.e 1st of march 2020 but the output is giving me users till 1 april, why not till today i.e 11 april, the data is in following format 

"_id" : ObjectId("1dv47sd1a10048521sa1234d"),
"updatedAt" : ISODate("2020-04-01T16:19:26.460+05:30"),
"createdAt" : ISODate("2020-04-01T16:18:46.066+05:30"),
"email" : "edx@gmail.com",
"phone" : "xxxxxxxxxx",
"password" : "$awdad$2b$10$4YaO6AEZqXA9ba0iz14ALi",
"dob" : "00/20/1990",
"stripeID" : "xxxxxxxxxxxxxxxx",
"__t" : "Customer",
"banned" : false,
"picture" : "link to image",
"name" : {
    "first" : "ababab",
    "last" : "Saaa"
},
"orderCount" : 2,
"creditCards" : [ ],
"addresses" : [ ],
"__v" : 0,
"isEmailVerified" : true

i have written a query for extracting data from the date greater then last month but it is giving me data till the 1 of april, my query is as follows

db.users.find({
  "createdAt" : { "$gte" :new Date("2020-03-1") }
})

so i want to get data timm today from 1 march 2020 also order count is greter then 1,thanks in advance i am preety new with mongo

Avinash jain
  • 486
  • 1
  • 7
  • 15
  • Use this example for finding data between a range of dates: [find - Query for Ranges](https://docs.mongodb.com/manual/reference/method/db.collection.find/#query-for-ranges). Use the [$and](https://docs.mongodb.com/manual/reference/operator/query/and/index.html) operator to specify multiple conditions (as in conditions for date and orderCount). – prasad_ Apr 11 '20 at 07:11
  • @prasad_ i have tried it but still data till 1/4/2020 is showed(db.users.find({ createdAt : { $gt :new Date("2020-03-1"), $lt: new Date("2020-04-11") } })) – Avinash jain Apr 11 '20 at 07:19
  • What you are telling is not clear. Can you update your post with more details. What is your input data and the query? You can include few required fields only. **It looks like that you are querying data between dates** `01-March-2020` and `11-April-2020`. – prasad_ Apr 11 '20 at 09:03
  • @prasad_ ia have updated and clearified more thing please have a look if it helps, thanks – Avinash jain Apr 11 '20 at 09:14
  • Does the collection has documents with `createdAt ` value greater than 1-April-2020? – prasad_ Apr 11 '20 at 09:21
  • The following two posts have similar question and answer: [Find objects between two dates MongoDB](https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb?rq=1) _and_ [Date query with ISODate in mongodb doesn't seem to work](https://stackoverflow.com/questions/19819870/date-query-with-isodate-in-mongodb-doesnt-seem-to-work?rq=1). – prasad_ Apr 11 '20 at 09:27

1 Answers1

0

MongoDB only stores absolute times (aka timestamps or datetimes), not dates. As such the queries should generally be specified using times, not dates, also.

Furthermore, MongoDB always stores times in UTC. Different systems can operate in different timezones. For example, the shell may be operating in a different timezone from your application.

To query using dates:

  1. Determine what timezone the dates are in.
  2. Convert dates to times in that timezone. For example, 2020-03-01 00:00:00 UTC - 2020-03-31 23:59:59 UTC and 2020-03-01 00:00:00 -0400 - 2020-03-31 23:59:59 -0400 are both "March" and would match different sets of documents.
  3. Convert the times to UTC. This may be something that a framework or library you are using handles for you, for example Mongoid in Ruby does this.
  4. Use the UTC times to construct MongoDB queries.

This is stated in the documentation here.

D. SM
  • 13,584
  • 3
  • 12
  • 21