KNOWLEDGE BASE

Finding Non-Standard Date Parts


Published: 09 May 2017
Last Modified Date: 18 Jul 2018

Question

How to find non-standard date parts, such as millisecond.

Environment

Tableau Desktop

Answer

The attached example workbook uses sample data to demonstrate both of the following options. Option 1 is recommended, as manipulating strings can slow performance.

Option 1

  1. Select Analysis > Create Calculated Field
  2. Name the calculated field. In this example, the calculated field is named "Millisecond Datepart". Create a calculation similar to the following, then click OK:
    ( [Date] - DATETRUNC('second', [Date]) )* 86400000

Option 2

  1. Select Analysis > Create Calculated Field
  2. Name the calculated field. In this example, the calculated field is named "Millisecond ". Create a calculation similar to the following, then click OK:

    STR( [Date] )

  3. Drag [String Date] to the Rows shelf. Count the position of the millisecond number as a number of characters including spaces and punctuation.
    In this example, the date was imported in the format yyyy-dd-MM hh:nn:ss.SSSS. The millisecond number is the 21st to 23rd characters in this string.
  4. Create a calculated field with a name like "Millisecond Datepart" with a calculation similar to the following:

    INT( MID( STR( [Date] ), 21, 3 ) )

Additional Information

The DATETRUNC() function returns the same timestamp at a higher date level. In Option 1, subtracting the timestamp with zero milliseconds from the full timestamp. This calculation returns duration as number of days, thus the final step is to multiply by the number of milliseconds in a day.

 
Did this article resolve the issue?