Redash: Date format in parameters doesn't work (MongoDB Query)

[SebC99]

Issue Summary

When using date parameters in MongoDB query, we can't use the date format, but only the number one (timestamp)

Steps to Reproduce

{
  "$match": {
     "_created_at": {
       "$gte": {
         "$date": "{{fromDate}}"
       },
       "$lt": {
         "$date": "{{toDate}}"
        }
       }
    }
}

[peaksnail]

me too, I store data with unix timestamp,there is no result when i query with date paramters, but i can get data when using number

[gglebq]

Any updates on this issue ?
"urt" : { "$gte" : {"$date" : "{{ Start Date }}"}, "$lte" : {"$date" : "{{ End Date }}"} }

That's the only thing stopping us from moving to Redash

[arikfr]

Did you try wrapping the value with $humanTime helper?

i.e.:

"urt": {
        "$gte": {
            "$date": {
                "$humanTime": "{{ Start Date }}"
            }
        },
        "$lte": {
            "$date": {
                "$humanTime": "{{ End Date }}"
            }
        }
    }

[dotbenj]

I have a similar use case and I've tried to wrap it with "$humanTime" helper as well and it's not working

"deliveryDate": {
   "$gt": {
      "$date": {
         "$humanTime": "{{ start }}"
      }   
   },
   "$lte": {
      "$date": {
         "$humanTime": "{{ end }}"
      }   
   }
}

[SebC99]

What works for me is using $ISODate:

"_created_at": {
   "$gte": "ISODate(\"{{ start }}\")"
}

[dotbenj]

Not working like this :

"deliveryDate": {
   "$gt": "ISODate(\"{{ start }}\")",
   "$lte": "ISODate(\"{{ end }}\")"
}

I'm using inputs Date & time

[manuelgu]

Any update on this? I'm unable to get any of the examples above working.

[ymhuang0808]

I found the date/datetime format in redash is incompatible with MongoDB.

[GaetanLhoest]

Hi, is there any update on this?

I would like as well to count events during the last hour, and made the following query:

{
    "collection": "live-providers",
    "aggregate": [{
        "$match": {
            "date": { 
                "$gt": "new ISODate(ISODate().getTime() - 1000 * 60 * 60)"
            }
        }
    } ,{
        "$group": {
            "_id": "$provider",
            "count1": {
                "$sum": "$insert"
            },
            "count2": {
                "$sum": "$delete"
            },
            "coun3t": {
                "$sum": "$updates"
            }
        }
    }, {
        "$sort": [{
            "name": "count1",
            "direction": 1
        }, {
            "name": "count2",
            "direction": -1
        }, {
            "name": "count3",
            "direction": -1
        }, {
            "name": "_id",
            "direction": -1
        }]
    }]
}

but it does not return anything, if I remove the $match section, it count on all the documents, and not only the last hour.