Store time in VARCHAR or seconds INT which is the best way?

Question

I am building a project with GTFS datas I got from my local bus company and I am using PostgreSQL I am new with to learn to use it. The time datas are given in a HH:MM:SS (24 hours) format, but the time happens to go beyond PostgreSQL's range like 24:01:00 where the service started the day before and is continuing the next day during the night after midnight.

I thought storing time in a VARCHAR field to keep the HH:MM:SS schema and convert this in PHP using the strtotime() function. I read some thread here about the best way to store time above 24:00:00 in postgresql* where I could transform the values in a INT field. Thus, I imagined to store time that way and programmatically convert it back in HH:MM:SS but I am puzzled.

Should I convert time received from a CSV time in HH:MM:SS format to INT before putting those in the database as pgSQL does not accept anything beyond 24:00:00? Or store the time in string format and use PHP's function strtotime() to convert time so I can make it the way I want? How about performance if my application get used by people for both ways?

Bests regards

---

*I did not respond to the thread as it's nearly two years old.

---

EDIT: better explanations and sample data, 5 + 1 first lines

I'd like to keep the fact that's the data is about time. The bus might have started to drive around 23:00 today evening, and end its service at 00:30 tomorrow evening. In the GTFS data, this gives 24:30:00 but PostgreSQL does not consider is as valid data.

And here are the datas:

calendar.txt

service_id,monday,tuesday,wednesday,thursday,friday,saturday,sunday,start_date,end_date
B_2017-BW_A_J6-Samedi-03,0,0,0,0,0,1,0,20170304,20170527
B_2017-BW_A_J7-Dimanche-01,0,0,0,0,0,0,1,20170305,20170528
B_2017-BW_A_P2-Mercredi-06,0,0,1,0,0,0,0,20170308,20170531
B_2017-BW_A_P2-Mercredi-06-0010000,0,0,1,0,0,0,0,20170308,20170531
B_2017-BW_A_P2-Sem-N-3-06,1,1,0,1,1,0,0,20170306,20170601

calendar_dates.txt

service_id,date,exception_type
B_2017-BW_A_J7-Dimanche-01,20170417,1
B_2017-BW_A_J7-Dimanche-01,20170501,1
B_2017-BW_A_J7-Dimanche-01,20170525,1
B_2017-BW_A_P2-Mercredi-06,20170405,2
B_2017-BW_A_P2-Mercredi-06,20170412,2

routes.txt

service_id,date,exception_type
B_2017-BW_A_J7-Dimanche-01,20170417,1
B_2017-BW_A_J7-Dimanche-01,20170501,1
B_2017-BW_A_J7-Dimanche-01,20170525,1
B_2017-BW_A_P2-Mercredi-06,20170405,2
B_2017-BW_A_P2-Mercredi-06,20170412,2

stop_times.txt

trip_id,arrival_time,departure_time,stop_id,stop_sequence,pickup_type,drop_off_type
12223270-C2017-choi-Sem-Cong-35,24:01:00,24:01:00,Csslesc2,37,0,0
12223270-C2017-choi-Sem-Cong-35,24:03:00,24:03:00,Csygare2,38,0,0
12223270-C2017-choi-Sem-Cong-35,24:05:00,24:05:00,Csycant2,39,0,0
12223270-C2017-choi-Sem-Cong-35,24:08:00,24:08:00,Csychap4,40,0,0
12223270-C2017-choi-Sem-Cong-35,24:08:00,24:08:00,Csyplac1,41,0,0
12223270-C2017-choi-Sem-Cong-35,24:09:00,24:09:00,Csychap2,42,0,0
12223270-C2017-choi-Sem-Cong-35,24:09:00,24:09:00,Csyjumo2,43,0,0
12223270-C2017-choi-Sem-Cong-35,24:11:00,24:11:00,Csysans2,44,0,0
12223270-C2017-choi-Sem-Cong-35,24:13:00,24:13:00,Csytouq2,45,0,0
12223270-C2017-choi-Sem-Cong-35,24:14:00,24:14:00,Csuptou2,46,0,0
12223270-C2017-choi-Sem-Cong-35,24:15:00,24:15:00,Csufrom6,47,0,0
12223270-C2017-choi-Sem-Cong-35,24:16:00,24:16:00,Crcrlf2,48,0,0
12223270-C2017-choi-Sem-Cong-35,24:17:00,24:17:00,Crccano2,49,0,0
12223270-C2017-choi-Sem-Cong-35,24:17:00,24:17:00,Crcrwas2,50,0,0
12223270-C2017-choi-Sem-Cong-35,24:18:00,24:18:00,Crchutt2,51,0,0
12223270-C2017-choi-Sem-Cong-35,24:19:00,24:19:00,Crcegli4,52,0,0
12223270-C2017-choi-Sem-Cong-35,24:19:00,24:19:00,Crcpcom2,53,0,0
12223270-C2017-choi-Sem-Cong-35,24:20:00,24:20:00,Crcplac4,54,0,0
12223270-C2017-choi-Sem-Cong-35,24:21:00,24:21:00,Crccamp2,55,0,0
12223270-C2017-choi-Sem-Cong-35,24:23:00,24:23:00,Crclorc2,56,0,0
12223270-C2017-choi-Sem-Cong-35,24:26:00,24:26:00,Ccycont2,57,0,0
12223270-C2017-choi-Sem-Cong-35,24:27:00,24:27:00,Ccychba2,58,0,0
12223270-C2017-choi-Sem-Cong-35,24:28:00,24:28:00,Crbrgar2,59,0,0
12223270-C2017-choi-Sem-Cong-35,24:29:00,24:29:00,Ccybeau2,60,0,0
12223270-C2017-choi-Sem-Cong-35,24:31:00,24:31:00,Ccychap2,61,0,0
12223270-C2017-choi-Sem-Cong-35,24:32:00,24:32:00,Ccygara2,62,0,0
12223270-C2017-choi-Sem-Cong-35,24:33:00,24:33:00,Ccybouc4,63,0,0
12223270-C2017-choi-Sem-Cong-35,24:33:00,24:33:00,Ccyfroi1,64,0,0
12223270-C2017-choi-Sem-Cong-35,24:34:00,24:34:00,Ccyga8,65,0,0

stops.txt

stop_id,stop_code,stop_name,stop_desc,stop_lat,stop_lon,zone_id,stop_url,location_type
Baegd741,,"GEROMPONT Avenue des Déportés 74",,  50.651931,   4.890270,,,0
Baegd742,,"GEROMPONT Avenue des Déportés 74",,  50.651980,   4.890100,,,0
Baegegl1,,"AUTRE-EGLISE Eglise",,  50.663079,   4.923704,,,0
Baegegl2,,"AUTRE-EGLISE Eglise",,  50.663151,   4.923620,,,0
Baeggar1,,"AUTRE-EGLISE Gare",,  50.663857,   4.918398,,,0

trips.txt

route_id,service_id,trip_id,trip_short_name,direction_id,block_id,shape_id
B0001-12254,B_2017-BW_A_P2-Mercredi-06,12712028-B_2017-BW_A_P2-Mercredi-06,3,0,3192097,B00010001
B0001-12254,B_2017-BW_A_P2-Mercredi-06,12712029-B_2017-BW_A_P2-Mercredi-06,9,0,3192088,B00010001
B0001-12254,B_2017-BW_A_P2-Mercredi-06,12712030-B_2017-BW_A_P2-Mercredi-06,13,0,3192097,B00010001
B0001-12254,B_2017-BW_A_P2-Mercredi-06,12712031-B_2017-BW_A_P2-Mercredi-06,21,0,3192092,B00010001
B0001-12254,B_2017-BW_A_P2-Mercredi-06,12712032-B_2017-BW_A_P2-Mercredi-06,23,0,3192096,B00010001

Answer 1

I have a cardinal rule for date maths. Don't do it. Always let some other package you know is consistent do it. Like your database. PostgreSQL is REALLY good at date maths.

Just store the time as an interval, then add it to a zero date (today, yesterday etc 00:00:00 time).

smarlowe=# create table intervals (i interval);
CREATE TABLE
smarlowe=# insert into intervals values ('24:01:01');
INSERT 0 1
smarlowe=# insert into intervals values ('48:01:01');
INSERT 0 1
smarlowe=# insert into intervals values ('129:01:01');
INSERT 0 1
smarlowe=# select i+'2017-04-15 00:00:00'::timestamp from intervals ;
      ?column?       
---------------------
 2017-04-16 00:01:01
 2017-04-17 00:01:01
 2017-04-20 09:01:01

Answer 2

The best way to store durations in PostgreSQL is to use the interval data type.

Duration (3 hours) and time of day (3 o'clock) have similar formats (3:00), but they mean different things.