4

I'm storing GTFS feeds into a SQL database and some times are expected to be stored above the 24:00:00 cap on time values. For example, some trains run at 12:30AM, but are listed for the previous days service, and that running time is stored as 24:30 in the GTFS specifications.

What would be the best way of getting around this? Should I just store it as a string?

Jimmy Gong
  • 1,825
  • 5
  • 20
  • 35
  • Is there a natural cut-off, e.g. like 3am? What are the reasons for this fancy way instead of just returning previous day for time component before that 3am cut-off? – mlt Sep 08 '15 at 19:47
  • Not really sure, but they just prefer to lump late-night runs with the previous day. The cut-off point is an interesting idea, I could hypothetically then just set the cutoff time as 00:00:00 since it would still be a 24 hour cycle. The struggle with that is different transit agencies may have different cut-off times, but I could just store the cutoff time in a separate column to help track what 00:00:00 means. – Jimmy Gong Sep 08 '15 at 20:01
  • 1
    That's interesting, because you really are storing a time not an interval. Usually when people ask this they want to store a number of hours, not an actual time. One option is to normalize the times by bumping the service over into the next day and subtracting 24 hours from the time. You could (ab)use `interval`, but it's kind of ugly. In this case falling back to integer seconds might make sense. – Craig Ringer Sep 09 '15 at 00:39

2 Answers2

5

Suggest to use int for that... your value could be:

Sec + Min * 60 + Hour * 3600

For the 24:30:00, you will get 88200.

When loading your value from DB, you could reverse your value by simple math equation:

Hour = int(value / 3600)
Min  = int(value % 3600 / 60)
Sec  = value % 3600 % 1800
EH Ong
  • 66
  • 3
  • This makes a number of calculations (such as computing wait times) much easier and I have found this to be the best approach. Also, note the times specified in GTFS are not truly wall-clock times but rather [offsets from "noon minus 12h"](https://developers.google.com/transit/gtfs/reference?hl=en#stop_timestxt), which this numeric representation makes slightly more explicit. –  Sep 09 '15 at 11:34
1

I'd store two fields:

departure_time timestamp with time zone,
service_date date

Departure time would be calculated like this:

 => select '2015-07-08'::timestamptz+'24:30'::interval;
 2015-07-09 00:30:00+02

This way:

  • you have a normal moment of time field for sorting events;
  • you'd not loose service date information;
  • you'd be able to calculate back the original GTFS data if needed.
Tometzky
  • 22,573
  • 5
  • 59
  • 73