SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 33 of 33
  1. #26
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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
    Output:

    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
    Why in strDiff I have the date and not only the different of the time?

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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
    It's OK boss?

  4. #29
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  6. #31
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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
    If my table mysql is:

    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 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

  7. #32
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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
    But I do not know the exact location of the rows... can you help me?

  8. #33
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •