Found my problem...
Thanks anyways :)
-- Chris.
I love deadlines. I especially love the whooshing sound they make as they fly by..." - Douglas Adams, 'Hitchhiker's Guide to the Galaxy'
----- Original Message ----- From: "Chris Knipe" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, May 05, 2005 4:09 PM
Subject: where and dates
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
