Using “Time” in PostgreSQL/PostGIS

Question

I'm loading gtfs into a PostGIS database but I have a problem when loading my stop times.

The data is in time format "00:00:00" and for 1:30 AM, my data is "25:30:00".

I'd like to keep it that way because usually, night buses services start before midnight so I can keep track of the schedule by time, but PostgreSQL doesn't accept time after 24:00:00.

I could modify my script (python) so when it comes to treating stop_times.txt, to modify the time to create something like 2530 so I'll have to deal with integer, but I'm not the only one that will have to deal with that db and my colleague would really prefer to deal with time instead of integers...

Does anyone could think of a way to "bypass" this limitation?

Thanks!!

Answer 1

You could have two columns: one with a proper time stamp where 25:30 is converted to 1:30 and one with a text '25:30' or integer if that's easier to work with for you.