Date filter in MongoDB snap

Hi,

I am trying to filter the records from MongoDB Select snap using date filter but somehow it is not working. I am using below expression in condition. Please suggest if I am doing something wrong:

{$and:[{“etl_date”:{$gte:“2019-05-15T00:00:00.000”}},{“etl_date”:{$lte:“2019-05-15T23:59:00.000”}}]}

Thanks
Aditya

I am assuming that when you say it is not working, it is giving syntax error. I tried the exact same syntax on my local mongo db instance, it gives the syntax error with MongoDB compass which is a front end tool to connect to MongoDB directly.

I think it it does not like the letter “T” that separates the date from the time component. You can just remove it and try, it should work.

For example, in my table, I had a column create_time that I used as shown below:

{$and:[{“create_time”:{$gte:“2019-02-12 20:53:31.000”}}, {“create_time”:{$lte:“2019-02-12 20:53:31.000”}}]}

Hope this helps.

Try the following:

{“INSERT_TIMESTAMP”: {’$gte’: ISODate(“2018-09-01”), ‘$lte’: ISODate(“2018-12-01”)}}

Based on MongoDB documentation it should be in the above format and the API is expecting the same from Snap

1 Like

Thanks, it worked.