Adding travel times within timestamp intervals

[rossmg9]

I have a table as follows...

SEGMENT l TIMESTAMP l TRAVEL TIME (seconds)
1 12:00:00 am 65
1 12:05:00 am 45
1 12:10:00 am 45
1 12:15:00 am 54
2 12:00:00 am 35
2 12:05:00 am 73
2 12:10:00 am 41
2 12:15:00 am 59
......
all the way to SEGMENT 15 and timestamp 11:55:00 pm ... .
.. .... .


What I need to come up with is summation of the travel times to a certain segment (there are 15 segments in my study)..But the timestamps are in 5 minute intervals and if the summation if more that 300 seconds (5 minutes) then I need to use the travel time associated with the timestamp for 12:05:00 am.

A quick example would be to say I need the travel time to segment 8 starting at 12:00:00 am..so I could use the timestamp 12:00:00 am for say 5 segments, if I am assuming free-flow speed of 60 MPH (1 mile in 1 minute) but then for the next 3 segments, to segment 8, I would have to use the travel times associated with timestamp 12:05:00 am.

because for each segment there are 288 timestamps, every 5 minutes throughout the day...then each segment has a distance associated, each around 1 mile..I have also the average speed for that segment at each particular timestamp...that is where the travel time comes in..dividing the distance by the speed...

So my concern is when there I need a travel time for a distance longer than 5 miles...because when I just use the easiest example of 60 MPH that would be a mile a minute..then I would have to use the 12:05:00 AM timestamp at mile 6 and add that to the previous travel time assocaited with the 12:00:00 AM timestamp.

Travel times will change with the timestamp because say traffic is at 60 mph then at segment 8 ahead there is an incident at 12:06:00 am with lane blockage and backup is back to segment 6..the timestamp 12:00:00 am will not catch this event since it is outside the range of my previous timestamp of >12:00:00 am and <12:05:00 am.

Hope that helps and thanks for any assistance

 

[rossmg9]

anyone??

 

[Fifty2One]

Sorry maybe I do not understand the question but if each segment is 5 minutes then just add 5 minutes per segment - no?

 

[rossmg9]

No, each segment is only about 1 mile long and on average takes about 1 minute to traverse...but there are times when it takes longer or less than 1 minute and then I have to figure out the summation of the travel time over, for example, 9 segments, and have to use the travel time associated with the next timestamp and add that to the previous travel times from the timestamp before to get actual travel time....if that makes sense to you

Basic example, say I need travel time over 9 segments and each segment takes exactly 60 seconds to pass. So say I am starting at 1:00 AM, now I need to know when the travel time reaches 300 seconds or 5 minutes, which we know here will be at segment 5. So now the travel time for the remaining segments (6,7,8,9) are useless for the timestamp 1:00 AM. I now must use travel time for these remaining segments for the time stamp 1:05 AM and add those to the travel time from the 1:00 am timestamp

Hope that helps