How can I make a query or aggregation for a chart that's relative to today's date?

MongoDB Atlas
,
1

First off, I love Mongo Atlas Charts, and they are extremely valuable.

I’m just stuck on how to make charts use relative dates.

For example, I’d love to be able to make a dashboard that’s only of today’s current metrics.

I’ve seen there’s a built in $$NOW variable, but I’m not sure how to use it in an aggregation as a date instead of full datetime.

Because if you were to query on $$NOW you’d be filtering only records that have taken place right now.

Additionally, it would be great to know how I could make month over month charts, or even month comparison charts.

How do you perform date math in Mongo Atlas Charts?

3

Hi @Dylan_Pierce :wave:

Thanks for the great feedback regarding Charts!

Not sure if this is what you’re after in terms of using it an aggregation but please view the below example based off my test environment. I have the following chart:

image
image5448×3010 464 KB

You can see there is a total of 3 documents to the far right all with a date year value of 2024 and onwards.

Now, I set the following filter using $$NOW to only display / use documents with a date value of $lte $$NOW:

[{ $match: { $expr: { $lte: ["$date", "$$NOW"] } } }]

Which results in the following (where the 3 documents on the far right in the above image are no longer counted):

image
image5460×2984 461 KB

If you still require further assistance, could you please provide some sample document(s) and clarification on how you would like the $$NOW variable used against those? This is just so I can get a better idea of what you’re after :slight_smile:

Look forward to hearing from you.

Regards,
Jason

4

Thanks for the reply @Jason_Tran

[{ $match: { $expr: { $lte: ["$date", "$NOW"] } } }]

That is very close, but say you wanted to make a relative match. Consider these scenarios:

  • Include all documents with a $created_at datetime field that’s within 12 hours
  • Include all documents with a $created_at datetime field within 2 days
  • Include all documents with a $created_at datetime field within 3 months

How could you use the aggregation pipeline to only $match documents based on a relative date like the scenarios above?

The answer here says use math within an $expr, which seems processing heavy. Is there a more elegant way?

5

u can use $subtract to compare date different

7

To refresh the schema presented to SQL clients, you can utilize the Refresh or Reload functionality in your SQL client tool. This ensures that any recent changes to the database schema are reflected. Additionally, consider " Download Scarlet iOS " for a seamless mobile experience, enabling you to manage and monitor your database on the go.