Conditional timestamp differences

[muppet]

I have a table which holds the status of a machine in our factory. When the machine is running a line is written with a status of 1, when it stops it writes a status of 5 until a program records the error then it writes a line with a status of 6 until the machine is running again when it writes a 1 and so on

STATUS
| Status | Error | Timestamp           |
|   1    |       | 2010-07-27 08:26:12 |     -Machine is running
|   5    |   22  | 2010-07-27 08:29:15 |     -Machine has stopped with an error code of 22
|   6    |       | 2010-07-27 08:29:16 |     -Error has been logged, machine still down
|   3    |       | 2010-07-27 08:30:48 |     -unrelated status event, machine still down
|   1    |       | 2010-07-27 08:31:33 |     -Machine is running again

I would like to query this to have an outcome of:

|  Time Down  | Error |  Timestamp          |  
|  00:02:18   | 22    | 2010-07-27 08:26:15 |

And, if possible, change the error '22' to the actual error reason which is stored in a separate table:

|  Time Down  | Error     |  Timestamp          |  
|  00:05:18   | Spout Jam | 2010-07-27 08:26:15 |

I have a separate table with error codes and reasons,

ALARMS
| Error_code | Reason           |
|     1      | Over Temperature |
|     22     | Spout Jam        |

So it needs to get the timestamp with a status 5 and subtract the timestamp with a status 1 before the 5 came up. :confused: yeah, that made it sound easier.

[tesuji]

Hi muppet

Glad to meeting you again. How is Hayley Westenra doing? I didn't heard of any new song from her for some time past. I myself prefer her older po kare kare ana, also her interpretation of Kate Bush's Wuthering Heights is masterly performance.

Of course, there is a solution for accumulating your down and up times. However, one cannot do this within single sql query statement. You need to write some procedural code using PSM, C, Java etc. I am just sitting in front of some SQL server and Sybase databases, there I have some special own-written user defined function (UDF, written in ANSI standard programming language PSM, yes that's truly ANSI standard for SQL programming). So it was easy to accumulating anything what looks like a date/time or so.

Replacing "22" by any arbitrary string can simply be done in select statement by using ANSI standard CASE function. This is the result:

select downtime() as "Down/Up Time (sec.)", 
   case 
      when werror = 22 then 'Spout Jam' || ' (22)'
      when werror = 99 then 'This Error ' || ' (99)'
      else cast(werror as varchar)
   end as "Error", wtimestamp as "Timestamp", timeStampToModJD(wtimestamp) as "mod. Julian Day"
from Waiapu where "Error" is not null;

/* Result
Down/Up Time (sec.)  Error           Timestamp                mod. Julian Day
------------------------------------------------------------------------------
138.000:183.000      Spout Jam (22)  2010-07-27 08:29:15.000  245540430555.000
*/

I put down and up time together showing you both. Btw, nice sequence of digits 138 and 183 calculated by your above samples, accidently chosen? Computing and accumulation is done inside UDF downtime() with my modified Julian Day UDF where a such-a-kind coded JD-example of your given timestamp is shown at the far right.

-- tesu

[griswolf]

Better to use a join with the ALARMS table. I have not checked that my syntax is correct...

SELECT s.Status as Error, NOW() - s.`TIMESTAMP` as `TIME Down`, s.`TIMESTAMP`, a.Reason 
  from STATUS s join ALARMS a on s.error = a.Error_code 
  where s.status = 5 AND s.timestamp > $lastTimeWeLooked

Or something like that.

See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[muppet]

Thanks for the kiwi references tesuji, I've been out of the country for a couple of years now and don't hear much from home.

I have simplified the process a little. I modified the machine to give an output of '1' when it starts to run and '0' when it stops running and added a column to the database as 'running'
This should make it alot easier to get the results, but I am still missing how to find the differences in time between the '1' and '0's

So here is what the database will hold:

| running | error |     timestamp       |
|    0    |   22  | 2010-08-03 08:33:25 |  -it stopped with error 22
|    1    |   00  | 2010-08-03 08:34:30 |  -started again
|    0    |   43  | 2010-08-03 08:35:35 |  -stopped again with error 43...
|    1    |   00  | 2010-08-03 08:36:40 |
|    0    |   22  | 2010-08-03 08:37:45 |
|    1    |   00  | 2010-08-03 08:38:50 |
|    0    |   22  | 2010-08-03 08:39:55 |
|    1    |   00  | 2010-08-03 08:41:00 |

And i would like to get:

| error | time lost  |
|  22   |   00:03:15 |  -total time that the machine was down with error 22
|  43   |   00:01:05 |  -total time it was down with the next error...

Thanks griswolf, I understand how to perform the 'join' function now.

[tesuji]

That is a more human data structure now. "And i would like to get:" implies you haven't got so far? I am afraid that such a constellation should also be possible:

| 0 | 22 | 2010-08-03 23:39:55 |
| 1 | 00 | 2010-08-04 00:41:00 |

-- tesu

[muppet]

Well, I would like to get this as a result of a query

| error | time lost  |
|  22   |   00:03:15 |  -total time that the machine was down with error 22
|  43   |   00:01:05 |  -total time it was down with the next error...

[griswolf]

Can you clarify, please whether "total time that the machine was down with error 22"

• means sum of all the type 22 down times in the table
• means the most recent type 22 down time in the table

The problem is that you want to SELECT information in two rows: One of them mentions the down time, one mentions the up time and there isn't any way to easily unify them.

If you have control over the layout of the database, then it would be much easier to have the data organized by incident in the first place: Error conditions start a new incident (add a row) and subsequent information updates that row rather than adding another row. Then the time down is simple timestamp arithmetic on two entries in the same row, which is easy. If this is an option, we can discuss how to lay out the table or maybe two tables to get the best effect. If you really only need to keep track of down time, you can use UPDATE to modify the row holding the current incident. If you need to keep a log of the periodic status You might create a distinct `incident` table that tracks incidents in parallel with the existing `log` table.

Finally, if you must use one table only, you can probably get there by keeping a 'cookie' column that is used to mark rows that participate in the same error incident. The state machine is:

1. CURRENT-STATUS='good'
   • on 'good' add new row with new cookie; -> 1
   • on 'error' add new row with new cookie; -> 2
2. CURRENT-STATUS='error'
   • on 'good' add new row with old cookie; -> 1
   • on 'error' (same error) add new row with old cookie; -> 2
   • on 'error' (new error) ?? treat as a transition to 'good' followed by the new transition to 'error' ??

You would use a subselect to get the first and last rows with the same cookie, and do arithmetic on the `timestamp` columns of those two rows.

[muppet]

Thanks for the post griswolf, all this cookie talk has confused me a little, but to answer your question - "total time that the machine was down with error 22" does mean - sum of all the type 22 down times in the table. And I do have control over how the database is structured.

I was thinking though, why not put a time_down column in and fill it whenever the machine starts. Then I could just group all errors and add all down_time. When I could do an insert something like

insert into abm_status (machine, running, timestamp, lost_time) values ('abm5', '1', now(), (now() - (select timestamp from abm_status where running = '0' order by timestamp desc limit 1)))

This should make life far easier. But it doesn't work. I get the error

You can't specify target table 'abm_status' for update in FROM clause

any work arounds?

[griswolf]

OK. "Cookie" is just a unique thing. One per instance. Browser cookies, for instance are one (well, sometimes a few) per URL. So anything that can be used as a key to the instance is a cookie (The page server sets and uses your cookies to keep track of which connection(s) are related). In this case, I was thinking to use the timestamp at start of error condition: Guaranteed to be unique, already in the table, etc. However...

MySql appears to not allow an update based on a sub-select on the same table. I had a related problem when I tried to figure out how to do the time-down calculation based on two rows in one table. I think the best answer is to have an incident table that keeps track of error incidents. I think it has a start_time , an error_code a nullable fixed_time and an id Multiple rows in the status table (which I've been calling the log table) can reference the same incident; and when the status changes from down to up, you UPDATE the incident.fixed_time column for that incident. Entries in the status table that are 'running' just have a null in the incident_id column.

This gets you easy calculation for down time based on incident(s), error type(s) or total time down. All timing info is in the incident table. Maybe like this:

create table abm_status (
  log_time timestamp default now() primary key,
  /* running int(1) default 1, -- not needed: Check null incident_id */
  incident_id integer
);
create table abm_incident (
  id integer auto_increment primary key,
  error_code integer(1), -- never 0: Only keep error incidents here
  start_time timestamp not null default now(),
  fixed_time timestamp -- filled in once when machine starts running
);

/* get the amount of time machine was down with error 22 */
SELECT SUM(fixed_time - start_time) as down_22 from abm_incident WHERE error_code = 22;

/* get total time down in January */
SELECT SUM(fixed_time - start_time as down_jan from abm_incident WHERE start_time >= '2010-01-01T00:00' and start_time < '2010-02-01T00:00' and end_time not NULL;

I checked that the create table code works, but did not make some data to test the SELECT statements.

One variation: "Incidents" can be either a collection of one or more adjacent errors or a single non-error. That allows you to see rather than infer the 0 error_status for non-error incidents. In that case, you write the fixed_time == the start_time so there's zero time down. If you want to do the same thing (no null fixed_time) for actual errors, then you change the column name to `stop_time` and add column `is_fixed` int(1) default 1, (assuming you have many more 'up' incidents than 'down')

[tesuji]

Hi muppet

Usually I don't like to meddle into progressed postings. However, this time let me allow to ask whether you are still want to calculate the down times from your priorly shown example table (heading: running | error | timestamp). Reason is I feel rather confident that this table is quite well designed for straight calculations with simple methods for processing consecutively stored rows. Even you don't need running-column. Error and timestamp columns are entirely sufficient.

-- tesu