KNOWLEDGE BASE

Extract Time from Date and Time Field


Published: 09 Jun 2015
Last Modified Date: 24 Apr 2017

Issue

How to take a field with date and time and create a new field with just the time (hh:mm:ss), which can be represented as a continuous value.

Environment

Tableau Desktop

Resolution

Step 1

Extract Time information, return this as a string formatted as hh:mm:ss.
(Attached example: Date to Time_modified.twbx)
  1. Create a calculated field called ”days”: DATETRUNC('day', [Order Date]). This will truncate the specified date to the accuracy of day-level.
  2. Create a calculated field called “time”: DATEDIFF('second',[days],[Order Date]). This will return the difference of amount of seconds.
  3. 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 2

Convert the string field to decimal value, which can be represented as continuous measure, and formatted as hh:mm:ss.
  1. 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)] ) ).
  2. Use Custom Number formatting to format these results as hh:mm:ss.

Additional Information

Alternate 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 )
Did this article resolve the issue?