Skip to content

Instantly share code, notes, and snippets.

@engelmav
Created November 23, 2012 16:57
Show Gist options
  • Save engelmav/4136471 to your computer and use it in GitHub Desktop.
Save engelmav/4136471 to your computer and use it in GitHub Desktop.
all_buses_morning_commute
SELECT
BusRoute.route_id
,bustrip.trip_id
,bustrip.trip_headsign
,BusStopTime.arrival_time
,busstopTime.stop_sequence
,busstop.stop_name
--,BusTripShape.shape_pt_sequence
FROM routes BusRoute
LEFT JOIN trips BusTrip
ON BusRoute.route_id = BusTrip.route_id
LEFT JOIN stop_times BusStopTime
ON BusTrip.trip_id = BusStopTime.trip_id
LEFT JOIN stops BusStop
ON BusStopTime.stop_id = BusStop.stop_id
WHERE
BusTrip.direction_id = '0' --this is outbound to NY
AND BusTrip.service_id = '9' --this is the weekday service
and
bustrip.trip_id IN (
SELECT trip_id FROM stop_times
WHERE
to_timestamp(arrival_time, 'HH24:MI:SS') < to_timestamp('09:00:00','HH24:MI:SS')
AND
to_timestamp(arrival_time, 'HH24:MI:SS') > to_timestamp('06:00:00','HH24:MI:SS')
AND stop_id = '3511')
ORDER BY
BusRoute.route_id
,BusTrip.trip_id
,BusStopTime.stop_sequence
ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment