1

If you want to quickly toggle your dashboards between known date intervals, you can use a query based dropdown to list. This example uses PostgreSQL to make a three-option dropdown list.

The dropdown list query looks like this:

SELECT 'Last Day' as "timeframe", current_date - interval '24 hours' as "value"
UNION ALL
SELECT 'Last Week' as "timeframe", current_date - interval '7 days' as "value"
UNION All
SELECT 'Last Month' as "timeframe", current_date - interval '1 month' as "value"

When I plug this into the {{ timeframe }} parameter in the below query, I get an interval selector for free:

SELECT events.action,
       events.object_id,
       events.object_type
FROM EVENTS
WHERE events.created_at >= '{{ timeframe }}'
LIMIT 100

image

2 Likes
2

One need to be careful with this approach: if they don’t set a refresh schedule for the dropdown values query they will end up with stale dates. Actually even if they do set a refresh schedule, short time frames, like “last day” might be wrong if the page was open for some time.

I would rather add the logic of picking the interval into the query itself. Maybe something like the following can work:

WHERE events.created_at >= CASE 
WHEN '{{parameter}}' = 'Last Day' THEN current_date - interval '24 hours' 
WHEN '{{parameter}}' = 'Last 7 Days' THEN current_date - interval '7 days' 
ELSE current_date + interval '1 year' END /* Just a future date, because in theory shouldn't happen*/

It makes the query a bit more complex, but can be saved as a query snippet to reduce typing in future queries.

Also, should be unnecessary once the following is merged:

1 Like