CODEWITHSUNDEEP
mysqlsqldatabase
Fishingfon
Fishingfon

Reputation: 1034

How to select column with greatest difference between dates in MySQL

I am trying to find the product EPC of the product which has the longest time between its first and last reading. There can be multiple entries in the table.

For example, in the sample data shown below, one product with the EPC 03.0000A89... has a entry with date 1992-11-05, and one entry with date 2015-05-12. This obviously has the biggest duration between dates, but i have no idea how to write a query to work this out.

These are the DDLs:

CREATE TABLE IF NOT EXISTS `Track_Record` (
  `longitude` varchar(15) NOT NULL ,
  `lattitude` varchar(15) NOT NULL ,
  `datetime` DATETIME NOT NULL,
  `EPC` varchar(200) NOT NULL ,
  `ip` varchar(50) NOT NULL ,

  PRIMARY KEY  (ip, EPC, datetime)
); 

 INSERT INTO track_record (ip, longitude, lattitude, datetime, EPC)
VALUES
('000.111.222', '27.4667 S', '153.0333 E', '1992-11-05 18:56:46', '03.0000A89.00016F.000169DCD'), -- 0
('000.111.222', '27.4667 S', '153.0333 E', '2015-05-12 13:21:16', '03.0000A89.00016F.000169DCD'), -- 0
('555.666.777', '22.2783 N', '114.1747 E', '2012-07-19 12:22:16', '04.0000A89.00016F.000169DCD'), -- 0
('000.111.222', '27.4667 S', '153.0333 E', '2011-03-01 11:43:26', '03.0000A89.00016F.000169DCD'), -- 0
('555.666.777', '27.4667 S', '153.0333 E', '2014-09-02 18:53:14', '06.0000A89.00016F.000169DCD'),
('222.333.444', '59.3500 N', '18.0667 E', '2002-10-05 18:23:18', '04.0000A89.00016F.000169DCD'), -- 0
('333.444.555', '15.7833 S', '47.8667 W', '2015-02-22 19:53:16', '01.0000A89.00016F.000169DCD'),

('555.666.777', '22.2783 N', '114.1747 E', '2014-02-23 18:53:14', '08.0000A89.00016F.000169DCD'),
('222.333.444', '59.3500 N', '18.0667 E', '2009-08-12 18:23:18', '03.0000A89.00016F.000169DCD'), -- 0
('333.444.555', '15.7833 S', '47.8667 W', '2015-10-26 19:53:16', '07.0000A89.00016F.000169DCD'),
('000.111.222', '27.4667 S', '153.0333 E', '2002-11-21 11:43:26', '03.0000A89.00016F.000169DCD'), -- 0
('555.666.777', '22.2783 N', '114.1747 E', '2014-09-12 18:53:14', '06.0000A89.00016F.000169DCD'),
('222.333.444', '59.3500 N', '18.0667 E', '2010-09-13 18:23:18', '11.0000A89.00016F.000169DCD'), -- 0
('333.444.555', '15.7833 S', '47.8667 W', '2005-01-27 19:53:16', '01.0000A89.00016F.000169DCD'),
('555.666.777', '22.2783 N', '114.1747 E', '2006-09-22 18:53:14', '06.0000A89.00016F.000169DCD'),
('222.333.444', '59.3500 N', '18.0667 E', '2008-10-11 18:23:18', '04.0000A89.00016F.000169DCD'), -- 0
('888.444.555', '15.7833 S', '47.8667 W', '2013-03-19 19:53:16', '10.0000A89.00016F.000169DCD'),

('444.555.666', '51.5072 N', '0.1275 W', '2013-01-11 22:21:15', '04.0000A89.00016F.000169DCD'); -- 0

If someone has an idea of how i get get this info, i would much appreciate any help you can offer.

Thanks Corey

Upvotes: 1

Views: 1021

Answers (5)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

So the EPC is your product code? Then you want the min and max time per EPC and then take the one with the max difference. That's all.

select epc
from
(
  select epc, max(datetime) - min(datetime) as diff
  from track_record
  group by epc
) products
order by diff desc limit 1;

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

It seems like a strange thing to want to know, but anyway...

SELECT EPC FROM track_record 
 GROUP 
    BY EPC 
 ORDER 
    BY DATEDIFF(MAX(datetime),MIN(datetime)) DESC
 LIMIT 1;
+-----------------------------+
| EPC                         |
+-----------------------------+
| 03.0000A89.00016F.000169DCD |
+-----------------------------+

Upvotes: 1

Vignesh
Vignesh

Reputation: 1063

Try the below query, I am not claiming this as the best performance method because mysql is not my primary working area. But this works.

SELECT MAX(DATEDIFF(a.`datetime`,b.`datetime`) * 24*60*60) AS diff, a.EPC FROM track_record a LEFT JOIN track_record b ON a.EPC = b.EPC

Upvotes: 0

BhandariS
BhandariS

Reputation: 604

use BETWEEN 

select * from tbl_name where DateTime BETWEEN '1992-11-05 00:00:0000' AND '2015-05-12 00:00:0000' ;

use DATEDIFF to get the difference between two dates

SELECT DATEDIFF(day,'2014-06-05','2014-08-05') AS DiffDate

once you get the difference between all the products..seletct the one with maximum value

Upvotes: 0

Kaabar Bilel
Kaabar Bilel

Reputation: 11

You can try the diffrence 'end_time - start_time'. Select * from where ip=ip end_time - start_time= max(select end_time - start_time from ...) (s.thg like that)

Upvotes: 0

Related Questions