1

We currently log data from our plants running remotely for diagnostic purposes on MySQL.

I have been asked to come up with a typical running profile for several variables. For this, I would require to get a pattern where, say, our plant is running in mode 400 for atleast 2 hours (we currently have the datetime column as primary key).

Would it be possible to come up with a query that says "show rows where plant is running in step 400 for more than 2 hours"?

Thanks :)

EDIT: Sorry, but one more query to add to that, is it possible to search where a step change occurs, like "search for * where value changes from 0 to 100"?

EDIT2: From a comment - an example would be:

datetime             plant_status incomer_power
2014-09-04 16:16:57     222     -67.06115
2014-09-04 16:17:27     222     -67.5517
2014-09-04 16:17:57     222     -68.0331
2014-09-04 16:18:27     222     -67.99048
2014-09-04 16:19:27     400     -158.7233
2014-09-04 16:19:57     400     -158.9488
2014-09-04 16:20:27     400     -159.0716

This is only a small snapshot of the data. But this data goes on for days. I would like a query, that would bring up the rows when plant_status changed to 400 (only 1 event in case of this example @ 2014-09-04 16:19:27).

  • Your question its a bit confusing,could you show some sample data?What do you mean step 400? – Mihai Sep 8 '14 at 11:56
  • Hi, One example would be: datetime plant_status incomer_power 2014-09-04 16:16:57 222 -67.06115 2014-09-04 16:17:27 222 -67.5517 2014-09-04 16:17:57 222 -68.0331 2014-09-04 16:18:27 222 -67.99048 2014-09-04 16:19:27 400 -158.7233 2014-09-04 16:19:57 400 -158.9488 2014-09-04 16:20:27 400 -159.0716 This is only a small snapshot of the data. But this data goes on for days. I would like a query, that would bring up the rows when plant_status changed to 400 (only 1 event in case of this example @ 2014-09-04 16:19:27). Included as EDIT above as well! – adalal Sep 8 '14 at 12:16
1

I do something similar on my side, and I use a MySQL Stored Procedure / Cursor.

You loop through each line of the table and record the current datetime entry and plant_status into variables, and then move on to the next line.

At which point you compare the current datetime and plant_status with the last ones.

If it matches a set condition (in your case new_status != last_status) you add an entry to a temporary table.

And then at the end you simply select * from temporary_table_name to show you all the rows where the status changed.

You could easily convert this to check the datetime as well (changing the old_datetime variable each time the status changes), and comparing that to the current one to find out if there is a two hour difference). (this probably should be a comment rather than an answer but I don't yet have enough points to comment - sorry)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.