Calculating hours within the query

Navigation
5

I've already done this and it works fine in C ++. My problem is to do within a select to appear in the table. If anyone wants to see the algorithm in C ++ to help mount it in MySql I can.

My table is this:

The fields after the state are custom_fields created by me

I need to create 4 more fields. Home • The total time the state is at 0.
• The total time that the state is above 0.
• The percentage he stayed at 0 and another. Home • The percentage that the state was above 0.

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

In 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.

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

If I was not very clear, you can comment, I'll try to explain it better.

I was thinking that a good solution would be to use variables and functions, but since I'm new I'm not very aware of this. If anyone knows of a handout or course that explains well about these subjects, you can indicate.

    
asked by anonymous 01.01.2017 / 19:46

1 answer

2

For the total time that the state is at 0 (use the same logic for when the state is nonzero), you can use 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 percent, 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 out the argument (value of percentage) to 2 (two) decimal places, because, in most cases, percentages with more than 2 decimal places are insignificant ( significant figures ).

Consideration:

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

    
01.01.2017 / 20:16
How to digitally sign a PDF in C #? How do I save an undefined string in a structure?