How to add Time series queries with grafana and MySQL?

Grafana Time Series Panel
1

Hi,

I’m new to grafana and playing around to see if it could fit my needs for a research lab.

I’m using grafana-server Version 4.5.2 (commit: ec2b0fe)

I have succefully added a MySQL data source.

Here is my database :

mysql> DESC meteo;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(100) | NO   | PRI | NULL    | auto_increment |
| date_insert | datetime | NO   |     | NULL    |                |
| temperature | float    | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

Following the documentation I’ve added a panel “Table” with the following query…

SELECT
  date_insert as 'Date',
  temperature as 'Temperature'
 FROM meteo

…and choosen “Format as Table”

The result is ok as you can see.

Grafana Panel Format Table

Now I would like to have a graph like this :

Grafana Panel Format Time series

How can I achieve this with my database ? I don’t understand the doc which says :

If you set Format as to Time series, for use in Graph panel for example,

then there are some requirements for what your query returns.

Must be a column named time_sec representing a unix epoch in seconds.

Must be a column named value representing the time series value.

Must be a column named metric representing the time series name.

How can I apply this with my database ? Is it just possible ?

Original post : See Stackoverflow

2

There are lots of examples on the docs page for MySQL: MySQL data source | Grafana documentation

This will show raw time series data (time series is a list of datetime+value pairs):

SELECT
  UNIX_TIMESTAMP(date_insert) as time_sec,
  temperature as value,
  'temperature' as metric
 FROM meteo
WHERE $__timeFilter(date_insert)
ORDER BY date_insert ASC

If you want to group by time (group by hour or day for example), here is one way (there is another example in the docs that uses DIV):

SELECT
  MIN(UNIX_TIMESTAMP(date_insert)) as time_sec,
  avg(temperature) as value
FROM meteo
WHERE $__timeFilter(date_insert)
GROUP BY date_format(date_insert, $interval)
ORDER BY date_format(date_insert, $interval) ASC

$interval is a template variable that allows you to switch from grouping by minute, hour etc. And looks like this:

image
image1261×849 51.6 KB

This is the query in the above screenshot:

select 'minute' AS __text, '%Y%m%d%H%i' as __value union select 'hour' AS __text, '%Y%m%d%H' as __value union select 'day' AS __text, '%Y%m%d' as __value union select 'month' AS __text, '%Y%m' as __value

Hope that these examples are clear. Any tips on how we can keep it short but explain the column names better in the Help section for the MySQL data source?

3 Likes
3

Hi,

Thank you so much, your answer is very clear and everything works fine now ! I can now go further with Grafana. Even if I’ll probably come back on this forum for new questions :wink:

Grafana’s doc is very clear and well organized (no problem for me to install grafana on Ubuntu Server, add MySQL data source and create a table panel following the doc). I’m just not very familiar with this kind of stuff (SQL querys). I’ve understood your answer because it was about my particular database.
So maybe you could also give a “real” database structure before showing examples using this database. But this could also confuse some people…

Anyway you do a very good job and the forum is also very helpful, thanks again.

1 Like
4

Hi Daniellee,

Can i know how to use epoch time of mysql db in grafana ??
because epoch time is stored as int in mysql db .
when i use it , grafana is converting it into microseconds .

It throws error saying " Data miss match" or no data on graph .

5

Can you show your query and the macro or function you are using (and any relevant information about the mysql db schema)?

I just tested with an int: 1513328205 of type int(11) and my query looked like this:

SELECT 
    UNIX_TIMESTAMP(from_unixtime(aint)) AS time_sec,
    along AS value,
    'test' AS metric
  FROM testdata.test
where from_unixtime(aint) >= $__timeFrom() AND from_unixtime(aint) <= $__timeTo()

But it can probably be done more simply.

1 Like
6

Hi Daniellee,

Thanks for quick update.
I tried it but it throws error “Found row with no time value”

SELECT UNIX_TIMESTAMP(from_unixtime(time_sec)) AS time_sec,
entity_count as value,
“Customer Affected” as metric
FROM customerAffected;

Time_Sec is Bigint type ;

so i tried this solution

SELECT cast((time_sec*.001) as signed integer )as time_sec,
entity_count as value,
“Customer Affected” as metric
FROM customerAffected;

Its working but there is error in solution as we are multiplying with (.001)

7 
CREATE TABLE ` customerAffected` (
`time_sec` bigint(20) NOT NULL,
`entity_count` int(11) NOT NULL,
PRIMARY KEY (`time_sec`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8

You don’t say anywhere in your questions which time unit you are using? Nanoseconds?

And do you need to be able to zoom in to that level in Grafana? Grafana only supports showing down to microseconds.

9

Epoch time stamp is already in microseconds , Now Grafana is converting it to nano-seconds .
When i n try to do this
"cast((time_sec*.001) as signed integer )as time_sec"
There is alot of mismatch due to mathematical operations

10

You’ve totally lost me - I don’t understand.

Can you give an example of one of your epoch timestamps that have in the customerAffected table.

11

1512645007961,
1512693184852,
1512693853743

Few example of epoch time which we are using in grafana.

12

But that is in milliseconds and not micro or nano seconds :confused:

I keep getting mixed up between Postgres and MySQL. Unfortunately, the MySQL data source only has support for timestamps in seconds (which is why the field alias is time_sec). So your solution with dividing by 1000 is the best you can do currently and that means you lose the millisecond precision.

Once we upgrade the mysql golang driver (it got updated with types support recently), then we will be able to support any datetime format like the Postgres data source does. I will be doing that in January.

1 Like
13

Thank you for the response daniellee.
once Mysql is upgraded with this feature .Kindly update the post .

Regards,
Rashmi

14

Hi Daniellee,

Can i know , is new grafana has been updated to support mysql with microseconds details .

15

The upgrade to the MySQL driver is in the latest beta which means it is possible to do add this feature now but I have not yet got around to supporting microseconds yet. Might have time before the stable release but it is more likely that it will be in Grafana 5.1.

16

This worked only when i included $interval within quotes like below
GROUP BY date_format(date_insert, ‘$interval’)
ORDER BY date_format(date_insert, ‘$interval’) ASC

Mine is MariaDB which is mostly like MYSQL syntax

17

Hello Daniellee,

I use following sql:

select week, sum(a.downtime) as downtime from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, b.downtime, b.totaltime from server_report as b) as a
group by week

and it gives me following outputs.

week | downtime

W1 | 1645 |

W2 | 52013 |

W3 | 4647 |

W4 | 202658 |

W5 | 720 |

W6 | 440238 |

W7 | 239 |

W8 | 400667 |

I want to graphs week/downtime; time is week.

Thaks for help

18

Use the $timeGroup macro function with 7 days (converted to hours).

Marcus

19

I have only week data for hosts. There is no daily data on database. I can select following data and try to graphs thats. Downtime is value and week is time

week | downtime
W1 | 1645 |
W2 | 52013 |
W3 | 4647 |
W4 | 202658 |
W5 | 720 |
W6 | 440238 |
W7 | 239 |
W8 | 400667 |

20

Then you need to convert week to datetime.

Marcus