Bitcoin Murderous Maniac
Reputation: 1478
MySQL to select max value between on and off time stamps plus 3 seconds
There's a transporter inlet valve sensor which data is collected to a MySQL DB table showing the intake valve open and close cycles with the time stamps. There's also a live weight sensor which data is collected to a MySQL DB table from the correlated transporter showing the live weight value as it changes with a time stamp.
I've built the below simplified sample query against a small sample of this table data to show the data I need to further manipulate with another subquery, etc. to put the data in a temp table.
MySQL Query to Temp Table
CREATE TABLE TempBatch
SELECT TimeStr,
CASE
WHEN Value = 'On' THEN 'On'
WHEN Value = 'Off' THEN 'Off'
ELSE Null END Status,
CASE
WHEN Value = 'On' THEN Null
WHEN Value = 'Off' THEN Null
ELSE Value END Weight
FROM
(
SELECT TimeStr, If(Value = 1, 'Off', 'On') Value FROM transporter2_inlet_valve_closed WHERE Value = 1 OR Value = 0
UNION ALL
SELECT TimeStr, Value FROM transporter2_liveweight
) x
WHERE TimeStr BETWEEN '2019-06-29 18:51:00' AND DATE_ADD('2019-06-29 19:27:18', INTERVAL 3 SECOND);
Struggling With...
I'm struggling to easily figure out a way to show the records where I see only the maximum weight between each on and off cycle but add in 3 seconds after the off cycle's time stamp to find the maximum weight for the cycle.
Clarifying SQL maybe...
(SELECT MAX(Value) Value FROM transporter2_liveweight WHERE TimeStr BETWEEN StopTime AND StartTime)
What I want...
Basically I want to omit the Null statuses but show the maximum weight value between each On and Off cycle (+3 seconds after Off timestamp) with the maximum weight in the Weight column of the Off status records but leave On records Null for the Weight column.
Example Desired Result
+---------------------+--------+---------+
| TimeStr | Status | Weight |
+---------------------+--------+---------+
| 2019-06-29 18:51:00 | On | Null |
| 2019-06-29 18:51:16 | Off | 315 |
| 2019-06-29 19:00:57 | On | Null |
| 2019-06-29 19:01:13 | Off | 311 |
+---------------------+--------+---------+
Here's a small sample of the MySQL data for you to query and play with....
Note: Create the below table and insert the data, this is what can be used to try to get the desired results I'm asking about.
CREATE TABLE `tempbatch` (
`TimeStr` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Status` varchar(3) DEFAULT NULL,
`Weight` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:16',NULL,'311');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:15',NULL,'310');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:14',NULL,'309');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:13','Off',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:12',NULL,'307');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:11',NULL,'305');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:10',NULL,'304');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:09',NULL,'305');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:07',NULL,'306');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:06',NULL,'308');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:05',NULL,'284');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:04',NULL,'256');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:03',NULL,'168');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:02',NULL,'106');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:01:01',NULL,'15');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 19:00:57','On',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:57:30',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:55:21',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:55:11',NULL,'2');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:54:25',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:55',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:50',NULL,'2');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:34',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:19',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:16',NULL,'2');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:53:06',NULL,'1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:49',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:47',NULL,'-5');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:46',NULL,'-4');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:44',NULL,'-1');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:42',NULL,'0');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:41',NULL,'5');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:40',NULL,'6');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:39',NULL,'7');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:38',NULL,'8');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:37',NULL,'9');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:36',NULL,'10');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:35',NULL,'12');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:34',NULL,'14');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:32',NULL,'17');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:31',NULL,'19');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:30',NULL,'22');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:29',NULL,'25');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:28',NULL,'29');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:27',NULL,'32');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:26',NULL,'33');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:25',NULL,'34');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:24',NULL,'36');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:23',NULL,'41');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:22',NULL,'45');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:21',NULL,'47');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:20',NULL,'52');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:18',NULL,'53');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:17',NULL,'56');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:16',NULL,'63');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:15',NULL,'68');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:14',NULL,'73');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:13',NULL,'78');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:12',NULL,'84');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:11',NULL,'87');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:10',NULL,'90');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:09',NULL,'92');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:08',NULL,'94');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:07',NULL,'99');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:06',NULL,'104');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:05',NULL,'109');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:04',NULL,'113');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:03',NULL,'111');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:02',NULL,'114');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:01',NULL,'126');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:52:00',NULL,'132');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:59',NULL,'139');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:58',NULL,'145');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:56',NULL,'152');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:55',NULL,'158');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:54',NULL,'163');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:53',NULL,'165');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:52',NULL,'167');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:51',NULL,'173');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:50',NULL,'183');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:49',NULL,'192');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:47',NULL,'194');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:46',NULL,'196');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:45',NULL,'204');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:44',NULL,'202');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:43',NULL,'212');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:42',NULL,'219');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:41',NULL,'229');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:40',NULL,'232');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:39',NULL,'235');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:38',NULL,'237');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:37',NULL,'242');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:36',NULL,'246');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:34',NULL,'249');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:33',NULL,'252');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:32',NULL,'256');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:31',NULL,'267');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:29',NULL,'277');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:28',NULL,'283');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:27',NULL,'286');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:26',NULL,'285');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:24',NULL,'286');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:23',NULL,'300');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:22',NULL,'303');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:21',NULL,'309');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:20',NULL,'308');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:19',NULL,'314');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:18',NULL,'315');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:17',NULL,'311');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:16','Off',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:14',NULL,'308');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:13',NULL,'310');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:12',NULL,'312');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:11',NULL,'311');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:10',NULL,'309');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:09',NULL,'305');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:08',NULL,'319');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:07',NULL,'260');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:06',NULL,'193');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:05',NULL,'108');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:04',NULL,'8');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:02',NULL,'-5');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:01',NULL,'20');
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:00','On',NULL);
INSERT INTO `TempBatch` (`TimeStr`,`Status`,`Weight`) VALUES ('2019-06-29 18:51:00',NULL,'-17');
Upvotes: 3
Views: 398
Answers (2)
FuzzyTree
Reputation: 32392
The query below selects all rows where the status isn't null.
For the rows where the status is 'Off' it uses a subquery to return the maximum weight between the most recent 'On' and the current 'Off' + 3 seconds
select t.TimeStr, t.Status,
case
when status = 'Off'
then (select max(cast(Weight as signed)) from tempbatch t2
where t2.TimeStr between
(select max(TimeStr)
from tempbatch t3
where status = 'On' and t3.TimeStr < t.TimeStr)
and date_add(t.TimeStr, interval 3 second)
)
else Weight
end as Weight
from tempbatch t
where t.Status is not null
order by TimeStr
http://sqlfiddle.com/#!9/f27fb/6
Upvotes: 2
Nikolas Stevenson-Molnar
Reputation: 4710
You can do this with some clever use of sub queries to select columns:
SELECT TimeStr, Status,
(
SELECT TimeStr FROM tempbatch
WHERE TimeStr >= tb.TimeStr AND Status = 'Off'
ORDER BY TimeStr ASC LIMIT 1
) AS off,
(
SELECT MAX(CONVERT(weight, SIGNED INTEGER)) FROM tempbatch
WHERE TimeStr BETWEEN tb.TimeStr AND DATE_ADD(off, INTERVAL 3 SECOND)
) AS MaxWeight
FROM tempbatch tb WHERE Status = 'On' ORDER BY TimeStr ASC;
First, the query overall only selects On rows. The first subquery, selects a column, off, which is the time at which the Off for that On row appears.
The second subquery uses the off column, plus 3 seconds, to find the maximum weight in the range you're looking for.
This isn't exactly like your example, in that it only shows On rows, but you can easily show On, Off, and MaxWeight together:
SELECT TimeStr AS 'On', Off, MaxWeight FROM (
SELECT TimeStr, Status,
(
SELECT TimeStr FROM tempbatch
WHERE TimeStr >= tb.TimeStr AND Status = 'Off'
ORDER BY TimeStr ASC LIMIT 1
) AS off,
(
SELECT MAX(CONVERT(weight, SIGNED INTEGER)) FROM tempbatch
WHERE TimeStr BETWEEN tb.TimeStr AND DATE_ADD(off, INTERVAL 3 SECOND)
) AS MaxWeight
FROM tempbatch tb WHERE Status = 'On' ORDER BY TimeStr ASC
) t;
Upvotes: 1
Related Questions
- mysql select records with timestamp distance of 3 minute
- Get max value from one table with the time stamp plus 3 seconds for a time period from another table
- Calculating maximum interval between different time at the same field MYSQL
- Getting max value in a specific peroid of time in MySQL
- select the second longest time from mysql query
- how to select datetime near 3 hours in mysql
- MySQL get max and min value of a field for last 5 minutes timestamp
- selecting max timestamp when timestamp is a text field
- Selecting multiple max timestamps from database
- MySql - Select value with datetime = datetime-3 minuts