Require help in getting time difference

[Deoctor]

Hi

I have a table having the structure like this.

CREATE TABLE `coc_issue_log` (
  `cil_issue_log_id` int(11) NOT NULL AUTO_INCREMENT,
  `cil_project_id` int(3) NOT NULL DEFAULT '0',
  `cil_issue_id` int(11) NOT NULL DEFAULT '0',
  `cil_open_pid` int(3) NOT NULL DEFAULT '0',
  `cil_closed_pid` int(3) NOT NULL DEFAULT '0',
  `cil_startpage` int(3) NOT NULL DEFAULT '1',
  `cil_endpage` int(3) NOT NULL DEFAULT '0',
  `cil_start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cil_end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cil_date` date NOT NULL DEFAULT '0000-00-00',
  `cil_completion` int(1) NOT NULL DEFAULT '0',
  `cil_rejected` int(3) NOT NULL DEFAULT '0',
  `cil_error_id` int(10) NOT NULL DEFAULT '0',
  `cil_comments` text,
  `cil_location_id` int(3) NOT NULL DEFAULT '0',
  `cil_modified_uid` int(10) NOT NULL DEFAULT '0',
  `cil_tool_ins_id` int(3) DEFAULT NULL,
  `cii_location_id` varchar(50) NOT NULL DEFAULT '000.000.000.000',
  PRIMARY KEY (`cil_issue_log_id`)
)

 

this contains the data some thing like this

 

insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1056','1','26','33','35','1','0','2011-03-25 18:42:12','2011-03-25 18:42:17','2011-03-25','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');
insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1169','1','26','33','35','1','0','2011-03-28 11:18:52','2011-03-28 11:18:57','2011-03-28','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');
insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1200','1','26','33','35','1','0','2011-03-28 12:48:52','2011-03-28 12:48:57','2011-03-28','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');
insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1224','1','46','33','35','1','0','2011-03-28 15:10:03','2011-03-28 15:10:12','2011-03-28','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');
insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1241','1','27','33','35','1','0','2011-03-28 17:16:41','2011-03-28 17:16:49','2011-03-28','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');
insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1243','1','46','33','35','1','0','2011-03-28 17:18:36','2011-03-28 17:18:45','2011-03-28','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');
insert into `coc_issue_log` (`cil_issue_log_id`, `cil_project_id`, `cil_issue_id`, `cil_open_pid`, `cil_closed_pid`, `cil_startpage`, `cil_endpage`, `cil_start_time`, `cil_end_time`, `cil_date`, `cil_completion`, `cil_rejected`, `cil_error_id`, `cil_comments`, `cil_location_id`, `cil_modified_uid`, `cil_tool_ins_id`, `cii_location_id`) values('1245','1','46','33','35','1','0','2011-03-28 17:24:18','2011-03-28 17:24:27','2011-03-28','1','0','0','xmlmerge completed successfully','1','1',NULL,'000.000.000.000');

 

The cil_tool_ins_id is the one which defines my tool. so here my problem is that i need to find the ideal time of my script. So  i guess what i need to do is that i need to subtract the cil_end_time of the first id from the cil_starttime of the next id. Is there is any way to do this using only the mysql script. or a stored procedure. I dont know any thing about stored procedures. Can any one please guide me on this how to do it.

 

this is the better example of what i need to do

 

SELECT TIMEDIFF((SELECT cil_start_time FROM coc_issue_log WHERE cil_issue_log_id = '1169'),(SELECT cil_end_time FROM coc_issue_log WHERE cil_issue_log_id = '1056')) AS TIMEDIFF

 

but the problem is that there are multiple log_id which i cannot hardcode anywhere.

 

Can some one please help me on this.

[joel24]

you could select the next / previous like so?

i.e.

SELECT TIMEDIFF((SELECT cil_start_time FROM coc_issue_log WHERE cil_issue_log_id = cil.cil.issue_log_id),(SELECT cil_end_time FROM coc_issue_log WHERE cil_issue_log_id < cil.cil_issue_log_id LIMIT 1)) AS TIMEDIFF, cil_issue_log_id FROM cil_issue_log cil