How to make Grafana properly display a Unix timestamp
There are many things in Prometheus that generate a 'time' value
that is in seconds since the Unix epoch of January 1st 1970 00:00
UTC. One such example, relevant to yesterday's entry on the time
when Prometheus metrics happened, is the
result of the Prometheus
function. When you're working with such times, it's common to want
to display them in a Grafana table (perhaps a multi-value table), and one natural way to display them is
simply as, well, the date and time they represent.
Sadly, Grafana makes this unnecessarily annoying, to the extent that every time I've needed to do this I've had to do Internet searches to find out how (again). If you edit a Grafana table, you can go to Column Styles and set the Type and Unit; the Type offers a 'Date', and for the Number type the Units has a 'Date & Time' section that offers options like 'Datetime local'. If you set either of these options on your Unix epoch timestamp, you will get a displayed result that is generally some time in 1970, and you will be unhappy and angry at Grafana.
The secret is that Grafana expects Unix timestamps in milliseconds,
not seconds. Prometheus and many other things generate timestamps
in seconds, not milliseconds; if you just pass these timestamps
straight to Grafana and have it display them as-is, they wind up
being interpreted as millisecond-based times that are way too small.
To make things work right, you must multiply all Unix timestamps
by 1000 before having Grafana display them. When you're using data
from Prometheus, this has to be done in the Prometheus query itself
in the Grafana dashboard, which will leave you with mysterious '
1000' bits in your queries.
Once you're turning your Unix timestamps from seconds to milliseconds, either the 'Date' column type or one of the 'Date & Time' units will display your timestamp. Right now I'm using the 'Datetime local' option of the latter because it seems less magical than assuming that Grafana's 'Date' type will always do what I want it to.
Sadly, this particular peculiar decision is now baked into Grafana in order to retain backward compatibility with current dashboards and panels. Grafana would have to introduce a new 'Date (seconds)' or 'Datetime local (seconds)' to do it right, and I hope that they do someday. Or introduce unit conversions in panel transformations.
(This is one of those entries that I write for myself so that I can find this information the next time I need it, because I'm sure I'm going to need it again. Grafana's behavior here is so counterintuitive and so opaque it's impressive. They don't even hint at the underlying units in the names, where they could do things like 'Datetime local (ms)'.)