KNOWLEDGE BASE

Extract Time from Date and Time Field


Published: 09 Jun 2015
Last Modified Date: 19 Jun 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 day level.
  2. Create a calculated field called “time”: DATEDIFF('second',[days],[Order Date]). This will return the difference in seconds.
  3. Create a calculated field called “HH:MM:SS” to place the seconds in HH:MM:SS format:
    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 a decimal value, which can be represented as a 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 Format > Numbers > Custom to format these results as hh:mm:ss.
Did this article resolve the issue?