Last Modified Date: 03 Jun 2016
Step 1Extract Time information, return this as a string formatted as hh:mm:ss.
(Attached example: Date to Time_modified.twbx)
- Create a calculated field called ”days”:
DATETRUNC('day', [Order Date]). This will truncate the specified date to the accuracy of day-level.
- Create a calculated field called “time”:
DATEDIFF('second',[days],[Order Date]). This will return the difference of amount of seconds.
- Create a calculated field called “HH:MM:SS”. This will format the amount of seconds into HH:MM:SS:
STR(INT(SUM([time])/3600)) + ":" + IF INT(SUM([time])%3600/60) < 10 THEN "0" ELSE "" END + STR(INT(SUM([time])%3600/60)) + ":" + IF INT(SUM([time]) %3600 %60) < 10 THEN "0" ELSE "" END + STR(INT(SUM([time])%3600 %60))
Step 2Convert the string field to decimal value, which can be represented as continuous measure, and formatted as hh:mm:ss.
- Convert the data to a fractional representation of the day, with the Calculated field 'FLOAT for Time of Day':
FLOAT( DATETIME( [Time of Day (string)] ) ).
- Use Custom Number formatting to format these results as hh:mm:ss.
Additional InformationAlternate Step 1, to return string in hh:mm:ss format, if the DateTime information is already a string with the correct Time formatting:
(used in Example: Cont_Values_for_Time_Only.twbx)
Bring in the DateTime field into Tableau as a STRING field, and isolate the Time Data by itself in a Calculated field named 'Time of Day':
RIGHT( STR( [Play Time] ),8 )
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials