How can I create a default variable that takes the latest date in my database as the default?

Grafana Dashboards
1

In Grafana 12.0.0 (Linux), I want to create a pie chart that will give a snapshot of daily stats. The “stats_zone” table looks like this:

+---------------+--------+------------+                        
| snapshot_date | zone   | num_assets |
+---------------+--------+------------+                 
| 2025-05-25    | AMER   |      86858 |
| 2025-05-25    | EMEAR  |     140324 |
| 2025-05-25    | APJCI  |     319442 |
| 2025-05-26    | AMER   |      87385 |
| 2025-05-26    | EMEAR  |     141425 |
| 2025-05-26    | APJCI  |     320962 |
| 2025-05-27    | AMER   |      88741 |
| 2025-05-27    | EMEAR  |     137056 |
| 2025-05-27    | APJCI  |     320196 |
+---------------+--------+------------+

I can create a variable in my dashboard called “stats_date” that looks like this:

SELECT DISTINCT DATE_FORMAT(snapshot_date, '%Y-%m-%d') FROM stats_zone AS stats_date ORDER BY snapshot_date DESC;

and then use it in my query to build the pie chart:

SELECT zone AS metric, num_assets AS value FROM stats_zone WHERE snapshot_date = '$stats_date'

So far so good. The problem is that when I save this database, the value of “stats_date” that was active when I built the dashboard becomes the value you see whenever you visit the dashboard. If I save the dashboard today, then tomorrow the first value in the dropdown list on the dashboard will be 2025-05-28, but the selected default value will still be 2025-05-27. The user must manually pick the latest date from the stats_date dropdown.

How can I configure this dashboard to always default to the first entry in the stats_date dropdown (which will always be the latest date from the database), and not a hardcoded value?

Thanks to anyone who can help.

2

what data type is snapshot_date

3

It’s a string in the format of “YYYY-MM-DD”. So, the dropdown contains a list of strings:

2025-05-30
2025-05-29
2025-05-28
<etc.>

But the default value will always be whatever value I was looking at when I saved the dashboard, when what I really want is for it to default to the first value in the list every time.

4

Well, I came up with a kludgey way to do it. I modifed my snapshot_date to include an “All” variable which I called 'Latest' (the single-quotes are important).

image
image446×346 19.6 KB

Then I modified the SQL that builds the pie chart.

SELECT zone AS metric, num_assets AS value
FROM stats_zone
WHERE snapshot_date = (
    CASE 
        WHEN STR_TO_DATE($stats_date, '%Y-%m-%d') IS NOT NULL 
        THEN $stats_date
        ELSE (SELECT MAX(snapshot_date) FROM stats_zone)
    END
);

This causes MySQL to evaluate the variable’s value being passed in as $stats_date to decide what to do with it. If the value is in %Y-%m-%d format (like '2025-05-30'), then it uses that value to lookup the stats for that date. If it’s not a valid value (like 'Latest'), then it will ignore it and instead pick the latest snapshot_date value from the table.

Then I saved the dashboard while having “All” selected in the date picker.

image

It’s quite a hack, and it’s a nuisance that the display label in the date picker is “All”, and not the value I specified of “Latest”. But at least it works. Whenever the user picks “All” (which happens automatically when they load the dashboard), they’ll get the stats for the latest date.

1 Like