List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:May 5 2005 2:09pm
Subject:where and dates
View as plain text  
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' 

Thread
where and datesChris Knipe5 May
  • Re: where and datesChris Knipe5 May