KNOWLEDGE BASE

## Extract Time from Date and Time Field

Published: 09 Jun 2015

### 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.

Tableau Desktop

### 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.