Calculation of hours within consultation

Question

I already made this account and it works right in C++. My problem is to do inside a select to appear in the table. If someone wants to see the C++ algorithm to help mount in Mysql I can send.

This is my chart:

Fields after state are custom_fields created by me

I need to create 4 more Fields.
• The total time the state is at 0.
• The total time the state is above 0.
• The percentage he stood at 0 and another.
• The percentage that the state stood above 0.

As you can see it only appears at the time the state has changed. So I need to take state time and subtract by the time of the previous state different. If you have two lines with the same state, consider only the time of the first row of the state repeated to be subtracted. The result of these subtractions will be summed and will generate a total time at the end.

On the last line, I have to take the total time and subtract from the current time, because the last line of the query is the current state.

The calculation of hours and percentages should be separated by object_id.

At the end I have to group the table by object_id to bring in the screen jqgrid.

If I’m not clear, you can comment, I’ll try to explain better.

I was thinking that a good solution would be to use variables and functions, but as I’m new I don’t have much knowledge of it. If someone knows of any booklet or course that explains well about these subjects, can indicate.

Answer 1

For the total time the state is at 0 (use the same logic for when the state is different from zero), you can use the TIMESTAMPDIFF():

SELECT CASE WHEN `state` = 0 THEN TIMESTAMPDIFF(HOUR, `current_time`, `state_time` ELSE 0 END) AS `state_zero` FROM `minha_tabela`

For the time, in percentage, where the state was zero (use the same logic for the other percentage calculations), you can use something like this:

SELECT
    (CASE WHEN `state` = 0
        THEN
            CONCAT(ROUND((COUNT(*)/(SELECT COUNT(*) FROM `minha_tabela`)*100),2),'%')
        ELSE 0
    END) AS `porcentagem_zero`
FROM `minha_tabela`

I used CONCAT() to concatenate the percentage result with the symbol "%" (percentage).

I used ROUND() to round off the argument (percentage value) to 2 (two) decimal places, because in most cases percentages of more than 2 (two) decimal places are insignificant (significant figures).

Consideration:

For cases where the table is empty, consider treating the case of division by zero.