Hi Team Grafana,
Tried to get the grafana graph with below mysql query and also FROM and TO time dynamic based on query, but graph is only populating current last 1 hour graph with response “Data Point outside time range” But in Query inspector data is showing, Pls help me get the correct graph, Thanks:
Query :
select UNIX_TIMESTAMP(pst.createdate) as time_sec, count(pst.pstpotrfnum) as value , count(pst.pstpotrfnum) as metric
from (select CONCAT(Air_Date,’ ‘,Slot_Start_Time) START_TIME,
DATE_ADD(CONCAT(Air_Date,’ ',Slot_end_Time), INTERVAL 15 MINUTE) as ENDTIME
from bi_projected_show_slot_master_discrete
where Air_Date="$SELECT_DATE" and Air_Partner="$SELECT_PLATFORM"
and Product_Id="$SELECT_PRODUCT" and ShowDuration="$SELECT_SHOW") as tview, octapil pil
inner join vendor_item_sku vis on pil.PILRFNUM = vis.pil_id
inner join vendor_item_sku_location visl on visl.vendor_item_sku_id=vis.id
inner join octapst pst on pst.vendor_item_sku_location_id=visl.id
where pil.PILPBIRFNUM="$SELECT_PRODUCT"
and pst.CREATEDATE >= tview.START_TIME
and pst.CREATEDATE <= tview.ENDTIME group by pst.pstpotrfnum
Query Inspector :
{
“xhrStatus”: “complete”,
“request”: {
“method”: “POST”,
“url”: “api/tsdb/query”,
“data”: {
“from”: “1520101800000”,
“to”: “1520706599999”,
“queries”: [
{
“refId”: “A”,
“intervalMs”: 900000,
“maxDataPoints”: 720,
“datasourceId”: 1,
“rawSql”: “select UNIX_TIMESTAMP(pst.createdate) as time_sec, count(pst.pstpotrfnum) as value , count(pst.pstpotrfnum) as metric\n from (select CONCAT(Air_Date,’ ‘,Slot_Start_Time) START_TIME,\n DATE_ADD(CONCAT(Air_Date,’ ',Slot_end_Time), INTERVAL 15 MINUTE) as ENDTIME\n from bi_projected_show_slot_master_discrete\n where Air_Date=“2018-03-06” and Air_Partner=“Ezmall.com”\n and Product_Id=“1087” and ShowDuration=“09:15 - 09:30”) as tview, octapil pil\n inner join vendor_item_sku vis on pil.PILRFNUM = vis.pil_id\n inner join vendor_item_sku_location visl on visl.vendor_item_sku_id=vis.id\n inner join octapst pst on pst.vendor_item_sku_location_id=visl.id\n where pil.PILPBIRFNUM=“1087”\n and pst.CREATEDATE >= tview.START_TIME\n and pst.CREATEDATE <= tview.ENDTIME group by pst.pstpotrfnum”,
“format”: “time_series”
}
]
}
},
“response”: {
“results”: {
“A”: {
“refId”: “A”,
“meta”: {
“rowCount”: 6,
“sql”: “select UNIX_TIMESTAMP(pst.createdate) as time_sec, count(pst.pstpotrfnum) as value , count(pst.pstpotrfnum) as metric\n from (select CONCAT(Air_Date,’ ‘,Slot_Start_Time) START_TIME,\n DATE_ADD(CONCAT(Air_Date,’ ',Slot_end_Time), INTERVAL 15 MINUTE) as ENDTIME\n from bi_projected_show_slot_master_discrete\n where Air_Date=“2018-03-06” and Air_Partner=“Ezmall.com”\n and Product_Id=“1087” and ShowDuration=“09:15 - 09:30”) as tview, octapil pil\n inner join vendor_item_sku vis on pil.PILRFNUM = vis.pil_id\n inner join vendor_item_sku_location visl on visl.vendor_item_sku_id=vis.id\n inner join octapst pst on pst.vendor_item_sku_location_id=visl.id\n where pil.PILPBIRFNUM=“1087”\n and pst.CREATEDATE >= tview.START_TIME\n and pst.CREATEDATE <= tview.ENDTIME group by pst.pstpotrfnum”
},
“series”: [
{
“name”: “1”,
“points”: [
[
1,
1520308593000
],
[
1,
1520308812000
],
[
1,
1520309075000
],
[
1,
1520309093000
],
[
1,
1520309206000
],
[
1,
1520309430000
]
]
}
],
“tables”: null
}
}
}
}