Hi,
I dont think it's needed to go into to much details here.... but the query:
SELECT CONCAT(Airports.IATA, ' - ', Airports.Name) AS ArrivingFrom,
ADDTIME(FlightData.TimeStamp, SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s'))) AS ArrvTime,
CONCAT(FlightData.Network, ' - ', FlightData.PilotCallSign) AS FlightNum
FROM FlightData
LEFT JOIN Airports ON FlightData.AirportDep=Airports.ICAO
WHERE FlightData.AirportDes='KJFK'
AND DATE_SUB(CURDATE(),INTERVAL 2 HOUR) <= ADDTIME(FlightData.TimeStamp, SUBTIME(FlightData.Enroute,DATE_FORMAT(SUBTIME(FlightData.TimeStamp,FlightData.ActDepTime),'%H:%i:%s')))
GROUP BY FlightData.PilotCallSign
ORDER BY ArrvTime;
Result: +-----------------------------------+---------------------+---------------+ | ArrivingFrom | ArrvTime | FlightNum | +-----------------------------------+---------------------+---------------+ | YYZ - Toronto/Pearson Intl | 2005-05-04 22:22:00 | V - ACA114 | | LHR - Heathrow | 2005-05-04 22:42:00 | V - BAW115 | | - Norman Manley Intl | 2005-05-04 22:45:00 | V - AAL645 | | BOS - Logan Intl | 2005-05-04 22:50:00 | V - AE729 | | NULL | 2005-05-05 00:00:00 | V - N6340D | | JFK - Kennedy Intl | 2005-05-05 00:00:00 | V - AFA5290 | | MCO - Orlando Intl | 2005-05-05 00:05:00 | V - FEDEX1472 | | MCO - Orlando Intl | 2005-05-05 00:25:00 | V - FEDEX1563 | | BOS - Logan Intl | 2005-05-05 01:23:00 | V - AAL328 | | YYZ - Toronto/Pearson Intl | 2005-05-05 01:40:00 | V - ACA221 | | TPA - Tampa Intl | 2005-05-05 02:10:00 | V - N171E | | BOS - Logan Intl | 2005-05-05 02:10:00 | V - N37TJ | | MIA - Miami Intl | 2005-05-05 02:40:00 | V - JAL5837 | | - Eduardo Gomes Intl | 2005-05-05 02:42:00 | V - PST9864 | | CVG - Cincinnati/Northern Ky Intl | 2005-05-05 03:15:00 | V - MTN219 | | YYZ - Toronto/Pearson Intl | 2005-05-05 03:45:00 | V - UAL392 | | BOS - Logan Intl | 2005-05-05 03:50:00 | V - DVA2314 | | JFK - Kennedy Intl | 2005-05-05 05:30:00 | V - WSM-9950 | | YWG - Winnipeg Intl | 2005-05-05 05:40:00 | V - ML853 | | ZRH - Zurich | 2005-05-05 05:50:00 | V - AAL213 | | ATL - The Hartsfield Atlanta Intl | 2005-05-05 06:05:00 | V - AAL209 | | SJU - Luis Munoz Marin Intl | 2005-05-05 06:17:00 | V - AAL117 | | LHR - Heathrow | 2005-05-05 08:40:00 | V - BAW01 | | ZRH - Zurich | 2005-05-05 13:00:00 | V - LH3733 | | PRG - Ruzyne | 2005-05-05 13:10:00 | V - CSA255 | | MAN - Manchester | 2005-05-05 15:30:00 | V - BAW1503 | | VIE - Schwechat | 2005-05-05 17:20:00 | V - AUA85Y | +-----------------------------------+---------------------+---------------+
Problem... Well, the date clause on the WHERE part of the query is not doing what I am hoping it would. I need to get all records where ArrvTime is bigger than (i.e. in the future) of NOW() + 2Hrs. All my dates in the database are stored in GMT, so I only need to use NOW()-2H (which is the same as DATE_SUB(CURDATE(),INTERVAL 2 HOUR)).
Can anyone help perhaps?
-- Chris.
I love deadlines. I especially love the whooshing sound they make as they fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy'
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
