Formating separate columns (date, time) to a Time column for CSV plugin

[andreasbrotzer]

Hello there,

I am using Grafana server (version 8.1.5) on a raspberry pi and want to create dashboards for data stored locally. Therefore, I included the CSV plugin and can also access the data.

The issue is that the csv files contain a column date (20210901) and a time column (120000). I couldn’t find a transformation to format these two columns to one time stamp column (2021-09-01 12:00:00). Any idea how to do that? It would be highly appreciated!

Cheers, AndBro

[mattabrams]

Hi @andreasbrotzer and welcome to the forun

When it comes to data transformation and presentation issues inside Grafana, I think that the most useful thing a user can share is their raw, unformatted data, found inside the inspect drawer like this.

From there we can mock up your data and help you get it looking the way you want (hopefully!)

[andreasbrotzer]

Hi @mattabrams,

thanks for the welcome.

Here is a screenshot of the raw data:

As you can see there is a column with dates (20211011) and Times (000001). which doesn work for Grafana for a time series plot as I understood it so far. Therefore, I have to convert/transform it to a dateformat Grafana understands (2021-10-11 00:00:01), right? Couldn’t find a proper transformation for the CSV plugin, however.

Cheers, Andreas

[mattabrams]

@andreasbrotzer

20211011

have you tried using the Convert Field type transformation?

Ideally, if this is CSV, I would find a way / script / macro to combine those fields, which I understand is your main question. To that end, you could try and somehow use a combination of transformations to join those fields in an appropriate format, but that’s not an ideal solution (even if we could).

What is generating this data, though? I ask because you should maybe try and fix this timestamp issue as “upstream” as possible…can whatever device is originating your timestamps change its formatting? perhaps to Unix timestamps?

[andreasbrotzer]

Thanks for your input.

For your example you rely on a TestData DB (some sort of Database). As I mentioned, I access the filed directly via the file system using the CSV plugin. I want to display the data as a rolling data monitor.

I tried using the Convert Field type transformation after applying a Add Field from calculation transformation to join the column (Date) and (Time) into one (-> YYYYMMDD,HHMMSS).

Doesn’t work however. The new DateTime Column (DT) is empty. Any suggestions…

[mattabrams]

can you share your raw, unformatted data with us?

You can use this method

[andreasbrotzer]

You can find a data file here.