rashmi_k28 Posted March 18, 2008 Share Posted March 18, 2008 I have a database records as shown below. The status of name filed is 0 and 1. How to find the timestamp between the status when changes from 0 to 1 and when status changes from 1 to 0. For example when status is 0 at 2008-03-18 08:20:02 and status is 1 at 2008-03-18 09:40:01 and again status is 0 2008-03-18 09:50:01. How to display it like 70 minutes the status is 0 and 10 minutes status is 1 and again 10 minutes status is 0 Name | timestamp | status aaa | 2008-03-18 08:20:02 | 0 | | aaa| 2008-03-18 08:30:01 | 0 | | aaa | 2008-03-18 08:40:01 | 0 | | aaa| 2008-03-18 08:50:01 | 0 | | aaa | 2008-03-18 09:00:01 | 0 | | aaa| 2008-03-18 09:10:01 | 0 | |aaa | 2008-03-18 09:20:02 | 0 | | aaa| 2008-03-18 09:30:01 | 0 | | aaa| 2008-03-18 09:40:01 | 1 | | aaa| 2008-03-18 09:50:01 | 0 | Quote Link to comment Share on other sites More sharing options...
aschk Posted March 18, 2008 Share Posted March 18, 2008 Well you need to know which record you're starting with to calculate it, but unless you know that you want to use 2008-03-18 08:20:02 for the calculation it's going to be hard, because you're looking at lots of status 0's in between. The timediff function will give you what you require. SELECT TIMEDIFF(rowX, rowY) Otherwise I would ask why you're logging a status every 10 minutes? It seems you're logging lots of pointless information. Also, if you SELECT * FROM <table> WHERE status = 1, this will give you all the records where's the status is 1 from which you can calculate the differences between the rows. You essentially need to keep a point of the last status 1 time and calculate between intervals. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 18, 2008 Share Posted March 18, 2008 You can easily accomplish this with user variables storing a rolling TIMEDIFF and an ORDER BY clause. Quote Link to comment Share on other sites More sharing options...
rashmi_k28 Posted March 19, 2008 Author Share Posted March 19, 2008 aaa | 2008-03-18 08:20:02 | 0 | If status 0 is at 2008-03-18 08:20:02 and till status is 1 I have to caluculate the timestamp in between. If status becomes 0 again at 2008-03-18 09:50:02 and till I get status is 1 I have to caluculate by using group by timestamp Quote Link to comment Share on other sites More sharing options...
fenway Posted March 19, 2008 Share Posted March 19, 2008 aaa | 2008-03-18 08:20:02 | 0 | If status 0 is at 2008-03-18 08:20:02 and till status is 1 I have to caluculate the timestamp in between. If status becomes 0 again at 2008-03-18 09:50:02 and till I get status is 1 I have to caluculate by using group by timestamp Yes, I understand... see my previous post. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.