SitePoint Sponsor |
|
User Tag List
Results 26 to 33 of 33
-
Oct 20, 2009, 12:04 #26
OK boss.
My query:
Output :
Code: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
Code: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
-
Oct 20, 2009, 12:42 #27
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Oct 20, 2009, 12:52 #28
Output in MYSQL:
Code: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
-
Oct 20, 2009, 12:55 #29
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
i dunno if the data is okay, but at least strDiff is showing only a time difference
-
Oct 20, 2009, 13:03 #30
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 ?
-
Oct 22, 2009, 04:05 #31
Hi.
I have new problem, myquery:
Code: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
Code: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 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
-
Oct 22, 2009, 07:54 #32
I try this query and working:
Code: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
-
Oct 23, 2009, 02:53 #33
Solution:
Code: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;
Bookmarks