Compatible with MySQL?

[mattbue]

Hi, is this plugin compatible with MySQL/MariaDB?

[marcusolsson]

I haven't tried it myself, but it works with the PostgreSQL data source, so I'm pretty sure it works with MySQL as well. Let me know if it doesn't and I'll fix it.

Your SQL query needs to return:

• A text column (two if you want to group the results)
• Two time columns (start time, end time)

It's important that you set it to return a Table instead of Time series.

Let me know how it goes!

[mattbue]

Tried it using the following database table 't_gantt_test':

Table content:

My query looks like this:

SELECT
  UNIX_TIMESTAMP(Start) AS "time",
  UNIX_TIMESTAMP(Ende) AS "time2",
  Name
FROM t_gantt_test

The query result looks like this:

The chart looks like this:

The "bars" are only a line on the start time. Not from start to end time. I'm wondering why the number of digits of unix start and end time are different...

[marcusolsson]

I think it's because time is a special keyword for the MySQL data source. If the SQL result contains a column called time, it's treated differently. Judging by your screenshot, it looks like Grafana converts it to milliseconds.

Maybe try something like:

SELECT
  UNIX_TIMESTAMP(Start) * 1000 as "start",
  UNIX_TIMESTAMP(Ende) * 1000 as "end",
  Name
FROM t_gantt_test

[mattbue]

Using the following query

SELECT
  UNIX_TIMESTAMP(Start) * 1000 as "start",
  UNIX_TIMESTAMP(Ende) * 1000 as "end",
  Name
FROM t_gantt_test

gives a plausible data table

But i cannot select the two time columns for start time and end time.

Changing the order of the columns in the query to

SELECT
  Name,
  UNIX_TIMESTAMP(Start) * 1000 as "start",
  UNIX_TIMESTAMP(Ende) * 1000 as "end"
FROM t_gantt_test

doesn't solve it. Since the name column doesn't have an alias and it can be choosen i gave it an alias to check if this is the cause at the other columns:

SELECT
  Name as "my_name",
  UNIX_TIMESTAMP(Start) * 1000 as "start",
  UNIX_TIMESTAMP(Ende) * 1000 as "end"
FROM t_gantt_test

Than i still can choose the column "my_name", but not the other two columns.

[marcusolsson]

Hm. Looks like the data source returns the timestamps as number fields rather than time fields. I'm looking at the documentation for table queries, and it looks like you can return the date column as is without converting to UNIX_TIMESTAMP.

https://grafana.com/docs/grafana/latest/datasources/mysql/#table-queries

If that doesn't do it for you, I'll be able to troubleshoot this weekend. If the MySQL data source doesn't supported time fields yet (it's one of the oldest data sources), I'll add an option to convert from number fields.

Thanks for trying this out!

[mattbue]

Using

SELECT
  Start as "start",
  Ende as "end",
  Name as "my_name"
FROM t_gantt_test

I still cannot choose the time colums.

[marcusolsson]

Thank you for letting me know! 🙏 I'll dig into this during the weekend and get back to you.

[marcusolsson]

Indeed the MySQL data source only seems to be able to handle one time field at the most. I'm thinking we could let users select strings and number fields for start and end time and then convert them to time fields in the plugin. I'll try to get to this in the next couple of days.

[mattbue]

Ok. Let me know if you have something i shall test.

[vaughanbrittonsage]

@marcusolsson I seem to be having a similar issue with using an Elasticsearch datasource. It would be great if we could select any field from the datasource and have it attempt to convert it within the plugin.

https://github.com/marcusolsson/grafana-gantt-panel/issues/2

[marcusolsson]

I've published v0.2.0 which allows you to use string and number fields as time. Hopefully that will solve your issue, otherwise let me know!

[vaughanbrittonsage]

Brilliant, thanks I'll take a look

[mattbue]

Thanks for your work. Tomorrow i will test it and let you know.