Date and the hour not exceeding one hour

[Miguel61]

OK boss.

My query:

Output :

SELECT * , 
TIMEDIFF
(
STR_TO_DATE(CONCAT(b.Date, ' ', b.hour), '%Y-%m-%d %H:%i:%S') , 
STR_TO_DATE(CONCAT(a.Date, ' ', a.hour), '%Y-%m-%d %H:%i:%S')
) AS strDiff 
FROM doTbl_A a JOIN
doTbl_A b ON 
a.ID=b.id-1 
WHERE a.DIST=b.DIST 
AND a.DATE=b.DATE ORDER BY a.ID

Output:

ID = 4
Dist = TE 
Date = 16/10/2009
Hour = 13:24:34
ID = 5
Dist = TE 
Date = 16/10/2009
Hour = 14:11:02
strDiff = 20/10/2009 0.46.28

ID = 5
Dist = TE 
Date = 16/10/2009
Hour = 14:11:02
ID = 6
Dist = TE 
Date = 16/10/2009
Hour = 14:23:47
strDiff = 20/10/2009 0.12.45

ID = 7
Dist = PE 
Date = 16/10/2009
Hour = 17:53:40
ID = 8
Dist = PE 
Date = 16/10/2009
Hour = 19:04:55
strDiff = 20/10/2009 1.11.15

Why in strDiff I have the date and not only the different of the time?

[r937]

Why in strDiff I have the date and not only the different of the time?

run your query outside of php and take another look

[Miguel61]

Output in MYSQL:

ID	Dist	Date		Hour		ID	Dist	Date		Hour		strDiff
4	TE 	2009-10-16	13:24:34	5	TE 	2009-10-16	14:11:02	00:46:28
5	TE 	2009-10-16	14:11:02	6	TE 	2009-10-16	14:23:47	00:12:45
7	PE 	2009-10-16	17:53:40	8	PE 	2009-10-16	19:04:55	01:11:15

It's OK boss?

[r937]

i dunno if the data is okay, but at least strDiff is showing only a time difference

[Miguel61]

Yes, strDiff showing a time difference between rows... if a.DIST=b.DIST and a.DATE=b.DATE and strDiff is < 1 hour... I delete row or what ?

[Miguel61]

Hi.

I have new problem, myquery:

SELECT 
TIMEDIFF(b.hour, a.hour) AS strDiff 
FROM doTbl_A a JOIN
doTbl_A b ON 
a.ID=b.id-1 
WHERE a.DIST=b.DIST 
AND a.DATE=b.DATE ORDER BY a.ID

If my table mysql is:

ID	Dist 	Date	 	Hour
15	LT 	22/10/2009 	08:56:05 
14	RM 	22/10/2009 	08:55:20  
13	RM 	22/10/2009 	08:55:19  
12	RM 	22/10/2009 	08:51:22    
11	LT 	22/10/2009 	08:47:19

The ID number 15 is validate... but I have ID number 11 whit the hour = 08:47:19 and whit same &#171;Dist&#187;...

The difference 08:56:05 (ID=15) and 08:47:19 (ID=11) is < 1 hour... and only ID=11 is valid...

Any idea?
Any suggestion with me ?

Many thanks
Mike

[Miguel61]

I try this query and working:

SELECT 
TIMEDIFF(b.hour, a.hour) AS strDiff 
FROM doTbl_A a JOIN
doTbl_A b ON 
a.ID=b.id-1 OR
a.ID=b.id-2 OR
a.ID=b.id-3 OR
A.ID=b.id-4
WHERE a.DIST=b.DIST 
AND a.DATE=b.DATE ORDER BY a.ID

But I do not know the exact location of the rows... can you help me?

[Miguel61]

Solution:

SELECT a.*,
     TIMEDIFF(b.hour, a.hour) AS strDiff
   FROM doTbl_A a
     JOIN doTbl_A b
       ON a.DIST = b.DIST
         AND a.DATE = b.DATE
         AND a.ID > b.ID
   WHERE TIMEDIFF(b.hour, a.hour) BETWEEN 0 AND 10000
   ORDER BY a.ID;