timediff problem

cirno · #1 (**permalink**) 01-23-11, 20:53

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

r937 · #2 (**permalink**) 01-23-11, 21:54

i'm betting the problem lies in the actual data

it-iss.com · #3 (**permalink**) 01-24-11, 04:22

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.

cirno · #4 (**permalink**) 01-24-11, 23:13

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

Internet Services

Web Development

Digital Marketing

Consumer Tech