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

user_17393502 0
Viewed 567

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

enter image description here

enter image description here

enter image description here

appsmith mongodb datetime
2 Answers

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')
  }
}

The video under the workaround section could also be helpful here: https://github.com/appsmithorg/appsmith/issues/10374

user_33006620
user_92776390
Related