Finding data between two dates by using a query in MongoDB Charts

[Ruben (Ruben van der Schaft)]

How would I write a query which checks the createdAt and finds all objects between 01-01-2021and 31-04-2021? I tried the following query, but it didn’t give back the right data(see image):
{createdAt:{$gte:“01-03-2021”,$lt:“31-03-2021”}}

its weird because when i use the filter function from MongoDB Charts(without the query bar), it will show the requested data.

[tomhollander]

Hi @Ruben -

Three things:

• To use dates in your queries, you need to wrap the strings in ISODate
• Dates need to be in Y-M-D format
• I’m guessing you probably want to also include data from the last day of April. In this case you should bring the end date forward by one day.

Putting these together, the query you likely want is:

{createdAt:{$gte:ISODate("2021-01-01"),$lt:ISODate("2020-05-01"}}

HTH
Tom

[Ruben (Ruben van der Schaft)]

Hi Tom,

thanks for your help. After some small fixes the query below worked!

{createdAt:{$gte:ISODate(“2020-03-01”),$lt:ISODate(“2021-03-31”)}}

[Mario_Yip]

Thanks for this Ruben. The first code got me close, but didn’t work.

[tomhollander]

Great! Yeah the dates in your screenshot and in your text didn’t match so I wasn’t sure what range you wanted.
Keep in mind that the query you are using returns data from the beginning of March 1st to the end of March 30th, i.e. it does not cover March 31st at all. Maybe that’s what you want but it seems unlikely. To cover the full month of March you need this:

{createdAt:{$gte:ISODate(“2020-03-01”),$lt:ISODate(“2021-04-01”)}}