Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    4

    Unanswered: timediff problem

    Hello codegurus and codemasters... I have a problem with TIMEDIFF statement, hope you awesome people can help me...

    The SELECT statement below:

    Code:
    SELECT * 
    FROM (SELECT resit_no,SUBSTRING(resit_time,1,19) AS entrystamp
          FROM DB1.receiptdetails
          WHERE terminal LIKE '%99' 
          AND resitdate BETWEEN '2010-12-01' AND '2011-01-31'
          AND productcode BETWEEN '91000001' AND '91000009') res1, 
         (SELECT expdate_resitno, SUBSTRING(TimeModified,1,19) AS moded_time 
          FROM DB2.mtransactionstatus
          WHERE expDt_resno LIKE '% - 99%'
          AND DTcreated BETWEEN '2010-12-01' AND '2011-01-31') res2
    WHERE res1.resitno = SUBSTRING(res2.expDt_resno,11,8)
    AND res1.entrystamp <> res2.moded_time
    AND TIMEDIFF(res1.entrystamp,res2.moded_time) <= '00:05:00'
    produces:

    Code:
    --------------------------------------------------------------------------
    resitno  | entrystamp	       | expdt_resno	    | 	moded_time
    ---------|---------------------|------------------------------------------
    99002593 | 2010-12-03 11:32:32 | 11/2012 - 99002593 | 2010-12-03 11:32:33 
    99000566 | 2010-12-03 15:55:09 | 12/2011 - 99000566 | 2010-12-03 15:55:10 
    99000567 | 2010-12-03 17:06:40 | 12/2011 - 99000567 | 2010-12-03 17:06:41 
    99000585 | 2010-12-15 10:28:00 | 12/2011 - 99000585 | 2010-12-15 10:27:59 
    99000581 | 2010-12-14 15:54:45 | 01/2012 - 99000581 | 2011-01-04 15:40:54 
    99000581 | 2010-12-14 15:54:45 | 01/2012 - 99000581 | 2011-01-06 15:01:51
    notice the two rows at the bottom. the difference between entrystamp and moded_time is higher than 5 minutes. why would it happen and how to correct it?

    both table and database use InnoDB.

    Thanks in advance...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm betting the problem lies in the actual data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    indeed Rudy is correct. The results are indeed correct (just verified myself). The TIMEDIFF (date1, date2) function gives the time difference of date1 - date2. If date2 is larger than date1 it will return a negative number. So your test on <= '00:05:00' will return the last two rows as the result is a negative number.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jan 2011
    Posts
    4
    Oh, I see....that's why....I thought TIMEDIFF automatically checks for (res1.entrystamp,res2.moded_time) and (res2.moded_time,res1.entrystamp) but turns out to be that way, huh...

    so i just add this to my previous statement, and it works like magic, lol.


    Code:
    AND TIMEDIFF(res2.moded_time,res1.entrystamp) <= '00:05:00'
    and it produces:

    Code:
    --------------------------------------------------------------------------
    resitno  | entrystamp	       | expdt_resno	    | 	moded_time
    ---------|---------------------|------------------------------------------
    99002593 | 2010-12-03 11:32:32 | 11/2012 - 99002593 | 2010-12-03 11:32:33 
    99000566 | 2010-12-03 15:55:09 | 12/2011 - 99000566 | 2010-12-03 15:55:10 
    99000567 | 2010-12-03 17:06:40 | 12/2011 - 99000567 | 2010-12-03 17:06:41 
    99000585 | 2010-12-15 10:28:00 | 12/2011 - 99000585 | 2010-12-15 10:27:59
    Yatta!
    thanks a lot, mr. Rudy and mr. Ronan

Posting Permissions

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