I'm looking at the difference between the current row and the last one.
Pacific_Time datetime,
ID int
SELECT AID, APFITIT, BID, BP.FTITEM, BPATTETEM Time TimTilata TBL 1 A JOIN TBL 1B ON AID - 1 = Beed WHERE Epiphitime - BPhipipTime & gt; 5 limited 30; I am joining the same table against myself, but I am moving it one row down or up. Most rows are separated by 1 second interval. I am searching for those rows which are more than 5 seconds different. The issue is that when Pacific_Time runs from 59 seconds to the next minute, it gives 41 seconds of delta, when in reality it is 1 second The difference is. Here's an example:
A.id A.Pacific_Time B.id B.Pacific_Time (A.Pacific_Time - B.Pacific_Time) 50 2011-07-19 07:49:55 49 2011- 07-19 07:49:54 1.000000 51 2011-07-19 07:49:56 50 2011-07-19 07:49:55 1.000000 52 2011-07-19 07:49:57 51 2011-07-19 07 : 49: 56 1.000000 53 2011-07-19 07:49:58 52 2011-07-19 07:49:57 1.000000 54 2011-07-19 07:49:59 53 2011-07-19 07:49:58 1.000000 55 2011-07-19 07:50:00 54 2011-07-19 07:49:59 41.000000 56 2011-07-19 07:50:01 55 2011-07-19 07:50:00 1.000000 57 2011- 07-19 07:50:02 56 2011-07-19 07:50:01 1.000000 58 2011-07-19 07:50:03 57 2011-07-19 07:50:02 1.000000 I have tried to use the timedif () , which gives me the right result, but it runs very slowly. It's running on a table with 20000 rows, but it must be run on a table with one million lines. Any advice?
mysql Ver 14.14 debit 5.5.29, debian-linux-gnu
SELECT AID, A.P. altitude, BID, B. pathithytime, A. pathithime - BPATTTM Timebila 1 TBL 1A JOIN TBL 1B At AID - 1 = Beed WHERE TO_SECONDS (APFi) Time - TO_SECONDS (BPFIFITEM) & gt; 5 LIMIT 30;
Please try this question.
No comments:
Post a Comment