Is there a way to do period filters with SQL
for exsample last 30 days
Last year
Current month etc
Or is the only option choosing the start and end dates?
Hi there,
You should be able to do this with a Date field filter:
If you mean directly in SQL syntax, you could do something like this in Postgres:
SELECT *
FROM your_table
WHERE your_date_column >= CURRENT_DATE - INTERVAL '30 days';
I am not seeing what you are seeing
These are the options I get
I added it as filter in the SQL code {{start_date}}
even if I could choose a period like last 30 days, how ill it trasfer the data to the code, when the filter is a single value and not a range?
Hi there,
I believe you configured the filter to be of type "Date", which only allows you to select a specific day. But if you use "Field Filter" and point it to a date column in the query, it shows the options I sent in the post above. See full docs here:
Thank you
I think tis only works when the date is directly stored in the DB in a Meta friendly format.
In our case the date is stored in a messy way, and we cave manually convert it in the select, is it possible to use your technique with our specifific use case?
This is what I am currently using
I would love to hear your thoughts, (I had to blure out the sensetive part)
Marcos - one more question, is there a way to do the aggregation in a smarter way, as currently it is just a Text Based Filter with predifiend list Day, Week, Month, Quarter, Year - but they might be something build it to do this.
This allows me to aggregate the charts based on different periods based on the our analyst's inputs.
For usual timestamp formats, you can go to Table Metadata, find the column, and configure Casting to convert it to a date:
This should let you apply a Date Field Filter to it.
Smart binning by week, month, etc, is supported in the graphical query builder:
However, it's not currently supported in SQL questions, so your approach I believe is close enough.
There is a feature request for it and it's in our roadmap in the "Soon" category, so please subscribe to it to get updated when it's out