Average date field in an aggregation

Working with Data
,
1

Hello,

I need a little help with the aggregation framework. I want to generate a report where I am meant to get the average day over a set of matched documents in the aggregation. I have just one date field in the record to compare across all checked documents. Below is a sample document

schema = {
  _id: ObjectId('some ID'),
  created: new Date('some date'),
  ...
}
3

Hi @Bolatan_Ibrahim,

Welcome to the MongoDB Community forums :sparkles:

To clarify, you want to generate a report where you compare each document in the collection to the average date of all the other documents in the same collection. Is that correct?

Can you please provide more information about what you are trying to achieve in your report, such as what specific date field you want to compare or what kind of result you are expecting? This will help us better understand your requirements and provide more accurate assistance.

Best,
Kushagra

5

To calculate the average of a date field created in MongoDB, you would typically convert the dates to a numeric value (e.g., timestamps), calculate the average of those numeric values, and then convert that average back to a date format if needed. MongoDB does not directly support averaging date fields, so this process involves a few steps:

  1. Convert Date to Timestamp: Use $toDate to ensure the field is in date format (if it’s stored as a string or other format) and then $toLong to convert the date to a timestamp (a long integer representing milliseconds since the UNIX epoch).

  2. Group and Average: Use $group to aggregate the documents and $avg to calculate the average of the converted timestamps.

  3. Convert Average Timestamp back to Date: Optionally, convert the average timestamp back to a readable date format using $toDate.

Here is how you can structure the aggregation pipeline:

[
  {
    "$addFields": {
      "createdTimestamp": {
        "$toLong": "$created"
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "averageTimestamp": {
        "$avg": "$createdTimestamp"
      }
    }
  },
  {
    "$addFields": {
      "averageDate": {
        "$toDate": "$averagecreatedTimestamp"
      }
    }
  }
]

@Kushagra_Kesav I believe that all the answers you are asking are already in OP’s original post. The schema states that the field he needs averaged is the created field. OP states he wants to compare:

…a set of matched documents in the aggregation.

6

Dear @Zaakiy_Siddiqui, I have started to follow you because I am not yet decided if what you posted is from you or from ChatGPT.

What makes me lean toward ChatGPT is

  1. well formatted 3 steps list with embolden first sentence
  2. extra explanation of what is a timestamp being a long integer representing … epoch,
  3. the mentioned of using $toDate (if stored as a string or other format) while in the same post (but in your 2nd edit) you seem to blame @Kushagra_Kesav, for asking clarification. The schema is also clear about the fact that created is already a Date.
  4. sentences like “in MongoDB, you would typically …”, "MongoDB does not directly …, it sounds funny
  5. in your 2nd edit, you use OP’s original post, and OP states. OP stands for original post, you use @ for Kushagra_Kesav, I think a human written text would also use @ Bolatan_Ibrahim to talk about OP author
  6. in you 2nd edit, you quote a set of matched documents in the aggregation, yet you shared an aggregation that does not use $match

What makes me lean for a genuine human written post:

  1. the error you made in the aggregation, in $group you $avg into the field averageTimestamp but in your last $addFields, you use the non-existing field averagecreatedTimestamp.

Finally, I am like Kushagra_Kesav, I have no clue about the matched documents or the avarage day.