MySQL query does not return any data

Question

I need to retrieve data from a specific time period. The query works fine until I specify the time period. Is there something wrong with the way I specify time period? I know there are many entries within that time-frame.

This query returns empty:

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);

Here is it's EXPLAIN:

+----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+
| 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)

The query works if I remove the HAVING clause (don't specify time range). Returns:

+---------+----------+------------------+-----------------+---------------+
| 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" |

I am using Google Transit format Data loaded into MySQL.

The query is supposed to provide stop times and bus routes for a given bus stop. For a bus stop, I am trying to get:

Route Name
Bus Name
Bus Direction (headsign)
Stop time The results should be limited only to buses times from 1 min ago to 20 min from now.

Please let me know if you could help.

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));

Andy · Accepted Answer · 2010-05-03 18:07:21Z

1

Stoptime is a time value, and DATE_ADD/SUB work with datetime fields. Ensure they are both the same type.

answered May 3, 2010 at 18:07

Andy

17.8k9 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon S · Accepted Answer · 2010-05-03 18:09:07Z

1

Try this instead:

SELECT * FROM
(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) AS qu_1
WHERE qu_1.stopTime BETWEEN DATE_SUB(qu_1.stopTime,INTERVAL 1 MINUTE) AND  DATE_ADD(qu_1.stopTime,INTERVAL 20 MINUTE);

Have to warn you I haven't tested this but it does remove the need for the HAVING clause.

answered May 3, 2010 at 18:09

Simon S

2276 silver badges17 bronze badges

Comments

David M · Accepted Answer · 2010-05-03 18:13:33Z

1

Don't work with the synthetic column stopTime other than as the output.

I think your query should be something like:

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
AND arrival_time BETWEEN <something> AND <something else>

The HAVING clause you wrote should always return true, so I'm guessing that's not what you really had in mind.

answered May 3, 2010 at 18:13

David M

4,3962 gold badges32 silver badges40 bronze badges

Collectives™ on Stack Overflow

MySQL query does not return any data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related