Skip to content

Instantly share code, notes, and snippets.

@danpoltawski
Created March 4, 2013 09:19
Show Gist options
  • Save danpoltawski/5081032 to your computer and use it in GitHub Desktop.
Save danpoltawski/5081032 to your computer and use it in GitHub Desktop.
sql query including bank holidays
SELECT t.trip_id, s.departure_time, t.trip_headsign
FROM gtfs_stop_times s
JOIN gtfs_trips t ON s.trip_id = t.trip_id
WHERE s.stop_id = 99005 AND s.departure_time >= "17:10:53"
AND s.danp_last_stop IS NULL
AND t.service_id IN (SELECT c.service_id FROM gtfs_calendar c
LEFT JOIN gtfs_calendar_dates d ON d.service_id = c.service_id AND d.date = "20130304"
WHERE c.start_date <= "20130304" AND c.end_date >= "20130304" AND c.monday = 1 AND d.service_id IS NULL
UNION SELECT service_id FROM gtfs_calendar_dates d WHERE d.date = "20130304" AND d.exception_type = 1)
ORDER BY s.departure_time LIMIT 2;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment