Grafana Relative DateTimes in table views (from SQL)

BEFORE and AFTER: What you will get from this blog post

I had a Grafana dashboard that monitored a group of microservices for their last updated time. If they’ve not updated in the last couple of minutes, that is bad — it probably meant that a service crashed or something like that. Therefore tracking the DateTime of the last update is important, Seeing DateTimes in table widgets isn’t often that noticeable or actionable. Here’s what it looked like;

The original table view of DateTimes pulled directly from a SQL database. Not that noticeable for actionable.

The data source was SQL, with an identifier for the service name, and a DateTime field for the “last updated”. Those DateTimes are accurate, sure, but my objective here is to know when something isn’t updating. Here are the problems I had with a simple DateTime field shown in Grafana;

1. Nothing stands out, one of those values is a month out of date, but it’s not immediately obvious.
2. Before reading the list, I need to check today’s date… and time!

I tried hunting around in the Table settings in Grafana, but I could not find anything like a “relative” DateTime, or calculation. It took me a little while, but I figured — why am I doing this in the display/view (Grafana), I can do this at the data source level instead and get Grafana to display the result.

Making changes to the Data Source (SQL)

Knowing that [My]SQL is perfectly capable of manipulating dates and times, I changed my query to make use of the timestampdiff function.

This outputs the minutes since each last updated time — much better already!

Making changes to Grafana

The number values now jump off the page! 300475 minutes since the last update. That sounds bad. Let's make it even easier to spot that badness too with some threshold formatting, and most importantly — units that make it human-readable;

Summary

When you’ve made changes to the query, and on Grafana’s table view, this results in something that looks like this;

Oh, that is so much nicer. Relative DateTimes with a table that are humanly readable — much more noticable and actionable.

Notes

Note: I used MySQL, just because that is where the data comes from in my environment. This technique should be possible with most other data sources too. From a quick search, here is a Postgres implementation, and I think the same for MsSQL.

Note: Make sure that the data source SQL query is set to output tableand not time seriesin Grafana. For me, the table widget did not accept a Time Series data source.

Format as “Table” was needed for me to get the table widget working correctly.

Note: If using the SQL method above, the time unit (minute used in this post) from the SQL query obviously needs to match up with the unit used in the Grafana view for the relative times to make sense.