Skip to content

Instantly share code, notes, and snippets.

@dsamojlenko
Last active December 10, 2015 16:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dsamojlenko/4463113 to your computer and use it in GitHub Desktop.
Save dsamojlenko/4463113 to your computer and use it in GitHub Desktop.
SQLite query to get upcoming bus times for the next hour for a bus stop from GTFS data.
SELECT s.stop_name, r.route_short_name, t.trip_headsign, st.arrival_time, strftime('%H:%M:%S',datetime('now','localtime')) AS CurrentTime, strftime('%Y%m%d',date('now')) AS CurrentDate, strftime('%H:%M:%S',datetime('now', '+60 Minute','localtime')) AS TimeMax
FROM stops s
INNER JOIN stop_times st ON s.stop_id = st.stop_id
INNER JOIN trips t ON st.trip_id = t.trip_id
INNER JOIN routes r ON t.route_id = r.route_id
INNER JOIN calendar c ON t.service_id = c.service_id
WHERE c.service_id IN (
SELECT service_id
FROM calendar c
WHERE Strftime('%Y%m%d',date('now')) BETWEEN c.start_date AND c.end_date
AND c.tuesday = 1
AND service_id NOT IN (
SELECT service_id FROM calendar_dates WHERE date = Strftime('%Y%m%d',date('now'))
AND exception_type = 2
)
UNION
SELECT c.service_id
FROM calendar c
INNER JOIN calendar_dates cd ON c.service_id = cd.service_id
WHERE cd.exception_type = 1
AND cd.date = Strftime('%Y%m%d',date('now'))
)
AND s.stop_code = '3012'
AND st.arrival_time BETWEEN CurrentTime AND TimeMax
ORDER BY arrival_time
@dsamojlenko
Copy link
Author

  • there is a problem around midnight I have to deal with

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment