I'm trying to generate a dynamic chart with a steady timeline, therefore I need to add additional time (minutes) to the x-axis. I need 2 columns. Column 1 (time) shows every minute of the day from a table which simply has a time column listing from 00:00 to 23:59 (the example table only has 20 mins). I'm trying to join this with the results from the 'log' table. The query will filter which machine 'name' I'm looking at, eg Machine 'name' '1'. Then list when this machine changed state alongside the 1440 minutes from the 'TimeMins' table. I hope that helps? Ultimately, I have a dynamic chart which shows the state changes for a machine throughout the whole day. The 'time' column will set the x-axis for the graph giving a steady timeline
Unfortunately I can't get it to join properly. Maybe there's another way or can someone crack the problem please?
The result should be similar to...
time state
00:00 null (or zero)
00:01 null
00:02 1
00:03 null
00:04 null
00:05 0
00:06 null
00:07 1
etc. A value for every minute of the day, whether there was a result in 'log' for that minute or not.
Very much appreciated. Have been on this for over 2 days.
Here is the SQLfiddle:
http://sqlfiddle.com/#!9/5f91a0/2
Thanks. Jamie.
You can use inner select.
In the example bellow I'm using 3.
select name, beginning, ending - beginning as seconds from (
select a.name, a.ts as beginning, (
select min(b.ts)
from log b
where b.ts > a.ts and a.name = b.name and b.state = 0
) as ending
from log a
where a.state = 1
) c
order by beginning;
2nd is providing the start and end for each machine
the 1st is wrapping the 2nd and it's just calculating seconds for each run and sorting the result by start
The 3rd is trying to find next row when machine stops after the a.ts started and state 0 with the same name (to match the same machine)
select min(b.ts)
from log b
where b.ts > a.ts and a.name = b.name and b.state = 0
I'm not advocating this as a solution (and even with this method, there are surely more optimal ways of writing this query), but just by way of illustration...
DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`ts` datetime NOT NULL,
`state` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `log` VALUES
(20,1,'2016-05-16 00:03:02',1),
(21,1,'2016-05-16 00:04:03',0),
(22,2,'2016-05-16 00:04:28',1),
(23,2,'2016-05-16 00:06:45',0),
(25,1,'2016-05-16 00:14:50',1),
(26,2,'2016-05-16 00:15:35',1);
DROP TABLE IF EXISTS `TimeMins`;
CREATE TABLE `TimeMins` (
t time PRIMARY KEY
);
INSERT INTO `TimeMins` VALUES
('00:00:00'),
('00:01:00'),
('00:02:00'),
('00:03:00'),
('00:04:00'),
('00:05:00'),
('00:06:00'),
('00:07:00'),
('00:08:00'),
('00:09:00'),
('00:10:00'),
('00:11:00'),
('00:12:00'),
('00:13:00'),
('00:14:00'),
('00:15:00'),
('00:16:00'),
('00:17:00'),
('00:18:00'),
('00:19:00'),
('00:20:00');
SELECT a.*
, b.id
, b.name
, b.state
FROM
( SELECT DISTINCT CONCAT(DATE(l.ts),' ',t.t) i
FROM log l
, timemins t
) a
LEFT
JOIN log b
ON DATE_FORMAT(b.ts,'%Y%m%d%h%i') = DATE_FORMAT(a.i,'%Y%m%d%h%i');
+---------------------+------+------+-------+
| i | id | name | state |
+---------------------+------+------+-------+
| 2016-05-16 00:00:00 | NULL | NULL | NULL |
| 2016-05-16 00:01:00 | NULL | NULL | NULL |
| 2016-05-16 00:02:00 | NULL | NULL | NULL |
| 2016-05-16 00:03:00 | 20 | 1 | 1 |
| 2016-05-16 00:04:00 | 21 | 1 | 0 |
| 2016-05-16 00:04:00 | 22 | 2 | 1 |
| 2016-05-16 00:05:00 | NULL | NULL | NULL |
| 2016-05-16 00:06:00 | 23 | 2 | 0 |
| 2016-05-16 00:07:00 | NULL | NULL | NULL |
| 2016-05-16 00:08:00 | NULL | NULL | NULL |
| 2016-05-16 00:09:00 | NULL | NULL | NULL |
| 2016-05-16 00:10:00 | NULL | NULL | NULL |
| 2016-05-16 00:11:00 | NULL | NULL | NULL |
| 2016-05-16 00:12:00 | NULL | NULL | NULL |
| 2016-05-16 00:13:00 | NULL | NULL | NULL |
| 2016-05-16 00:14:00 | 25 | 1 | 1 |
| 2016-05-16 00:15:00 | 26 | 2 | 1 |
| 2016-05-16 00:16:00 | NULL | NULL | NULL |
| 2016-05-16 00:17:00 | NULL | NULL | NULL |
| 2016-05-16 00:18:00 | NULL | NULL | NULL |
| 2016-05-16 00:19:00 | NULL | NULL | NULL |
| 2016-05-16 00:20:00 | NULL | NULL | NULL |
+---------------------+------+------+-------+