Actually I want to bar graph for week use with this sql. Can I do that?. I think it may be easy but cannot do it. I have two parameter. weeks and downtime values.
Like as below;
Grafana graph panel only works with time-series data and therefore you need to return a timestamp in epoch format for Grafana to be able to plot a point/bar on the graph.
Just googled “mysql convert week number to date” and the first hit looks promising 
Good luck
Marcus
Thanks for help.
Adding datestamp on my table and now my output is:
|2018-01-08 | 539 |
|2018-01-15 | 5921 |
|2018-01-22 | 4647 |
|2018-01-29 | 633 |
|2018-02-05 | 720 |
|2018-02-12 | 2538 |
|2018-02-19 | 239 |
|2018-02-26 | 1022 |
Getting following error:
MySQL response parsing error sql: Scan error on column index 0: unsupported Scan, storing driver.Value type time.Time into type *sql.RawBytes
1 Like
My sql is:
select cast(datestamp as date), sum(a.downtime) as downtime from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, b.datestamp, b.downtime, b.totaltime from server_report as b) as a
group by week
Finally I did it with following sql 
select week as metric, UNIX_TIMESTAMP(cast(datestamp as date)) as time_sec, sum(a.downtime) as value from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, b.datestamp, b.downtime, b.totaltime from server_report as b) as a
group by week
Thanks for help.
1 Like
I try to remove “Data points outside time range” statement from graphs.
Adding $__timeFilter() but may be it does not support “cast(datestamp as date)”
select week as metric, UNIX_TIMESTAMP(cast(datestamp as date)) as time_sec, sum(a.downtime) as value from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, b.datestamp, b.downtime, b.totaltime from server_report as b) as a
where year=‘2018’ and $__timeFilter(cast(datestamp as date)) group by week
but getting following error:
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>= FROM_UNIXTIME(1519497403) AND cast(datestamp as date <= FROM_UNIXTIME(1519804’ at line 3
Does it do any difference with $__timeFilter((cast(datestamp as date)))?
No, stil getting same error.
select week as metric, UNIX_TIMESTAMP(cast(datestamp as date)) as time_sec, sum(a.downtime) as value from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, b.datestamp, b.downtime, b.totaltime from server_report as b) as a
where year=‘2018’ and $__timeFilter((cast(datestamp as date))) group by week
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>= FROM_UNIXTIME(1518588427) AND (cast(datestamp as date <= FROM_UNIXTIME(151981’ at line 3
Does this work better?
select
week as metric, UNIX_TIMESTAMP(datestamp) as time_sec, sum(a.downtime) as value from
(
select distinct b.site, b.environment, b.hostname, b.year, b.week, cast(b.datestamp as date) as datestamp, b.downtime, b.totaltime from server_report as b) as a
where year=‘2018’ and $__timeFilter(datestamp) group by week
Marcus
Actually, following sql is working.
select week as metric, UNIX_TIMESTAMP(datestamp) as time_sec, sum(a.downtime) as value from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, cast(b.datestamp as date) as datestamp, b.downtime, b.totaltime from server_report as b) as a
where year=‘2018’ group by week
But when i add $__timeFilter(datestamp) as you sent. it seems wrong.
Please try my query with a greater time range? Do you get an error with my latest query?
Marcus,
Sorry, I think there is zoom in problem. It is working now.
select week as metric, UNIX_TIMESTAMP(datestamp) as time_sec, sum(a.downtime) as value from
(select distinct b.site, b.environment, b.hostname, b.year, b.week, cast(b.datestamp as date) as datestamp, b.downtime, b.totaltime from server_report as b) as a
where $__timeFilter(datestamp) group by week
Great work. Thanks a lot :grinning 
1 Like
hi, I am new ,i use mysql database, want to create a graph ,but, my data doesn’t have time column.so ,what should i do?
thx
Add dummy dates or something. May be that your data is not good use case for visualizing as graph in Grafana.
Marcus
ok 。thanks a lot:grinning:
We group all the calls per month with this query below.
Nós agrupamos todos os chamados por mês com esta consulta abaixo.
SELECT
UNIX_TIMESTAMP(date) as time_sec,
CASE
WHEN month(date) = 1 THEN ‘Janeiro’
WHEN month(date) = 2 THEN ‘Fevereiro’
WHEN month(date) = 3 THEN ‘Março’
WHEN month(date) = 4 THEN ‘Abril’
WHEN month(date) = 5 THEN ‘Maio’
WHEN month(date) = 6 THEN ‘Junho’
WHEN month(date) = 7 THEN ‘Julho’
WHEN month(date) = 8 THEN ‘Agosto’
WHEN month(date) = 9 THEN ‘Setembro’
WHEN month(date) = 10 THEN ‘Outubro’
WHEN month(date) = 11 THEN ‘Novembro’
WHEN month(date) = 12 THEN ‘Dezembro’
ELSE ‘Indefinido’
END as metric,
count(month(date)) as value
FROM glpi_tickets
GROUP BY 1,2
got the exact same error. could you guide me with this, please? would like some updates and if you have ruled it out.
Hello I tried write in Metrics box next script^
select
“OpCo” as metric,
UNIX_TIMESTAMP(cast(“Datestamp” as date)) as time_sec,
"Web1"as value
from
public.“Test”
where $__timeFilter(“Datestamp”)
And i had error: pq: function unix_timestamp(date) does not exist
Could u explain me why?