14 Replies Latest reply on Jun 18, 2018 8:46 PM by Chris McClellan

# Calculation of total duration with regards to multiple timestamp

Hi, As shown below is a small portion of my data:

Model,Timestamp,long,lat

x,14/1/2018 1:06:12 PM,103.764280,1.331031

x,14/1/2018 1:06:12 PM,103.764253,1.330950

x,14/1/2018 1:06:11 PM,103.764242,1.330870

x,14/1/2018 1:06:11 PM,103.764226,1.330784

x,14/1/2018 1:06:10 PM,103.764210,1.330709

x,14/1/2018 1:06:10 PM,103.764189,1.330645

x,14/1/2018 1:06:09 PM,103.764178,1.330575

x,14/1/2018 1:06:09 PM,103.764172,1.330495

x,14/1/2018 1:06:08 PM,103.764162,1.330414

y,14/1/2018 1:06:08 PM,103.764162,1.330355

x,14/1/2018 1:06:07 PM,103.764156,1.330275

y,14/1/2018 1:06:07 PM,103.764146,1.330205

y,14/1/2018 1:06:06 PM,103.764140,1.330108

y,14/1/2018 1:06:06 PM,103.764124,1.330039

I would like to ask if there is any ways to calculate the total duration of  each model(x and y) based on the timestamp?

This is a vehicle tracking data so i would like to track each model's runtime on the road

• ###### 1. Re: Calculation of total duration with regards to multiple timestamp

Hello there ,
Is this something that you are trying to acheive ?
To do this we need to create two time for each model ( maximum time stamp and minimum time stamp as under )

Once done create the calculation for calculating the time difference in seconds as under :

Hope this helps
Thanks
Rahul

• ###### 2. Re: Calculation of total duration with regards to multiple timestamp

Hi!

It looks like something that I'm looking at. But if i have 300k rows of such data with multiple model and timing, by doing the method as shown above, will it automatically mapped to its min time and max time of the certain model?

• ###### 3. Re: Calculation of total duration with regards to multiple timestamp

And I also would like to know how to see the time difference in hh:mm:ss format if possible!
Thanks for your help!

• ###### 4. Re: Calculation of total duration with regards to multiple timestamp

This looks the same as the question you posted the other day : Calculating Distance between multiple coordinates for multiple routes

On that thread I showed you how to do it, basically calculate the difference between each point (and the previous point) then add all the distances to give the total trip distance.

• ###### 5. Re: Calculation of total duration with regards to multiple timestamp

Hi! the last thread i posted was on distance. Am i right to say that the logic to find distance and duration works the same?

• ###### 6. Re: Calculation of total duration with regards to multiple timestamp

Ah sorry, I didn't realise this one was for time

If you've only got Model,Timestamp,long,lat you don't have enough data to tell one trip from the next.  Is there another field that you can use to do that ?

Or a rule about the breaks between trips, or no trip is in progress over midnight ?

• ###### 7. Re: Calculation of total duration with regards to multiple timestamp

Hello,

To see the time difference in HH:MM:SS you will have to do a bit of calculations.
The below post will guide you completely to it.
http://www.vizzingdata.com/tableau-tips-datetime-difference-in-the-format-ddhhmmss/

Thanks
Rahul

• ###### 8. Re: Calculation of total duration with regards to multiple timestamp

Hi!
Thank you for the help! Everything looks perfect!

• ###### 9. Re: Calculation of total duration with regards to multiple timestamp

Hi sorry to trouble again. I've tried doing it in a "1 day" log file and it works. But when i do it on a "month" basis, the duration for all the dates for each and every particular models are the same.

E.g.

Model X on 15th May has a duration of 5 hours 10mins 3secs.

It is the same duration for my 21th,23th and 24th data for Model X too. Any reasons or solution to tackle such issue?

• ###### 10. Re: Calculation of total duration with regards to multiple timestamp

If you've only got Model,Timestamp,long,lat you don't have enough data to tell one trip from the next.  Is there another field that you can use to do that ?

Or a rule about the breaks between trips, or no trip is in progress over midnight ?

If you only want the calculation to be for a day you can change all the LOD to    {FIXED [Model] datetrunc('day', [Timestamp] .....

• ###### 11. Re: Calculation of total duration with regards to multiple timestamp

Hi, As i am really very new to all this, may i ask what this datetrunc actually do?

• ###### 12. Re: Calculation of total duration with regards to multiple timestamp

The end state of my visualization is to see:

1. total duration of the vehicle mode

2. Total duration of the different vehicle each day

3. Total duration of the different vehicle in each mode(total of 4 mode: 1,2,3 and 4)

As I was trying to understand and learn the logic behind how the duration calculation operates,I did not include the mode into the question. Sorry for any confusion to the questions.

But I would like to know how to segregate between months and mode.

The data field overall consist of Mode, model, Timestamp, Lat, Long, Speed

Would greatly appreciate if anyone can explain or direct me to a certain direction to complete point 2 and 3.

As of now, I'm only able to calculate the total duration for each vehicle(regardless of month or dates, just total runtime)

UPDATE: I'm able to mapped the duration to its individual mode and model. But now another issue is that:

e.g. The whole duration of my Model X is 5 hours 3 second. My mode 1 has a total of 5hours 3 second, but inbetween this 5 hour, it actually transit to mode 2 for 10mins.

But tableau capture mode 1 as 5 hour 3 seconds , adding both mode 1 and 2, it should give me my Model X total timing.

But it actually overlaps.

|            1hour(Mode 1)           |  5mins(Mode 2)  |          1hour(Mode 1)             | 1min(Mode2) |   2min(Mode1)  |   4mins(Mode 2)  |           2hours 56mins 3second(Mode1)            |

The actual data will have lots of transition, I would like to piece up all Mode 1's total duration and Mode 2's duration.

But currently it gives me overlaps duration

• ###### 13. Re: Calculation of total duration with regards to multiple timestamp

DATETRUNC will round off the timestamp to a day, that will enable you to get the total duration by day of each mode.