List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:May 5 2005 2:25pm
Subject:Re: where and dates
View as plain text  
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" <savage@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
> 

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