Calculate time difference

[rashmi_k28]

Hi,

 

Help me in calculating time differnence

 

select status,timestamp from `tablename` where date(timestamp)=date(2008-04-04);

 

The result what I get is

    1 | 2008-04-04 17:30:01 |

|      1 | 2008-04-04 17:40:01 |

|      1 | 2008-04-04 17:50:01 |

|      1 | 2008-04-04 18:00:01 |

|      1 | 2008-04-04 18:10:01 |

|      1 | 2008-04-04 18:20:02 |

|      1 | 2008-04-04 18:30:02 |

|      1 | 2008-04-04 18:40:01 |

|      1 | 2008-04-04 18:50:01 |

|      1 | 2008-04-04 19:00:02 |

|      1 | 2008-04-04 19:10:01 |

|      1 | 2008-04-04 19:20:02 |

|      1 | 2008-04-04 19:30:01 |

|      1 | 2008-04-04 19:40:01 |

|      1 | 2008-04-04 19:50:01 |

|      1 | 2008-04-04 20:00:01 |

|      1 | 2008-04-04 20:10:01 |

|      0 | 2008-04-04 20:20:01 |

|    0| 2008-04-04 20:30:01 |

|      1 | 2008-04-04 20:40:02 |

|      1 | 2008-04-04 20:50:01 |

|      1 | 2008-04-04 21:00:01 |

|      1 | 2008-04-04 21:10:01 |

|      1 | 2008-04-04 21:20:01 |

|      1 | 2008-04-04 21:30:01 |

|      0 | 2008-04-04 21:40:01 |

|      1 | 2008-04-04 21:50:01 |

|      1 | 2008-04-04 22:00:02 |

|      1 | 2008-04-04 22:10:02 |

 

Now how to calculate the time difference between the two fields when status is 0.

For example When status becomes 1 at 2008-04-04 20:40:02 so how can i calculate the difference as 20 minutes the status was down and when status is 0 at 2008-04-04 21:40:01 in the another row I should calculate the timediffernce as 10 min.

 

 

 

[beboo002]

first u change time to unix_timestamp() then subtract time in ,timeout then u get total time then u use this function

round(SEC_TO_TIME((totaltime))/10000,2) this function get time in hours

 

may this idea help u

[rashmi_k28]

What is time in and timeout.

I want the difference between the status from when it is 0 and status changed to 1 from 0 in a day