I want to query for documents that were created in the last 24 hours .
In SQL it'd be like this:
select * from table where table.createdAt >= date_sub( $$now, interval 1 day)
The most intuitive way to do this is to use $dateSubtract or $dateAdd like this (which by the way is posted by me)
but the thing is I am using Azure Cosmos for Mongo API, and it doesn't support those functions. You can see these two are not in there.
So the next best thing you can think of is something like this:
{
measuredAt:{
$lte: ISODate()-123
}
}
this is syntactically correct and does work on MongoDb Compass. But when I do this on Metabase, the Java parser returns a validation error.
the error message is as follows
JSON reader was expecting a name but found '-123'.
The next thing you think of, quite naturally, is to use $subtract, like this:
{
measuredAt:{
$lte:{
$subtract:[ISODate(), 123 ]
}
}
}
I haven't got around and figure it out, but this thing doesn't work on the Cosmos label, so I can't use this. (See the screen shot, both stages do exactly the same thing, and one returns documents and the other doesn't)
(the documents returned in each stage are hidden)
So what would be a good workaround for this, I just want to do
select * from table where column = 'somekeyword' and createdAt > date_sub($$now, interval 1 day) order by createdAt desc