Add relative date "before" filter

[shamruk]

I have a Mongo database. I want to get all records in a range between 3 days ago and two days ago. Sounds as a common request.
I have not figured out how to make it using gui and tried to use native query but it immediately converts relative dates like "yesterday" to a fixed day like 05.01.2017.
As I cannot use js in native query and write something like "new Date()" is there any way to make relative dates possible.
I could be done with some simple convention where you replace some MetabaseDate.daysAgo(3) to "03.01.2017" before running a query.

Looking forward to your reply, thanks for creating such a great tool.

PS or point me to the class where I could add such feature

⬇️ Please click the 👍 reaction instead of leaving a +1 or update? comment

[salsakran]

You can just add 2 filters date >= 3 days ago + date < 1 day ago using our gui query builder.

This is easy enough to do using two filters, and we're not likely to add more complexity to single filters when it's easy enough to compose.

[shamruk]

Looks like I don't see. Could you point it?
I see only 2 relative options Previous and Next https://yadi.sk/i/2YW0NVwp38sNaQ
I can make date >= 3 days ago but not date < 1 day ago with this 2 options.
PS
Relative dates would be great for native queries anyway. Thanks

[camsaul]

@shamruk try something like Date After -3 Days combined with Date Before -1 Days

[shamruk]

I see MySQL have variables (witch would let me make what I want) and mongo does not

[camsaul]

You don't need variables for this the dates are relative to whenever the query is ran

[shamruk]

Before is not relative https://yadi.sk/i/voWpScp538sTyA
It is a fixed date

[camsaul]

@shamruk you can just upload images directly to the GH thread (just drag them into the text box)

[salsakran]

@shamruk actually, you're correct regarding there not being a relative "before" filter.

[shamruk]

By the way I have managed to workaround this problem editing metabase configuration in DB. But got some strange problem:
Making only hour range metabase has added a date range (i guess for optimization). But this date range is empty because it is using gt and lt instead of gte and lte

{
    "$match": {
        "$and": [
            {
                "time~~~day": {
                    "$gt": {
                        "___date": "2017-01-12"
                    }
                }
            },
            {
                "time~~~day": {
                    "$lt": {
                        "___date": "2017-01-13"
                    }
                }
            },
            {
                "time~~~hour": {
                    "$gte": {
                        "___date": "2017-01-12T01:00:00"
                    },
                    "$lte": {
                        "___date": "2017-01-14T00:00:00"
                    }
                }
            }
        ]
    }
}

[shamruk]

And I cannot fix native query myself because all dates will be fixed (not relative) and there are no variables in mongo to overcome this. Looks I'm stuck now with this 3 problems

[huksley]

I have the same problem - no way to include Previous 30 days + today, except by editing SQL by hand.

[ipeevski]

Ideally, it would be good to have a relative option in these date filters:

• before (before today)
• after (after today)
• on (on today, this week, this month, this year)
• between (for completeness ...)

After that change, previous and next may not be needed at all - it would be the same as between 1 month and yesterday for example

[efredz]

It could be done if Metabase supported the Date constructor, so we could do something like this:

timestamp: { // 18 minutes ago (from now)
    $gt: new Date(ISODate().getTime() - 1000 * 60 * 18)
}

[tjramage]

We could really do with this... @ipeevski – did you ever figure this one out?

[ipeevski]

I think latest metabase has a few more options around dates and seems to be less of an issue, but haven't found a specific solution to the original query

[varunmundale]

Date After -x Days , Date Before -y Days can be done using native query
Here is an example which works for me...

[{
    "$project": {
        "mydate": "$mydate",
        "dminus3": {
            "$subtract": [ISODate(), 259200000]
        },
        "dminus90": {
            "$subtract": [ISODate(),7776000000]
        }
    }
},{
    "$project": {
        "mydate": "$mydate",
        "dateComp": { "$cmp": ["$dminus3", "$mydate"] },
        "dateComp1": { "$cmp": ["$dminus90", "$mydate"] }
    }
}
, {
    "$match": {
        "$and": [{
        {
            "dateComp": 1
        },{
            "dateComp1": -1
        }
    }
]

1 day = 86400000
Metabase also disallows operations or comments, so we have to use a hardcoded value of 86400000 instead of 24 * 60 * 60 * 1000

[dfx413]

While it looks like it will be possible to formulate expressions like between -3 days and -2 days with the new date/time UI that is being built, AFAIK there is still no easy way to cross between past and future, eg. between -3 days and +2 days.
This is currently only possible using a combination of 3 interval() custom expressions, which is not very suitable for the average user.
Would be really nice if we were able to use these expressions in the query builder.

[flamber]

Coming in 0.43 - there's a separate requests for relative between, which is possible with a little hacky workaround #21976