Timefilter/timegroup query data from MySQl

Grafana Time Series Panel
,
1

I have three shift of time which is 12am-8am, 8am-4pm and 4pm-12am
I try to visualize the data in three different gauge the represent respective time range of each day, however it doesn’t look like expected, do anyone has do similar panel and give some advice?
I tries many ways, it doesn’t show the correct result or it will be empty in the gauge…

advice is appreciated.

2

hello can you share a sample of your data and query ?
what kind of information you are trying to display over time period ?

3

Please provide DML and DDL as follows so it helps us help you

image
image863×1200 98.6 KB

DROP TABLE IF EXISTS `grafana`.`shifts`;

CREATE TABLE `grafana`.`shifts` (
  `shiftid` INT NOT NULL,
  `shiftname` VARCHAR(45) NULL,
  `shiftstart` time NULL,
  `shiftend` time NULL,
  PRIMARY KEY (`shiftid`));

INSERT INTO `grafana`.`shifts`
(`shiftid`,
`shiftname`,
`shiftstart`,
`shiftend`)
VALUES
(1,
'12am to 8am',
'12:00:00',
'08:00:00'),
(2,
'8am to 4pm',
'08:00:00',
'04:00:00'),
(3,
'4pm to 12am',
'04:00:00',
'12:00:00');

DROP TABLE IF EXISTS  `grafana`.`production`;

CREATE TABLE `grafana`.`production` (
  `productionid` INT NOT NULL,
  `shiftid` INT NOT NULL,
  `widgets` VARCHAR(45) NULL,
  `widgetsproduced` int NULL,
  `productiondate` datetime,
  PRIMARY KEY (`productionid`));

INSERT INTO `grafana`.`production`
(`productionid`,
 `shiftid`,
`widgets`,
`widgetsproduced`,
 `productiondate`)
VALUES
(1,
1,
'headphones',
34,
'2022-08-24 14:30:00');


select shiftname, shiftstart, shiftend,widgets,widgetsproduced, productiondate
  from shifts s
  join production p on s.shiftid = p.shiftid
1 Like
4

image
image877×207 36.3 KB

To show no of cuts of the machine based on shift, I try to query after the where clause but not able to do it

SELECT
   $__timeGroupAlias(_TIMESTAMP,5s,0),
   _VALUE AS "OSCAM"
FROM rms
WHERE 
  $__timeFilter(_TIMESTAMP) AND
  _NAME = 'CnB Prai Gateway.OSCAM.Sensor0_Voltage' AND
  _VALUE = 1
GROUP BY TIME_FORMAT(_TIMESTAMP, "%H:%i")
ORDER BY $__timeGroup(_TIMESTAMP,5s,0)
5

I used the following code:

SELECT
   $__timeGroupAlias(_TIMESTAMP,5s,0),
   _VALUE AS "OSCAM"
FROM rms
WHERE 
  $__timeFilter(_TIMESTAMP) AND
  _NAME = 'CnB Prai Gateway.OSCAM.Sensor0_Voltage' AND
  _VALUE = 1
GROUP BY TIME_FORMAT(_TIMESTAMP, "%H:%i")
ORDER BY $__timeGroup(_TIMESTAMP,5s,0)
1 Like