Month to date query in MongoDB

[kd-am]

Hi,

I have been trying to create a dashboard on MongoDB data to show month to date (MTD) aggregates. I couldn't find any documentation for the syntax of "start of month" or "current month" in $humanTime. If anyone have any pointer, pleas help.

Thanks!
Kaustubh

[palonza]

hi @kd-am,

The $humanTime format in Redash does not provide a direct option for "start of month" or "current month" as you mentioned.

To achieve the desired functionality, you can use MongoDB's date aggregation operators to calculate the start of the current month dynamically within your MongoDB query.

1. In Redash, create a new query for your MongoDB data source.
2. Write your MongoDB aggregation query, including the logic to calculate the start of the current month dynamically.

Here's an example MongoDB aggregation query that calculates the start of the current month and performs further aggregations:

db.collection.aggregate([
  {
    $match: {
      dateField: {
        $gte: {
          $dateFromParts: {
            year: { $year: "$$NOW" },
            month: { $month: "$$NOW" },
            day: 1,
            hour: 0,
            minute: 0,
            second: 0,
            millisecond: 0
          }
        }
      }
    }
  },
  // Additional aggregation stages for further calculations
  // ...
]);

[kd-am]

Thanks @palonza !