Graphing time series for durations formatted as HH:MM:SS

mlefevre · December 19, 2019, 7:09pm

I’m using Grafana to access data in an influxdb database. The graphs for data in the form of floats and ints is working very well. Now, I’m trying to graph a time series of daylight hours (duration) in the form of a string as HH:MM:SS. So far I have had no luck.

This is my query from the query inspector:
{
“xhrStatus”: “complete”,
“request”: {
“method”: “GET”,
“url”: “api/datasources/proxy/3/query”,
“params”: {
“db”: “hass”,
“q”: “SELECT "state" FROM "variable.daylight" WHERE time >= now() - 7d”,
“epoch”: “ms”
},
“data”: null,
“precision”: “ms”
},
“response”: {
“results”: [
{
“statement_id”: 0,
“series”: [
{
“name”: “variable.daylight”,
“columns”: [
“time”,
“state”
],
“values”: [
[
1576476000161,
“10:01:16”
],
[
1576562400082,
“10:01:00”
],
[
1576648800116,
“10:00:45”
],
[
1576685180034,
“10:00:45”
],
[
1576735200049,
“10:00:35”
]
]
}
]
}
]
}
}

or in screenshot form:

That results in no data displayed in the graph. If I take the query and paste it into influx, I get this:

    InfluxDB shell version: 1.7.6
    Enter an InfluxQL query
    > use hass
    Using database hass
    > SELECT "state" FROM "variable.daylight" WHERE time >= now() - 7d
    name: variable.daylight
    time                state
    ----                -----
    1576476000161782016 10:01:16
    1576562400082861824 10:01:00
    1576648800116229120 10:00:45
    1576685180034132992 10:00:45
    1576735200049216000 10:00:35

which tells me that the data is there, I just need to get the query right in Grafana. Can anyone see what I’m doing wrong?

jangaraj · December 19, 2019, 8:42pm

Grafana graphs only int/float data, not strings. There is no way of converting strings to int/float in InfluxDB. The best option is to save duration as int [seconds] into InfluxDB directly.

mlefevre · December 19, 2019, 11:50pm

That was my first thought too. However, in setting the units for the Y axis, I found, under Duration, a unit value of HH:MM:SS. What would that be used for?

jangaraj · December 20, 2019, 6:52am

github.com

grafana/grafana/blob/d7c76dacad0b5b867acc53d476bdb5664327e26b/packages/grafana-data/src/valueFormats/dateTimeFormatters.test.ts#L170-L181


it('1 dthms', () => {
  const str = toDurationInHoursMinutesSeconds(1);
  expect(formattedValueToString(str)).toBe('00:00:01');
});
it('-1 dthms', () => {
  const str = toDurationInHoursMinutesSeconds(-1);
  expect(formattedValueToString(str)).toBe('00:00:01 ago');
});
it('0 dthms', () => {
  const str = toDurationInHoursMinutesSeconds(0);
  expect(formattedValueToString(str)).toBe('00:00:00');
});

=> that is only for formatting of int (number of seconds) to more human friendly format, e.g. 1=>00:00:01.

mlefevre · December 20, 2019, 2:38pm

Makes perfect sense now, Thanks for the help.