Search code examples
sqlpostgresqlgtfs

Best way to store time above 24:00:00 in postgresql?

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?

Solution

  • 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