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

Question

I store GTFS channels in an SQL database, and some times are expected to be stored above 24:00:00 time values. For example, some trains operate at 12:30, but are listed for the service of the previous days, and that operating time is stored as 24:30 in the GTFS specifications.

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

Answer 1

Suggest using int for this ... your value could be:

Sec + Min * 60 + Hour * 3600

Within 24:30:00 you will receive 88,200.

When loading your value from the database, you can change your value with a simple mathematical equation:

Hour = int(value / 3600)
Min  = int(value % 3600 / 60)
Sec  = value % 3600 % 1800

Answer 2

:

departure_time timestamp with time zone,
service_date date

:

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

:

• ;
• ;
• GTFS.