MongoDB query current date timestamp

Get Help
1

This is regarding running a query for mongodb on metabase where we need the current timestamp for filtering our data.
But it seems like the following doesn't work:
new Date().getTime()

Current version of metabase - v0.41.5

Query for ref:
{
"$match": {
"$expr" : {
$gt: [
"timestamp", {
$subtract: [ new Date().getTime(), { $multiply: [1, 24, 60, 60, 1000 ] } ]
}
]
}
}
}

timestamp - field in the mongodb document
I want to filter the data occurring in the last 24 hours.
Please help to resolve this issue or any thread which we can follow to get the required result.

1 Like
2

Hi @prateek
You can do something like this to get previous 1 day:

    "$match": {
      "$expr": {
        "$gte": [
          "$created_at",
          {
              "$subtract": [ISODate(), { $multiply: [ 1, 24, 60, 60, 1000 ] }]
          }
        ]
      }
    }
3

ISODate() returns date object.
e.g. ISODate("2022-05-06T05:50:35.187Z").

So subtract doesn't work which in return gives the same ISODate for $gte operator.
Maybe we will be needing something at epoch level so that we may able to subtract the product of milliseconds from the same.

4

Hi flamber,

Below command is giving error on version v0.43.1

Can you guide what to do in this case? Same query is working on NoSQLBooster.

Invalid JSON input. Position: 105. Character: '.'.

[
{ "$match": {
"$expr":{
"$lte": [
"$myTimeStamp", new Date().getTime() - 864000000
]
}
}
},
{ $sort: { myTimeStamp : -1 } }
]

If we try to cover new Date().getTime() - 864000000 within quotes, then it becomes string and result is not retrieved.