Playing With Dates in Redash (MongoDB Aggregation)

technology

Goal: Filter dates in a MongoDB aggregation using Redash and its $humanTime function

Challenge: Filtering with filters such as 3 months ago obviously takes the current day as the index. But often times we want to observe full months or years. For that, we have to find alternative ways to do that.

Approach: We need to tell the $humanTime function that it should find a date at the first (1st) of a specific time period, for example month.

Given the following query part:

1
2
3
4
5
6
7
{
    "createdAt": {
        "$gte": {
          "$humanTime": "2 weeks ago"
        }
    }
}

We can use one month after last month -6 months at 0:00 to get the 1st of the month 6 months ago. Let’s say it is the 20th of August now. This date selection will be 1st of February.

To get the 1st of the current month, we simply use one month after last month at 0:00.

And finally, the most recent 1st of January can always be set through 1st january -12 months at 0:00.

Share