Why am I unable to use datetime field to query data from Appsmith to MongoDB?-CodePudding

I'm trying to query data between datetime fields from Appsmith to MongoDB. I'm not able to get the data from this query, it is returning empty response but there is data in the selected datetime range.

The control flow looks like this:

There is a start time datetime picker field along with dropdown to select time period from start day(1 day, 1 week, 1 month).
On changing dropdown, it resets the table widget where I'm showing the data.
Table widget gets the data from call_data document but the query field is populated by a JSObject (queryObj.prepareQuery()).

JSObject code is:

export default {
    prepareQuery: () => {
        var callTypeSelected = call_type_select.selectedOptionValue;
        var customerId = customer_select.selectedOptionValue;
        var callStatus = call_status_select.selectedOptionValue;
        var timeRange = call_time_range.selectedOptionValue;
        var callTime = call_date_picker.selectedDate;
        var query = { };
        
        if (callTypeSelected !== null && callTypeSelected !== undefined && callTypeSelected !== "") {
            query["call_type"] = callTypeSelected;
        }
        
        if (customerId !== null && customerId !== undefined && customerId !== "") {
            query["strings_customer.$id"] = `ObjectId('${customerId}')`;
        }
        
        if (callStatus !== null && callStatus !== undefined && callStatus !== "") {
            query["call_status"] = callStatus;
        }
        
        if (timeRange !== null && timeRange !== undefined && timeRange !== "" && callTime !== null && callTime !== undefined) {
            var callTimeDateObj = new Date(callTime);
            
            var higherTime = new Date(callTimeDateObj);
            higherTime.setHours(higherTime.getHours()   1);
            if (timeRange === "1 day") {
                higherTime.setHours(higherTime.getHours()   24);
            } else if (timeRange === "1 week") {
                higherTime.setHours(higherTime.getHours()   24 * 7);
            } else if (timeRange === "1 month") {
                higherTime.setHours(higherTime.getHours()   24 * 30);
            }
            
            query["call_time"] = {"$gte": `ISODate('${callTimeDateObj.toISOString()}')`, "$lt": `ISODate('${higherTime.toISOString()}')`}
        }

        return query;
    }
}

Attaching the screenshots for more clarity:

enter image description here

CodePudding user response：

It seems the quotes are not properly put. e.g. the date query should look like:

{
  "createdAt": {
    $gt: ISODate('2020-03-17T11:07:25.312Z'), 
    $lt: ISODate('2020-03-29T11:07:25.312Z')
  }
}

CodePudding user response：

I was able to recreate your issue and get it working. Your query should look like -

{
  "release_date": {
    "$gte": "2020-10-16T13:02:04.044Z",
    "$lt": "2021-04-14T14:02:04.044Z"
  }
}

I've created a public app that you can fork and play around with - https://app.appsmith.com/applications/61e022f1eb0501052b9fa205/pages/61e02308eb0501052b9fa20c