query of timestamp

sandy1028

I have a values in database like this:
2008-03-05 12:20:01 | 0 | A|
| 2008-03-05 12:20:01 | 1 | B |
| 2008-03-05 12:30:02 | 1 | B |
|2008-03-05 12:30:02 | 1 | A |
| 2008-03-05 12:30:02 | 1 | B |
| 2008-03-05 12:30:02 | 0 | C

fields are timestamp,status and name.
How to write a query when the status is changed from 0 to 1 I have to calculate the timestamp.
Suppose if the status is 0 at 2008-03-05 12:20:01 and status has become 1 at 2008-03-05 12:30:01 how to find the information of status change in a day and calculate the duration

bogu

You need something like this?

SELECT
	(UNIX_TIMESTAMP(t1.`datetime`)-UNIX_TIMESTAMP(t2.`datetime`)) as nr_sec
FROM `table` t1
LEFT JOIN `table` t2 USING(`field`)
WHERE t1.`status` = 1 AND t2.`status` = 0 AND  DATE(t1.`datetime`) = DATE(t2.`datetime`)
GROUP BY DATE(t1.`datetime`)

sandy1028

bogu wrote:

You need something like this?

SELECT
	(UNIX_TIMESTAMP(t1.`datetime`)-UNIX_TIMESTAMP(t2.`datetime`)) as nr_sec
FROM `table` t1
LEFT JOIN `table` t2 USING(`field`)
WHERE t1.`status` = 1 AND t2.`status` = 0 AND  DATE(t1.`datetime`) = DATE(t2.`datetime`)
GROUP BY DATE(t1.`datetime`)

But I am using only one table.
For a field name I want to calculate the duaration when the status changes from 0 to 1.

nemonoman

I can't quite figure out what you mean by your question. It sounds like you are trying to capture 2 values: record creation timestamp (status 0), some event timestamp (status 1).

To calculate the duration between 2 DATETIMEs, such as NOW and the timestamp:

SELECT TIMEDIFF( now( ), timestamp )
FROM table

kakki

i think thats a bit complicated...
if your 3rd column is the name and you want to find the difference between
the last entry and the new one ...
to find out the last date and the date before that, you may have to do this:

SELECT timestamp FROM xxx WHERE name='A' ORDER BY timestamp LIMIT 2

now you need a query that really "calculates" these timestamps for name='A'
( may look a bit complicated and unnecessary, but i think min/max and limit is not
working as desired )

SELECT TIMEDIFF( MAX(timestamp) - MIN(xxx.timestamp) ) AS timediff
FROM xxx 
WHERE name='A' 
  AND timestamp IN 
( 
SELECT timestamp 
FROM xxx 
WHERE name='A' 
ORDER BY timestamp 
LIMIT 2
)

that should do the trick...i guess