How can i extract all trips of one stop on one specific day of a GTFS-DB

69 Views Asked by At

List all trips of one stop of one day

I used this SQL-Command to extract all stops.

SELECT r.route_short_name, st.arrival_time, st.departure_time, t.trip_id, st.trip_id, t.service_id, cd.date , cd.service_id , c.service_id FROM stop_times st 
JOIN trips t ON t.trip_id = st.trip_id
JOIN routes r ON r.route_id = t.route_id
JOIN calendar_dates cd ON cd.service_id = t.service_id
JOIN calendar c ON c.service_id = cd.service_id

WHERE  '20231208' BETWEEN c.start_date AND c.end_date
AND

st.stop_id = 'de:05314:63155' AND
STR_TO_DATE(st.arrival_time, '%H:%i:%s') 
      BETWEEN '13:40:00' AND '15:00:00'

AND c.service_id NOT IN (SELECT cd2.service_id FROM calendar_dates cd2 WHERE cd2.date =20230508)



GROUP BY st.trip_id
ORDER BY st.arrival_time ASC

I got the results but there are some trips, which are not valid to the actual time table.

0

There are 0 best solutions below