Date and Time Handling with Grafana

Grafana
1

Hi Team,
first things first:
I 'm using Grafana 10.4.2 with a MySQL/MariaDB as the data source.
So I have a little table in that DB containing temperature values in relation with date and time.
So far nothing special.

The column for Date and Time is… guess what … in date and time format.
grafik
BUT for some reason Grafana adds a fix time to that date an change the time column to a string data type.
grafik
When I fix the first issue by using the transform data method the data type, the data type of the date column changes to string.
grafik
But I can deal with that…
More important is the fact Time column has the data type of string.

This prevent the visualisation to work properly due to missing time values.
When I try to fix that with the convert field type method, Grafana adds suddenly the current date in front of the time. Causing again wrong data.

grafik
grafik1003×223 12.4 KB

Is there someone who can show me, how to fix that issue?

2

I don’t understand what is a question and how is it t related to Grafana.

3

Looking at the last screenshot, the right column should be a “time-only” column with the data type of “time”. Because the database column (the source) uses the data type “time” as well. (There is no date an time together in on cell.)
But for some reason Grafana adds the current date to the time values, making them invalid.

4

additional information:
The whole data set contains the temperature values from yesterday (beginning from 0 am to12 pm). One column for the date, one column for the time (only) and one column for the temperature values.
Does that make it clear?

5

Don’t use 2 columns, use just one column of TIMESTAMP or DATETIME type and store data there in the UTC timezone. Your life will be easier.

If you have to use 2 columns (DATE/TIME) then combine them (check your DB doc how), so Grafana will get single timestamp column result.

1 Like
6

I’m afraid I made myself unclear.
The data is correct and is displayed correctly at times. Only when you want to correct the first error (change string back to time) the values are at first supplemented unintentionally and then incorrect (the panel should take values from yesterday, but inserts the current day). This is simply a bug and to be honest, a double one. I would really like this bug to be fixed with release 11.1.

7

it’s not a bug, not on grafana side at least

is your date and time UTC or some local time?

8

I tried both.

9

try this

timestamp(date,time)

but if the date and time combination is not utc the above is useless

select timestamp(datum,uhrzeit) as [time], 
tempartur as value
  from kumbuccha