0

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
Community
  • 1
  • 1
Palingenae
  • 13
  • 4
  • Edit your question, and include some sample data. – Mike Sherrill 'Cat Recall' Apr 17 '17 at 11:13
  • *"The bus might have started to drive around 23:00 [tonight], and end its service at 00:30 tomorrow morning."* Can you explain how the pair of times {23:00, 24:30} communicates something different than the pair of times {23:00, 00:30}? Or do they carry the same meaning to people who use the system? – Mike Sherrill 'Cat Recall' Apr 23 '17 at 16:29

2 Answers2

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • It's not a duration, i would use this for the duration of a travel. I'd rather tell "that bus is coming at 16:37 and arrives at _the destination you gave_ at 16:58". I'd only use `interval` for the 21 minutes taken by the journey. – Palingenae Apr 16 '17 at 21:10
  • @Krands: In that example, you're leaving out some information that's critical for travel: the date. It's not that the bus is coming at 16:37 and gets to your destination at 16:58, it's that the bus is coming at 16:37 *today* and gets to your destination at 16:58 *today*. – Mike Sherrill 'Cat Recall' Apr 17 '17 at 11:20
  • I sure did not chose the good time example, but I edited the question with the needed data. – Palingenae Apr 17 '17 at 20:55
0

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
Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21