New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow relative dates for default values in SQL variables #4509

Open
mazameli opened this Issue Mar 9, 2017 · 15 comments

Comments

Projects
None yet
9 participants
@mazameli
Copy link
Contributor

mazameli commented Mar 9, 2017

Per a Discourse discussion. Right now you can only select a single date, though there are lots of times where you'd want the default to be "today" or "last 30 days":
screen shot 2017-03-09 at 12 24 49 pm

I suppose the challenge is that many of our relative date selections are actually between clauses, meaning you're actually mapping to two variables. There are some exceptions, such as "today," but not sure how we'd do "Last 30 days" with our current SQL template tags.

@tanzoniteblack

This comment has been minimized.

Copy link
Contributor

tanzoniteblack commented Mar 9, 2017

The date SQL variable only allows you to select a single date anyways, so if we show only relative dates that are single values rather than ranges (i.e. today or yesterday but not last week, last 30 days, etc.) then it would probably be "good enough".

@moumny

This comment has been minimized.

Copy link

moumny commented Mar 12, 2017

Creat design ! I like the "required" field very much.
Another option that could be convienient is to set "maximum date range" eg. maximum one month to avoid having users querying too much data.

@tlrobinson

This comment has been minimized.

Copy link
Member

tlrobinson commented May 3, 2017

I believe this is resolved. @mazameli can you verify?

@mazameli

This comment has been minimized.

Copy link
Contributor

mazameli commented May 3, 2017

Sort of. I can only get it to work with field filter variables (expected?). And when trying to run:

SELECT count(*)
FROM products
where CREATED_AT = {{t}}

I get Cannot parse "TIMESTAMP" constant "1" with these variable settings:

screen shot 2017-05-03 at 11 19 25 am

@mazameli

This comment has been minimized.

Copy link
Contributor

mazameli commented May 3, 2017

I think I'm doing this wrong, actually. It should be where {{t}}, which works.

@mazameli

This comment has been minimized.

Copy link
Contributor

mazameli commented May 3, 2017

But, again, I can't recall if the expected behavior is to have the same relative options for default values for variables of type Date, not just for Field Filters

@tlrobinson

This comment has been minimized.

Copy link
Member

tlrobinson commented May 3, 2017

I can only get it to work with field filter variables (expected?)

Yes, non field filter date variables can't do ranges, only a single value.

We could (in theory) support relative dates, and "30 days ago" would substitute now() - interval '30 days' or whatever. Not sure how useful that is.

@mazameli

This comment has been minimized.

Copy link
Contributor

mazameli commented May 3, 2017

Yes, non field filter date variables can't do ranges, only a single value.

Yeah, I just wasn't sure if we were trying to support yesterday, today, tomorrow for example. I think that was @tanzoniteblack's main usecase:

The date SQL variable only allows you to select a single date anyways, so if we show only relative dates that are single values rather than ranges (i.e. today or yesterday but not last week, last 30 days, etc.) then it would probably be "good enough".

@mazameli

This comment has been minimized.

Copy link
Contributor

mazameli commented May 3, 2017

Though I guess it doesn't matter really since you can do yesterday etc. with field filters. It feels a little roundabout, though; like I'd maybe expect to see those default relative options when selecting Date as the variable type.

@tanzoniteblack

This comment has been minimized.

Copy link
Contributor

tanzoniteblack commented May 3, 2017

The new filters that allow us to use relative dates are nice, and solve a section of the use case I'd intended.

However, many of the times I want to use these filters, I really only want to set the default to Today and still have the normal calendar widget for the user to interact with. I'm not sure why it's required that we when creating the question define which widget for time selection is required when doing a field method, rather than having a "Time Widget" that allows the user when using the question to select for themselves whether they want a single specific date, a span, or a relative time period.

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented May 8, 2017

I think the right solution here would be to allow Field-base-filter-clauses to display widgets on the report itself.

@salsakran salsakran modified the milestones: 0.25, 0.24.0 May 8, 2017

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented May 8, 2017

bumping this to 0.25 to give us a bit of room to figure out how to approach this. specifically, we should unify how question params work with user-space chrome (eg, the granularity controls + filters in the new QB timeseries mode), GUI query parameters (#4716), and dashboard filters. Right now all three are subtly different.

@salsakran salsakran modified the milestones: 0.25, 0.26 Jul 24, 2017

@salsakran salsakran modified the milestones: 0.26, 0.27 Sep 12, 2017

@bendem

This comment has been minimized.

Copy link

bendem commented Oct 10, 2017

Hi there, I just found this issue and I think my problem fits the current discussion.
Many of our queries are built build joining our tables to

from generate_series(
    date_trunc('day', {{from}}),
    date_trunc('day', {{to}}),
    interval '1 day'
) series(day)

From a user PoV, I'd love to be able to have something like

from generate_series(
    date_trunc('day', {{range.start}}),
    date_trunc('day', {{range.end}}),
    interval '1 day'
) series(day)

and mark the range variable as a date range.

As for the builder, it would probably be ok to generate a field between :range.start and :range.end predicate.

@salsakran salsakran modified the milestones: 0.27, 0.28 Nov 13, 2017

@ConradIrwin

This comment has been minimized.

Copy link

ConradIrwin commented Dec 6, 2017

I'd like to second the ability to have relative dates in normal filters.

Our use-case is for parameterizing bigquery's _PARTITIONTIME field, which metabase does not expose as a field.

@salsakran salsakran modified the milestones: 0.28, 0.29 Dec 18, 2017

@salsakran salsakran modified the milestones: 0.29, 0.30 Apr 25, 2018

@Giannettig

This comment has been minimized.

Copy link

Giannettig commented Jun 18, 2018

Hi guys, I need to filter today AND tomorrow in the query and it seems to me that I can either filter tomorrow OR today but not both. I tried selecting next one day and checkboxing current day and it di not work.

@salsakran salsakran modified the milestones: 0.30, 0.31 Sep 14, 2018

@camsaul camsaul modified the milestones: 0.31, 1.0 Nov 21, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment