Transformation of Unix Time Stamp - Global Setting Possible

Grafana MySQL
1

Hi Community,

I am quite to this forum so any help is really appreciated.

I am pulling data from MySQL db. The DB stores the timestamp in Unix Timestamp. I know I can use “Transformation” to convert to human readable but it looks like I have to do this on every Dashboard and Panel. Is there a way to set this on a global level so whenever I use the that DB column, it always converts it to human readable?

Thanks
Mustafa

2

Hi Mustafa,

You can use the from_unixtime() function in your queries, that way you are converting at source, and it will be correct on every dashboard and panel:

SELECT
from_unixtime(timestamp, ‘%Y %D %M %H:%i:%s’)
FROM
your_table

1 Like
3

Hi @sowdenraymond, but I have to do this in every query correct?

4

If you use views or stored procedures you only need to change it in one place

5

Hello,

I’m stuck on a problem, unless there’s a known error: I have a +2hour time lag in Grafana.

I’m using a MySQL database, and when I type this query:

$SELECT FROM_UNIXTIME(tstamp) as datetime_value, outdoortemperature as Temperature FROM DATA order by datetime_value DESC

From PHPMYADMIN, I have no problem.

However, the same query in Grafana in my dashboard, the values ​​of ‘datetime_value’ are offset by +2 hours.
I don’t understand how to correct this.
My SQL query is still OK.
How do I proceed in Grafana?
What SQL query should I type to get the time OK in Grafana?

Thanks for your support

6

@cmpellegrinelli

plugin your timezone where I have US/Eastern

`convert_tz(tstamp,‘US/Eastern’,‘UTC’)

and see if that helps. grafana needs the time to be utc

7

Hello
Thank you for your reply, but can you tell me exactly where I should perform these steps?
All from Grafana?
Currently, in Grafana, the timezone is set to ‘browser’ in the configuration file.
Do I need to change my SQL query?
Thanks

8

Hello,
Could someone provide me with some support?
I’ve been looking for a solution to these offsets for days. After running other tests using UTC from Grafana, the table view does indeed contain the correct times and dates, and the graph also displays the correct time on the X axis. However, if I hover over a point on the graph, I’m 2 hours behind!
It’s incomprehensible.

SELECT FROM_UNIXTIME(tstamp) as temps, outdoortemperature as Température FROM DATA order by temps DESC limit 288

Thanks