Time NaN (Not a Number) How to interpret/manipulate time format?

Grafana Configuration
1

Hello,

Just got started with Grafana last week, currently investigating if this would be a good tool to use for my needs. I’m obviously an absolute noob so I have this question…

I have a JSON API webservice that I can query to retrieve some data. I’ve managed to create a data source and configure a dashboard to display this data. It shows me a workable set in a table format but the time column (readable string) is using a format that Grafana does not seem to recognize meaning that when I try to set the “Type” to “time” it only gives me a “NaN” for value of that column.

How can I manipulate the content of the field so that the time is recognized and can be plotted on a graph?

Thanks!

The format:

image
image1508×701 51.1 KB

The NaN after setting the “Type” to “time”:

image
image1501×692 41.5 KB

1 Like
2

Hi @valravn ,

Do you have control over the API endpoint? Can you change those values into unix epoch format first? Something about that formatting isn’t getting recognized or parsed correctly…

3

Hey Matt,

Unfortunately I do not, it is an API provided to me and several others who consume it for different purposes. I’ve talked to the person in charge of it and he also mentioned that the way he is generating the JSON would make it hard to change/add a different format. I thought that sounded odd but assume he might be using an external library and might not fully grasp it.

At any rate, if we assume that changing the endpoint is not possible then is there no way to fix this? There is no transformation we can run on a string value to parse/remove content? Is there no string replace option? No way to specify a date format so parsing will work? No way around this at all without changing the endpoint?

Thanks again, sorry if it sounds rough, just slightly surprised I didn’t find any way to manipulate this field :slight_smile:

1 Like
4

Hi @valravn

Can you go to the inspect drawer in the panel dropdown and share your raw query (removing any sensitive parts from the request / response)?

5

Sure Matt, here it is.

Thanks again.

{
  "id": 23763571993,
  "gridPos": {
    "h": 9,
    "w": 12,
    "x": 0,
    "y": 9
  },
  "type": "table",
  "title": "STATIC",
  "transformations": [],
  "pluginVersion": "8.1.2",
  "fieldConfig": {
    "defaults": {
      "custom": {
        "align": "auto",
        "displayMode": "auto"
      },
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          },
          {
            "color": "red",
            "value": 80
          }
        ]
      },
      "mappings": [],
      "color": {
        "mode": "thresholds"
      }
    },
    "overrides": []
  },
  "options": {
    "showHeader": true
  },
  "targets": [
    {
      "cacheDurationSeconds": 300,
      "fields": [
        {
          "jsonPath": "$.qam_us_list[*].channel_id",
          "type": "string"
        },
        {
          "jsonPath": "$.qam_us_list[*].snr_us",
          "name": "",
          "type": "number"
        },
        {
          "jsonPath": "$.qam_us_list[*].timestamp",
          "name": "",
          "type": "string"
        }
      ],
      "method": "GET",
      "queryParams": "",
      "refId": "A",
      "urlPath": "/opco_api/oa/kpi/service-a/qam-us/mac/A0:39:EE:9E:37:FE"
    }
  ],
  "datasource": null
}
6

@valravn

The issue, as far as I can see, is that there is no mechanism in Grafana to programmatically transform data on a value-by-value level. This is probably because Grafana was always designed as a visualization layer that sits on top of other DBs.

That being said, you can pattern match using regex and transform things like labels and field titles, and you can exclude rows of data based on regex, but I’m not sure if Grafana is the best place to transform a returned JSON value of 2021-08-30T08:34:17Z[UTC] into 2021-08-30T08:34:17.

https://grafana.zuchka.dev/d/9Is37BVnz/change-time?orgId=1

Doing so, I believe, would resolve your issue. Here is a vid I made showing how changing the unit --> date / time transforms the first value to NaN and the second value to the chosen format

7

note that if you need timezones the following should also work:

2016-05-10 18:20:18.674 UTC
2016-05-10 18:20:18.674 +1:00
8

Hey Matt,

I understand. So in short Grafana doesn’t support this value manipulation, I have to try to convince the API developer to change this field or add a new one and unix epoch sounds like the way to go. That’s clear and thanks again for your time.

Maybe I was the first and only person to have this particular requirement but perhaps not and maybe it would be a nice feature to consider adding to the software. I can see how some string manipulation and maybe other value transforms could come in handy, especially when you don’t have access to changing the source data and there are already some transform options in place anyway. I’m not paying Grafana Labs for anything so please don’t take this as a feature request or anything but if it makes sense maybe bring it up, see what the consensus might be? I don’t know.

Anyway, thanks again, I’ll just cross my fingers and hope the guy has some time to change the API and then I’ll go from there again.

Cheers!

9

Maybe someone else has a solution!

I would also consider making an issue in the repo for the JSON API plugin.

That project is very well maintained and maybe the author can help you out more :+1:

10

Cool Matt, thanks again :slight_smile: