1

Hi!

I have a mongodb database connected with redash and am trying to write a query filtering documents by timestamp and showing only documents with the timestamp in the current month (first october until today).
I’ve tried to use the $humanTime extension:

    "collection": "orders",
    "query": {
        "myTimestamp": {
            "$gte": {
                "$humanTime": "last month"
            }
        }
    }

but unfortunately “last month” evaluates to the first of the previous month and thereby includes the last and the current month. “this month” doesn’t get the requested results either because “this month” evaluates to today not the “first of the month”.

I’ve also tried a mongodb aggregation but unfortunately failed to inject the current month/year into the query:

{
    "collection": "orders",
    "aggregate": [
        {
            "$addFields": {
                "month": {
                    "$month": "$myTimestamp"
                },
                "year": {
                    "$year": "$myTimestamp"
                }
            }
        },
        {
            "$match": {
                "$and": [
                    {
                        "month": 10
                    },
                    {
                        "year": 2021
                    }
                ]
            }
        }
    ]
}

The aggregation works, but I need to replace the fixed values 10 and 2021 with expressions evaluating to current month/year.

Thank you in advance for any hints or suggestions!

1 Like
2

I managed to solve it by grouping the data by year-month pairs and selecting the first one:

{
    "collection": "orders",
    "aggregate": [
        {
            "$unwind": "$myTimestamp"
        },
        {
            "$group": {
                "_id": {
                    "Month": {
                        "$dateToString": {
                            "format": "%Y-%m",
                            "date": "$myTimestamp"
                        }
                    }
                },
                "count": {
                    "$sum": 1
                }
            }
        },
        {
            "$sort": [
                {
                    "name": "_id.Month",
                    "direction": -1
                }
            ]
        },
        {
            "$limit": 1
        }
    ]
}
1 Like