Hello guys,
I’m currently working on a gantt chart. I have insert my data using SQL query but the results are not showing on the panel.
Here’s my SQL Query:
SELECT
lightColour,
DATEDIFF(s, timeStarted, GETDATE()) as StartTime,
DATEDIFF(s, timeEnded, GETDATE()) as EndTime
FROM tb_Events
Here’s the table view of my data:
The data is Formatted as “Table”.
Am I missing something?
Thank you in advance for any help.
Thank you thank you,
Kevin.
Hello
You need a numeric value to plot on the gantt?
SELECT lightColour,
DATEDIFF(s, timeStarted, GETDATE()) as StartTime,
DATEDIFF(s, timeEnded, GETDATE()) as EndTime,
numero as value --->somenumber column here
FROM tb_Events
Also what is the value of timeStarted?
Hello Mr Yosiasz,
Thank you very much for the quick response.
May I know what’s the number column for ?
Here’s the value of my table.
Hello Mr Kevin
Please post your data not as an image but inline csv text?
id,lightID, state,timeStarted,timeEnded,remark,lightColour
1,1,1,2022-06-30 10:08:20.000,
etc
Sure thing.
Here’s the data inline
ID,lightID,state,timeStarted,timeEnded,remark,lightColour
1,1,1,2022-06-30 10:08:20.000,2022-06-30 10:38:20.000,NULL,Red
6,1,1,2022-06-30 11:28:20.000,2022-06-30 11:48:20.000,NULL,Red
7,2,2,2022-06-30 10:38:20.000,2022-06-30 11:28:20.000,NULL,Green
8,3,1,2022-06-30 11:48:20.000,2022-06-30 12:18:20.000,NULL,Blue
9,3,1,2022-06-30 11:48:20.000,2022-06-30 12:18:20.000,NULL,Blue
Yes sir! This is exactly what I needed ! Can you please share the code please. Thank you thank you !
you will need to clean it up a bit as it has other visualization
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 36,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "mssql",
"uid": "u6VEWIC7k"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [
{
"options": {
"1": {
"color": "dark-blue",
"index": 1
},
"4": {
"color": "dark-red",
"index": 0
}
},
"type": "value"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 12,
"x": 0,
"y": 0
},
"id": 2,
"options": {
"colorByField": "value",
"endField": "enddate",
"experiments": {
"enabled": true,
"lockToExtents": false,
"relativeXAxis": false
},
"showYAxis": true,
"sortBy": "startTime",
"sortOrder": "asc",
"startField": "startdate",
"textField": "metric"
},
"targets": [
{
"alias": "",
"columns": [],
"datasource": {
"type": "mssql",
"uid": "u6VEWIC7k"
},
"filters": [],
"format": "table",
"global_query_id": "",
"rawSql": "select GETDATE() startdate, dateadd(hh, 5, getdate()) enddate, 'Wonk' as metric, 4 as value union\nselect GETDATE() startdate, dateadd(dd, 5, getdate()) enddate, 'Wink' as metric, 1 as value ",
"refId": "A",
"root_selector": "",
"source": "url",
"type": "json",
"url": "https://jsonplaceholder.typicode.com/users",
"url_options": {
"data": "",
"method": "GET"
}
}
],
"title": "Gantt Colors",
"type": "marcusolsson-gantt-panel"
},
{
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "UB0gLMq7k"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [
{
"options": {
"1": {
"color": "dark-red",
"index": 0
},
"2": {
"color": "dark-green",
"index": 2
},
"3": {
"color": "dark-blue",
"index": 1
}
},
"type": "value"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 12,
"x": 0,
"y": 9
},
"id": 3,
"options": {
"colorByField": "value",
"endField": "timeEnded",
"experiments": {
"enabled": true,
"lockToExtents": true,
"relativeXAxis": false
},
"showYAxis": true,
"sortBy": "startTime",
"sortOrder": "asc",
"startField": "timeStarted",
"textField": "lightColour"
},
"targets": [
{
"columns": [
{
"selector": "ID",
"text": "",
"type": "number"
},
{
"selector": "lightID",
"text": "value",
"type": "number"
},
{
"selector": "state",
"text": "",
"type": "number"
},
{
"selector": "timeStarted",
"text": "",
"type": "timestamp"
},
{
"selector": "timeEnded",
"text": "",
"type": "timestamp"
},
{
"selector": "remark",
"text": "",
"type": "string"
},
{
"selector": "lightColour",
"text": "",
"type": "string"
}
],
"data": "ID,lightID,state,timeStarted,timeEnded,remark,lightColour\n1,1,1,2022-06-30 10:08:20.000,2022-06-30 10:38:20.000,NULL,Red\n6,1,1,2022-06-30 11:28:20.000,2022-06-30 11:48:20.000,NULL,Red\n7,2,2,2022-06-30 10:38:20.000,2022-06-30 11:28:20.000,NULL,Green\n8,3,1,2022-06-30 11:48:20.000,2022-06-30 12:18:20.000,NULL,Blue\n9,3,1,2022-06-30 11:48:20.000,2022-06-30 12:18:20.000,NULL,Blue",
"datasource": {
"type": "yesoreyeram-infinity-datasource",
"uid": "UB0gLMq7k"
},
"filters": [],
"format": "table",
"global_query_id": "",
"refId": "A",
"root_selector": "",
"source": "inline",
"type": "csv",
"url": "https://jsonplaceholder.typicode.com/users",
"url_options": {
"data": "",
"method": "GET"
}
}
],
"title": "Gantt Colors",
"type": "marcusolsson-gantt-panel"
}
],
"refresh": "",
"schemaVersion": 36,
"style": "dark",
"tags": [],
"templating": {
"list": []
},
"time": {
"from": "now-6h",
"to": "now"
},
"timepicker": {},
"timezone": "",
"title": "Gantt",
"uid": "XuCzC63nz",
"version": 3,
"weekStart": ""
}
Thank you very much for the help kind sir ! I have successfully got it to work. You are my saviour ! TQ TQ.