Difference and sum of DATETIME column

Asked

Viewed 581 times

2

I have a table called chamado_processos with the following structure and data

As you can see one of the columns of this table called dt_processo is a field DATETIME and the column tp_processo defines whether the process is beginning or stop.

I would like to calculate the time between each so-called recital a single called may have more than one start process and more than one stopping process.

That call I showed in the picture has 00:02:17 hours, minutes and seconds.

mysql sql database algorithm
  • 1

    What have you ever done? If you have more than one start is worth the oldest? If you have more than one finish is worth the newest?

  • It is rather ample your questioning by more than one start process and more than one stopping process simulate this also in your inquiry? How can I define which one to take/add from which?

  • The process time will only be calculated if the last process of the call has the tp_processo = P

  • So if there is more than one process for example an I and P, I and P, it should add up the two times and so on, I was clearer?

2 answers

3

Sum of hours

SELECT CONVERT (VARCHAR, SUM (CONVERT (INT, LEFT (HORA, 2))) + (((SUM (CONVERT (INT, RIGHT (HORA, 2)))) - (SUM (CONVERT (INT, RIGHT (HORA, 2))) % 60)) / 60)) + ':' +  CONVERT (VARCHAR, SUM (CONVERT (INT, RIGHT (HORA, 2))) % 60) 
FROM TABELA

This query makes the sum of the hours used in the task.

Difference between hours converting to amount of minutes 

SELECT 
ISNULL(DATEDIFF(Mi,'2011-12-01 02:05:00','2011-12-01 10:15:00'),0)

Will be displayed the amount of minutes in this range that is 490 minutes.

Convert minutes to HH:MM format

DECLARE @TMINUTOS INT 
SET @TMINUTOS=ISNULL(DATEDIFF(Mi,'2011-12-01 02:05:00','2011-12-01 10:15:00'),0)

The amount of minutes will be 490 minutes

SELECT RIGHT('0' + CAST((@TMINUTOS / 60) As VARCHAR(2)),2) + ':' + RIGHT('0' + CAST((@TMINUTOS % 60) As VARCHAR(2)),2)

The result to be displayed is 08:10 which is the time difference.

1


Another way to do this is by grouping the values by a common key and subtract the values taking into account whether it is start or not. Hence your table of chamado_processos should have a column with an equal value for those two records.

SELECT SUM(IF(tp_processo = 'P', `dt_processo`, 0)) - 
       SUM(IF(tp_processo = 'I', `dt_processo`, 0)) tempo 
FROM chamado_processos  
group by id

SQL Fiddle

In this case if there is no end process the result will be negative start time

  • Thank you! I made some small modifications, helped me a lot.

Browser other questions tagged mysql sql database algorithm

You are not signed in. Login or sign up in order to post.