一千萬個為什麽

搜索

MySQL查詢不返回任何數據

我需要從特定時間段檢索數據。 查詢工作正常,直到我指定時間段。我指定時間段的方式有問題嗎?我知道在這個時間範圍內有很多條目。

此查詢返回空:

SELECT  stop_times.stop_id, STR_TO_DATE(stop_times.arrival_time, '%H:%i:%s') as stopTime, routes.route_short_name, routes.route_long_name, trips.trip_headsign  FROM trips 
JOIN stop_times ON trips.trip_id = stop_times.trip_id 
JOIN routes ON routes.route_id = trips.route_id
WHERE  stop_times.stop_id = 5508
HAVING stopTime BETWEEN DATE_SUB(stopTime,INTERVAL 1 MINUTE) AND  DATE_ADD(stopTime,INTERVAL 20 MINUTE);

這是解釋:

+----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys    | key     | key_len | ref                           | rows | Extra       |
+----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+
|  1 | SIMPLE      | stop_times | ref    | trip_id,stop_id  | stop_id | 5       | const                         |  605 | Using where |
|  1 | SIMPLE      | trips      | eq_ref | PRIMARY,route_id | PRIMARY | 4       | wmata_gtfs.stop_times.trip_id |    1 |             |
|  1 | SIMPLE      | routes     | eq_ref | PRIMARY          | PRIMARY | 4       | wmata_gtfs.trips.route_id     |    1 |             |
+----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+
3 rows in set (0.00 sec)

如果我刪除HAVING子句(不指定時間範圍),查詢將起作用。返回:

+---------+----------+------------------+-----------------+---------------+
| stop_id | stopTime | route_short_name | route_long_name | trip_headsign |
+---------+----------+------------------+-----------------+---------------+
|    5508 | 06:31:00 | "80"             | ""              | "FORT TOTTEN" |
|    5508 | 06:57:00 | "80"             | ""              | "FORT TOTTEN" |
|    5508 | 07:23:00 | "80"             | ""              | "FORT TOTTEN" |
|    5508 | 07:49:00 | "80"             | ""              | "FORT TOTTEN" |
|    5508 | 08:15:00 | "80"             | ""              | "FORT TOTTEN" |
|    5508 | 08:41:00 | "80"             | ""              | "FORT TOTTEN" |
|    5508 | 09:08:00 | "80"             | ""              | "FORT TOTTEN" |

我正在使用加載到MySQL中的 Google Transit格式數據。

該查詢應該為給定的公共汽車站提供停靠時間和公交路線。 對於一個公共汽車站,我想得到:

  1. 路線名稱
  2. 公交車名稱
  3. 巴士方向(隊長)
  4. 停止時間 結果應僅限於從1分鐘前到現在20分鐘的公交車時間。

如果你能提供幫助,請告訴我。

UPDATE The problem was that I was comparing DATE to DATETIME as one answer said. I could not use DATE because my values had times but not dates. So my solution was to use Unix time:

 SELECT  stop_times.stop_id, stop_times.trip_id,   UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(NOW(),'%Y-%m-%d '), stop_times.arrival_time)) as stopTime, routes.route_short_name, routes.route_long_name, trips.trip_headsign  FROM trips 
JOIN stop_times ON trips.trip_id = stop_times.trip_id 
JOIN routes ON routes.route_id = trips.route_id
WHERE  stop_times.stop_id = 5508
HAVING stopTime > (UNIX_TIMESTAMP(NOW()) - 60) AND stopTime < (UNIX_TIMESTAMP(NOW()) + (60*20));

最佳答案

停止時間是一個時間值, DATE_ADD /SUB使用日期時間字段。確保它們都是同一類型。

轉載註明原文: MySQL查詢不返回任何數據