CODEWITHSUNDEEP
timetableau-api
blackfury
blackfury

Reputation: 685

Tableau - Find time difference between two colums

I have two columns of data:

+-------------------------+-------------------------+
| StartTime               | FinishTime              |
+-------------------------+-------------------------+
| 2015.09.06 12:56:03 GMT | 2015.09.06 12:56:30 GMT |
| ...                     | ...                     |
+-------------------------+-------------------------+

I want another column to calculate duration.

I used DataDiff Function in Tableau, but i am getting an error.

Upvotes: 0

Views: 8847

Answers (2)

Vaibhav Fouzdar
Vaibhav Fouzdar

Reputation: 309

Following will give an output (duration:1h 2m 11s)

"(duration:" +
STR(CEILING(DATEDIFF('minute',[start_date],[end_date])/60)-1)+"h "+ STR(DATEDIFF('minute',[start_date],[end_date]) - ((CEILING(DATEDIFF('minute',[start_date],[end_date])/60)-1) * 60 )) + "m " +
STR(DATEDIFF('second',[start_date],[end_date]) % 60) + "s)"

Upvotes: 0

Andrew LaPrise
Andrew LaPrise

Reputation: 3423

Based on the data you showed, I'm guessing you want the difference in seconds between the times. You're right to use DATEDIFF(). (I noticed you said DATADIFF, so careful that you're using the right method.)

Use the following calculated field:

DATEDIFF('second', [StartTime], [FinishTime])

Adjust that first argument accordingly if I guessed wrong on the unit of time you want.

Edit to address comments:

If Tableau's having trouble automatically parsing StartTime and EndTime as datetimes, you can use DATEPARSE():

DATEPARSE("yyyy.MM.dd hh:mm:ss", [StartTime])

As for displaying the duration in hours, minutes, and seconds, DATEDIFF() just returns a single number, so you'll have to homebrew something. You'll need to create your own function so you can format the duration according to your needs, but here's one quick example that displays the duration as "hh:mm:ss" (you'll need to make a calculated field like I showed above to find the duration in seconds first):

RIGHT('0' + STR(FLOOR([Duration in Seconds]/60/60)), 2) + ':' + 
RIGHT('0' + STR(FLOOR([Duration in Seconds]/60)), 2) + ':' + 
RIGHT('0' + STR(FLOOR([Duration in Seconds])), 2)

On each line, you divide the duration in seconds by the appropriate divisor to convert to hours, minutes, or seconds. Then you FLOOR that number to trash the decimal. To deal with single digit numbers, you concatenate a '0' to the front of each number, then take the right two characters of the resulting string. (RIGHT('027', 2) yields '27', and RIGHT('04', 2) yields '04'.)

Upvotes: 2

Related Questions