2

I need help, I need to calculate the difference in minutes between the previous end date and the start date of a current incident, According to a system row.

this is the table:

id |system |start |end | ------------------------------------------------------------ 2 | system 1 | 2016-01-01 12:00:00 | 2016-01-01 13:00:00 | ------------------------------------------------------------ 3 | system 1 | 2016-01-02 11:00:00 | 2016-01-02 12:00:00 | ------------------------------------------------------------ 5 | system 1 | 2016-01-03 15:00:00 | 2016-01-03 16:00:00 | ------------------------------------------------------------ 6 | system 2 | 2016-01-01 10:00:00 | 2016-01-01 11:00:00 | ------------------------------------------------------------ 7 | system 2 | 2016-01-02 17:00:00 | 2016-01-02 18:00:00 |

this is the result:

The two systems records with ID 2 and 6 have no prior records of 'end date' to make subtraction:

id | system | diff_min | --------------------------------- 2 | system 1 | 0 | --------------------------------- 3 | system 1 | 1380 | --------------------------------- 5 | system 1 | 1620 | --------------------------------- 6 | system 2 | 0 | --------------------------------- 7 | system 2 | 1800 | ---------------------------------

Germán Rojas
  • 37
  • 5

3 Answers3

0

you can achieve this using two inbuilt functions

 select id, system, TIMESTAMPDIFF(MINUTE, endDate, startDate)  diff_min

or

 select id, system, TIMEDIFF(endDate, startDate)/60 as  diff_min
KP.
  • 393
  • 1
  • 12
0
 select  id,system,TIMESTAMPDIFF(MINUTE,end,start) as diff_min from table_name;
Priyanshu
  • 885
  • 6
  • 12
0

The following query does this:

SELECT
    id,
    system,
    IF (@previousSystem = system, TIMESTAMPDIFF(MINUTE ,@previousEndTime,start), 
        @previousEndTime := 0) diff_min,
    @previousSystem := system,
    @previousEndTime := end
FROM
    system_table,
    (
        SELECT
            @previousSystem := NULL,
            @previousEndTime := '0000-00-00 00:00:00'
    ) var
ORDER BY system, id;

Output:

Running the above query on your given data you will get an output like below:

id  system      diff_min
2   system 1    0
3   system 1    1320
5   system 1    1620
6   system 2    0
7   system 2    1800

SQL FIDDLE

Please ignore the last two columns in the result set of my query

1000111
  • 13,169
  • 2
  • 28
  • 37