CODEWITHSUNDEEP
mysqlgrafana
JYC
JYC

Reputation: 3

Grafana dashboard using MySQL: how should I deal with timestamp of "%Y%m%d%H%i%s" to use as time column of panel?

I want to visualize my MySQL DB table to an hourly basis graph using Grafana dashboard. The table that I'm working with has the attributes below, with unused ones including PK not mentioned:

SERVER_NAME varchar(250)
STAT_TYPE int(11) 
STAT_DTM varchar(14) 
CPU_MAX_USAGE int(11) 
MEMORY_MAX_USAGE int(11)

What matters is STAT_DTM. Its format is "%Y%m%d%H%i%s", e.g. "20210621090000"; for 09:00:00 of June 21st 2021. I want this to be the X axis of the graph. Grafana guide says:

return column named time or time_sec (in UTC), as a unix time stamp or any sql native date data type. You can use the macros below.

So I put unix_timestamp(date_format(str_to_date(substr(stat_dtm, 1, 10),'%Y%m%d%H'), '%Y-%m-%d %H:00:00')) but an error saying db query error: query failed - please inspect Grafana server log for details popped up.

select
    unix_timestamp(date_format(str_to_date(substr(stat_dtm, 1, 10),'%Y%m%d%H'), '%Y-%m-%d %H:00:00')) as 'time',
    CPU_MAX_USAGE,
    MEMORY_MAX_USAGE
from lcop.tb_stat_os_day
where stat_type = 60 and server_name = 'LDFSWAS1'

The Panel I'm currently working on

The result of the query above

How can I set the timestamp correctly and show the graph? The table schema cannot be modified unfortunately and I can give any additional info if needed. Thanks in advance.

Upvotes: 0

Views: 2266

Answers (1)

matigo
matigo

Reputation: 1461

Let's simplify your type conversion there:

SELECT '20210621090000' as `src`,
       UNIX_TIMESTAMP(STR_TO_DATE('20210621090000', '%Y%m%d%H%i%s')) as `dts`

The STR_TO_DATE() function can be given the full format, which can then be given to UNIX_TIMESTAMP. There is no need to make things more difficult with SUBSTR() or DATE_FORMAT() 👍🏻

Upvotes: 1

Related Questions