SQLite query to get upcoming bus times for the next hour for a bus stop from GTFS data.

getNextBusTimesForStop.sql

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

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