Time stamped recordings 10 seconds part
I have some SQL data in a table with MySQL and below is what it looks like when I export using MySQL WorkBench.
I saw a number of lines that weren't within 10 seconds of each other and tried to apply it accordingly here, but I couldn't easily figure it out so I'm posting as a question for some help.
Initial data
This is the data I am running in the table
Column headers
TimeInt, TimeStr, IsInItValue, Value, IQuality
Data
'1495542477', '2017-05-23 12:27:57', '0', '0', '3'
'1495542475', '2017-05-23 12:27:55', '0', '1', '3'
'1495542474', '2017-05-23 12:27:54', '0', '3', '3'
'1495542473', '2017-05-23 12:27:53', '0', '4', '3'
'1495542472', '2017-05-23 12:27:52', '0', '5', '3'
'1495542471', '2017-05-23 12:27:51', '0', '4', '3'
'1495542470', '2017-05-23 12:27:50', '0', '3', '3'
'1495447612', '2017-05-22 10:06:52', '0', '1', '3'
'1495447611', '2017-05-22 10:06:51', '0', '2', '3'
'1494851001', '2017-05-15 12:23:21', '0', '2', '3'
'1493819613', '2017-05-03 13:53:33', '0', '0', '3'
'1493819612', '2017-05-03 13:53:32', '0', '1', '3'
'1493819611', '2017-05-03 13:53:31', '0', '2', '3'
'1493819609', '2017-05-03 13:53:29', '0', '4', '3'
'1493819608', '2017-05-03 13:53:28', '0', '6', '3'
'1493819607', '2017-05-03 13:53:27', '0', '5', '3'
'1493819606', '2017-05-03 13:53:26', '0', '4', '3'
'1493819605', '2017-05-03 13:53:25', '0', '2', '3'
'1493819603', '2017-05-03 13:53:23', '0', '1', '3'
I would like to see if there is a way to attach this data to myself or apply some logic with a subquery, etc. so that I can return values or easily distinguish data where the timestamp for the very next row in this table is longer than 10 seconds.
The method from one of the answers to the post I linked above uses a method where value entries NULL
would be helpful, but I couldn't get it to work with MySQL.
I tried a variation on the answer for the number of lines that were not within 10 seconds of each other , but couldn't figure out where I was going wrong, but below one of the few things I've tried. I'm not sure what interval
is still valid since 2011, or if I'm doing something else wrong to get syntax errors, but this is one of the things I've tried.
SELECT t2.TimeStr, Value,
(SELECT MAX(t.TimeStr)
FROM canouncebit t
WHERE t.TimeStr > t2.TimeStr
AND t.TimeStr - t2.TimeStr <= interval '10' second) NextRecord
FROM canouncebit t2
ORDER BY TimeStr
Expected data (or something close enough)
I would like to get something like this, or even close a little. I can use
Data
'1495542477', '2017-05-23 12:27:57', '0', '0', '3'
'1495447612', '2017-05-22 10:06:52', '0', '1', '3'
'1494851001', '2017-05-15 12:23:21', '0', '2', '3'
'1493819613', '2017-05-03 13:53:33', '0', '0', '3'
So basically I only need to return data and value, in particular only for records where this value has been set for more than X
seconds (10 in this case).
Create table statement
CREATE TABLE `ContainerOzBit` (
`TimeInt` varchar(10) NOT NULL,
`TimeStr` datetime NOT NULL,
`IsInitValue` int(11) NOT NULL,
`Value` float NOT NULL,
`IQuality` int(11) NOT NULL,
UNIQUE KEY `TimeInt` (`TimeInt`,`TimeStr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
Insert Data Report
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542477','2017-05-23 12:27:57',0,0,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542475','2017-05-23 12:27:55',0,1,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542474','2017-05-23 12:27:54',0,3,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542473','2017-05-23 12:27:53',0,4,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542472','2017-05-23 12:27:52',0,5,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542471','2017-05-23 12:27:51',0,4,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495542470','2017-05-23 12:27:50',0,3,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495447612','2017-05-22 10:06:52',0,1,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1495447611','2017-05-22 10:06:51',0,2,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1494851001','2017-05-15 12:23:21',0,2,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819613','2017-05-03 13:53:33',0,0,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819612','2017-05-03 13:53:32',0,1,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819611','2017-05-03 13:53:31',0,2,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819609','2017-05-03 13:53:29',0,4,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819608','2017-05-03 13:53:28',0,6,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819607','2017-05-03 13:53:27',0,5,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819606','2017-05-03 13:53:26',0,4,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819605','2017-05-03 13:53:25',0,2,3);
INSERT INTO `ContainerOzBit` (`TimeInt`,`TimeStr`,`IsInitValue`,`Value`,`IQuality`) VALUES ('1493819603','2017-05-03 13:53:23',0,1,3);
source to share
One approach would be to use MySQL session variables:
-- omit the duplicated timeint that we used in the derived table
SELECT c.* FROM (
-- grab the maximum timeint value in the group
SELECT MAX(timeint) timeint FROM (
SELECT timeint, -- current row timeint value
-- if diff betwen current and prev values more than 10 sec
-- increment the group number, otherwise keep it the same
@g := IF(timeint - @p > 10, @g + 1, @g) g,
-- preserve the the value so it available on the next iteration
@p := timeint
FROM ContainerOzBit CROSS JOIN (
SELECT @p := NULL, @g := 1 -- initialize sesion variables
) i
-- deterministic order is crucial for this approach
-- since we're iteration row by row
ORDER BY timeint
) q
-- group by the group number
GROUP BY g
-- since timeint values are unique
-- join back and retrieve all the columns
) r JOIN ContainerOzBit c
ON r.timeint = c.timeint
-- set the reverse order for the result set
ORDER BY timeint DESC;
Reading comments from the innermost choice coming out
Result:
+ ------------ + --------------------- + ------------- + ------- + ---------- + | TimeInt | TimeStr | IsInitValue | Value | IQuality | + ------------ + --------------------- + ------------- + ------- + ---------- + | 1495542477 | 2017-05-23 12:27:57 | 0 | 0 | 3 | | 1495447612 | 2017-05-22 10:06:52 | 0 | 1 | 3 | | 1494851001 | 2017-05-15 12:23:21 | 0 | 2 | 3 | | 1493819613 | 2017-05-03 13:53:33 | 0 | 0 | 3 | + ------------ + --------------------- + ------------- + ------- + ---------- +
Here is a dbfiddle demo
source to share