Here is my MySQL table.

+-----------------+-------------------+------+----------+---------------------+
| server_check_id | server_service_id | time | error_id | created             |
+-----------------+-------------------+------+----------+---------------------+
|           57532 |                 1 |  226 |        1 | 2011-06-18 16:08:44 |
|           57489 |                 1 |  236 |        1 | 2011-06-18 16:03:29 |
|           57446 |                 1 |  229 |        1 | 2011-06-18 15:58:15 |
|           57403 |                 1 |  377 |        1 | 2011-06-18 15:52:57 |
|           57360 |                 1 |  232 |        1 | 2011-06-18 15:47:42 |
|           57317 |                 1 |  216 |        1 | 2011-06-18 15:42:27 |
|           57274 |                 1 |  227 |        1 | 2011-06-18 15:37:10 |
|           57231 |                 1 |  327 |        1 | 2011-06-18 15:31:47 |
|           57145 |                 1 |  232 |        1 | 2011-06-18 15:18:27 |
|           57102 |                 1 |  238 |        1 | 2011-06-18 15:13:12 |
|           57059 |                 1 |  210 |        1 | 2011-06-18 15:07:46 |
|           57016 |                 1 |  227 |        1 | 2011-06-18 15:02:31 |
|           56973 |                 1 | 1001 |        2 | 2011-06-18 14:55:45 |
|           56930 |                 1 | 1000 |        2 | 2011-06-18 14:49:44 |
|           56887 |                 1 |  237 |        1 | 2011-06-18 14:44:28 |
|           56844 |                 1 |  232 |        1 | 2011-06-18 14:39:14 |
|           56801 |                 1 |  229 |        1 | 2011-06-18 14:33:59 |
|           56758 |                 1 |  233 |        1 | 2011-06-18 14:28:43 |
|           56715 |                 1 |  225 |        1 | 2011-06-18 14:23:28 |
|           56672 |                 1 |  224 |        1 | 2011-06-18 14:18:14 |
|           56629 |                 1 |  223 |        1 | 2011-06-18 14:12:56 |
|           56586 |                 1 |  229 |        1 | 2011-06-18 14:07:42 |
|           56543 |                 1 |  246 |        1 | 2011-06-18 14:02:28 |
|           56500 |                 1 |  260 |        1 | 2011-06-18 13:57:10 |
|           56457 |                 1 |  207 |        1 | 2011-06-18 13:51:56 |
|           56414 |                 1 |  235 |        1 | 2011-06-18 13:46:41 |
|           56371 |                 1 |  222 |        1 | 2011-06-18 13:41:25 |
|           56328 |                 1 |  230 |        1 | 2011-06-18 13:36:10 |
|           56285 |                 1 |  218 |        1 | 2011-06-18 13:30:55 |
|           56242 |                 1 |  219 |        1 | 2011-06-18 13:25:38 |
+-----------------+-------------------+------+----------+---------------------+

error_id 1 indicates working while 2 indicated its not working.

Now, I want to get how long my service was not working last time. To further explain, I want to get duration between most recent record which has error_id 1 with its previous record has error_id 2 following all records with error_id 2 until the one whose previous one has error_id 1. Hope it make sense.

Considering above data, I want to get duration between record with server_check_id 56930 to 57016

I can do it with several for loops in PHP but I want to know if its possible with 1 or 2 simple MySQL queries.

Please advise.

Accepted Answer

pseudo-code:

1) $last_err_id =  SELECT `server_check_id` FROM `table` WHERE `error_id` = '2' ORDER BY `created` DESC LIMIT 0,1

2) $prev_work_id = SELECT `server_check_id` FROM `table` WHERE `error_id` = '1' AND `server_check_id` < $last_err_id ORDER BY `created` DESC LIMIT 0,1

3) $start_err_time = SELECT `created` FROM `table` WHERE `error_id` = '2' AND `server_check_id` > $prev_work_id ORDER BY `created` DESC LIMIT 0,1

4) $start_work_time = SELECT `created` FROM `table` WHERE `error_id` = '1' AND `server_check_id` > $last_err_id ORDER BY `created` DESC LIMIT 0,1

Result will be between $start_work_time and $start_err_time.

Written by OZ_
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki