CODEWITHSUNDEEP
mongodbdatedatetime
jfix
jfix

Reputation: 561

MongoDB query to retrieve distinct documents by date

I have documents in the database with a dateTime value like so:

{
    "_id" : ObjectId("5a66fa22d29dbd0001521023"),
    "exportSuccessful" : true,
    "month" : 0,
    "week" : 4,
    "weekDay" : "Mon",
    "dateTime" : ISODate("2018-01-22T09:02:26.525Z"),
    "__v" : 0
}

I'd like to:

  1. query the database for a given date and have it return the document that contains the dateTime if the date matches (I don't care about the time). This is mainly to test before inserting a document that there isn't already one for this date. In the above example, if my given date is 2018-01-22 I'd like the document to be returned.

  2. retrieve all documents with a distinct date from the database (again, I don't care about the time portion). If there are two documents with the same date (but different times), just return the first one.

From what I understand Mongo's ISODate type does not allow me to store only a date, it will always have to be a dateTime value. And on my side, I don't have control over what goes in the database.

Upvotes: 0

Views: 2286

Answers (1)

s7vr
s7vr

Reputation: 75934

Try range query with start date time from start of the day to end date time to end of the day. So basically create dates a day apart.

Something like 

var start = moment().utc().startOf('day');
var end = moment().utc().endOf('day');

db.collection.find({
    dateTime: {
        $gte: start,
        $lte: end
    }
})

Get all distinct dates documents:

db.collection.aggregate(
 {"$group":{
  "_id":{
    "$dateToString":{"format":"%Y-%m-%d","date":"$dateTime"}
  },
  "first":{
    "$first":"$$ROOT"
  }
}}])

Upvotes: 1

Related Questions