Skip to content
Docs

Subway departures

One row each time a train leaves a station, April 2021 to 3 October 2026 from a public archive of the MTA's subway feeds and since 4 October 2026 from our own collector.

Rows
409,614,609
Size
8.2 GB
Covers
April 2021 onward (no L train before 15 December 2021)
Updated
Daily
Source
MTA subway GTFS-realtime feeds
License
CC BY 4.0
Fileshttps://data.transitlab.nyc/v1/subway_stop_events/year=YYYY/month=MM/YYYY-MM-DD.parquetOne file a day, 2,016 so far. In the database as transitlab.subway_stop_events.

Rows and size from catalog.json, 2026-10-08.

One train leaving one station. Every 10 seconds our collector reads the MTA’s eight subway feeds. Each feed lists, for every train, the stops it has yet to make. When a stop drops off a train’s list, the train has just left it, and we write a row with departed_at set to the feed time of that snapshot.

When a whole trip vanishes from the feed, its remaining stops get rows with event = 'trip_ended' and departed_at set to when the trip was last seen. Leave those out of run-time work.

This is the source of subway segment times.

Every row has a source.

sourceDaysHow they differ
archive1 April 2021 to 3 October 2026From a public archive of the MTA’s subway feeds, which marks a stop as passed the same way we do. feed and first_seen are null. A trip-stop has one departed row (rare repeats are dropped). A stop with no passing time becomes a trip_ended row at the time the train was last seen. row_id and trip_uid have the same form as ours.
collector4 October 2026 onOur own collector, as described above.

Both have the same columns and types, and both are cut into files by the UTC day of departed_at, so one query reads both. The two agree where they overlap: on 5 October 2026, for trips and stops in both, the median difference in departed_at is 0 seconds and 99% are within 15 seconds.

What is missing:

  • No L train from 1 April to 14 December 2021. The archive did not read the L’s feed until 15 December 2021.
  • The first four hours of 1 April 2021 (UTC): the archive starts at New York midnight, so the evening of 31 March is not in it.
  • About 16 hours of 4 October 2026 (UTC): our collector started that afternoon, and we use the archive only before that day.
  • Every other day from 1 April 2021 to 3 October 2026 is there.
SQL
-- How long did southbound L trains take from Union Sq to 3 Av yesterday?
WITH dep AS (
SELECT trip_uid, stop_id, max(departed_at) AS departed_at -- the last departure is the real one
FROM transitlab.subway_stop_events
WHERE event = 'departed' AND route_id = 'L'
AND stop_id IN ('L03S', 'L05S')
AND departed_at >= current_date - 1 AND departed_at < current_date
GROUP BY trip_uid, stop_id
)
SELECT a.trip_uid,
a.departed_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS left_union_sq,
date_diff('second', a.departed_at, b.departed_at) AS seconds
FROM dep a JOIN dep b USING (trip_uid)
WHERE a.stop_id = 'L03S' AND b.stop_id = 'L05S'
ORDER BY left_union_sq;
row_idvarchar

Stable ID of the row. If a row appears twice, keep one.

observed_attimestamp

When we wrote the row (UTC).

eventvarchar

departed, or trip_ended when a whole trip dropped out of the feed.

feedvarchar

Which of the MTA's subway feeds the train came from. Null in archive rows.

trip_uidvarchar

Our ID for the train trip.

trip_idvarchar

MTA trip ID.

route_idvarchar

Subway route.

start_datevarchar

Service date, YYYYMMDD.

stop_idvarchar

GTFS stop ID, with direction.

arrivaltimestamp

The MTA's last predicted arrival (UTC).

departuretimestamp

The MTA's last predicted departure (UTC).

first_seentimestamp

First time the stop appeared in the feed (UTC). Null in archive rows.

last_seentimestamp

Last time it appeared (UTC).

departed_attimestamp

Feed time of the first snapshot without the stop: just after the train left (UTC).

sourcevarchar

archive for rows imported from the public archive (before 4 October 2026), collector for rows from our own collector.

  • Use the last departure per trip and stop. In our own data the feed sometimes drops a stop and adds it back, so about 3% of trip-stops have more than one departed row. The last one is right; the first can be minutes early. Archive rows have almost no repeats, so row counts jump when source changes: count trip-stops, not rows.
  • Times are UTC without a zone. Write departed_at AT TIME ZONE 'UTC' to get a proper timestamp before you turn it into New York time.
  • Times are within about 10 to 15 seconds. That is how often the feed refreshes. Averages over many trains are sharper than that.
  • How we checked it against the older archive: Our subway collector.