Column difference and sum DATETIME [closed]

Navigation
2

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

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

I would like to calculate the time between each call since a single called can have more than one startup process and more than one stop process strong>.

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

    
asked by anonymous 10.10.2016 / 14:35

2 answers

1

Another way to do this is by grouping the values by a common key and subtracting the values by taking into account whether or not it starts or not. This is why your chamado_processos table should have a column with an equal value for these 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 the negative start time

    
10.10.2016 / 15:15
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 sums up 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)

The number of minutes in this range is displayed that is 490 minutes.

Convert minutes in 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 number 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 difference in time.

    
10.10.2016 / 14:59
Return radiobutton selection in C # Practical application of isset () and unset () in PHP