Solved

time conversions over midnight

  • 24 August 2018
  • 3 replies
  • 8 views
kimo
Badge +1

The new DatetimeConverter works well for the gtfs stop times which are stored as only a time string field. But it rejects (naturally) any times greater or equal to 24:00:00. The gtfs convention is to roll over midnight for bus routes that do not finish before midnight. How might that be handled?

I am trying to calculate the total time of a route using Arrival_Time - Departure_Time.

I cannot just replace the 24 with 00 because I have to add up two intervals. I can see a clumsy three part conversion. There are 1.4M records so elegance and speed would be welcome.

icon

Best answer by takashi 24 August 2018, 05:43

View original

3 replies

C
Badge

GTFS datetimes with hours >= 24 are not strictly valid datetimes. They're more like a date plus an interval value - this is the approach I use when I load GTFS data into databases. I'd consider separating the date part from the time part of the arrival/departure times, and then parsing the time part into a "seconds from midnight" value.

takashi
Rank
Userlevel 2
Badge +17

Just an idea. If you add a date (YYYYmmdd) to the time string as prefix after formatting the time string, you can calculate interval between two datetime values correctly. For example, add "20180101" if the formatted time is greater than "040000", "20180102" otherwise.

kimo
Badge +1

Thanks the suggestions. I did a combination of these two approaches. The times are already split because they are a general trip record for any date. I used a test filter to split into three types: today, span_midnight, tomorrow. Today was left alone, tomorrow were split, 24 subtracted from the hour part and then the h,m,s reassembled into a string. Span_midnight were parsed like tomorrow and a dummy date added to each one, one day apart. Then the new time interval arithmetic gave me the duration in minutes. Since it is only the 'night buses' that are a problem there were only 700 out of the million so the extra processing was trivial.

Reply


Community Stats

28,707
Posts
109,479
Replies
36,553
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.