CODEWITHSUNDEEP
mysqlsqldatabase
Fishingfon
Fishingfon

Reputation: 1034

How to select column with greatest difference between dates - MySQL

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

No matter what i do, i cant seemt to figure out how to get this info.

This is the table:

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),
  FOREIGN KEY (EPC) REFERENCES Product(EPC) ON DELETE CASCADE,
  FOREIGN KEY (ip) REFERENCES RFID_Reader(ip) ON DELETE CASCADE
);

And this is the data in the table:

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

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

Thanks Corey

Upvotes: 0

Views: 78

Answers (1)

pala_
pala_

Reputation: 9010

Sounds like you need the difference between the max and min per epc, so this:

select epc, max(`datetime`), min(`datetime`), timediff(max(`datetime`), min(`datetime`))
  from Track_Record
  order by timediff(max(`datetime`), min(`datetime`)) desc
  limit 1;

Results from your sample data above:

+-----------------------------+---------------------+---------------------+--------------------------------------------+
| epc                         | max(`datetime`)     | min(`datetime`)     | timediff(max(`datetime`), min(`datetime`)) |
+-----------------------------+---------------------+---------------------+--------------------------------------------+
| 03.0000A89.00016F.000169DCD | 2015-10-15 18:23:18 | 2011-03-01 11:43:26 | 838:59:59                                  |
+-----------------------------+---------------------+---------------------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Upvotes: 3

Related Questions