query

sandy1028

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 |

NogDog

Maybe - and I mean maybe - you could use an SQL variable:

SET @previous = -1;
SET @current = -1;

SELECT *, @previous:=@current AS `previous`, @current:=status AS current
FROM `table_name` WHERE `previous`<>`current`;

sandy1028

What is current and previous.
I have to set the value as previous and current as -1

I have to find the time when status=0 and when again status becomes 1 for the name field in the table.
Suppose status becomes 0 again, To display the time calculated for the previous status0 and again the current status 0 .

NogDog

Assuming you're using MySQL, the "@" defines a variable; so I was trying to use @previous and @ as variables to track the current and previous status values, and then by comparing the current row's status to the value of @previous, decide if this is a status-change row or not. I initialize them both to -1 so that they will not be the same as the status of the first line, regardless of whether it's a 0 or 1.

Each "SET" statement would be submitted as a separate query command before the main SELECT query is issued.