Forum Discussion

aditya_sharma's avatar
aditya_sharma
New Contributor III
7 years ago

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

mongodb

3 Replies

  • psathyanarayan's avatar
    psathyanarayan
    Employee
    7 years ago

    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.

    • vkbeemanapalli's avatar
      vkbeemanapalli
      Employee
      7 years ago

      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