Page tree

Trifacta Dataprep



Contents:


   

Contents:


Formats a set of Unix timestamps according to a specified date formatting string.

Source values can be a reference to a column containing Unix timestamp values.

NOTE: Date values must be converted to Unix timestamps before applying this function. Unix time measures the number of milliseconds that have elapsed since January 1, 1970 00:00:00 (UTC). See UNIXTIME Function.

Supported format strings for this function are the same as the supported format strings for the DATEFORMAT function.

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

unixtimeformat(MyUnixDate, 'yyyy-MM-dd')

Output: Returns the Unix timestamp values in the MyUnixDate column, converted to year-month-day format.

Syntax and Arguments

unixtimeformat(unixtime_col, date_format_string)


ArgumentRequired?Data TypeDescription
unixtime_colYdatetimeName of column whose Unix timestamp values are to be formatted
date_format_stringYstringString literal identifying the date format to apply to the value

For more information on syntax standards, see Language Documentation Syntax Notes.

unixtime_col

Name of the column whose Unix time data is to be formatted.

  • Missing values for this function in the source data result in missing values in the output.
  • Multiple columns and wildcards are not supported.

Usage Notes:

 

Required?Data TypeExample Value
YesDatetime (formatted as Unix time integer values)myDate

date_format_string

String value indicating the date format to apply to the input values.

NOTE: If the platform cannot recognize the date format string, the generated result is written as a string value.

For more information on the supported formatting strings, see below.

  • Missing values for this function in the source data result in missing values in the output.
  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString'MM/dd/yyyy'

Examples

Tip: For additional examples, see Common Tasks.

Example - Unix timestamp formatting variations

DescriptionunixTimestamp columnTransformnewUnixTimestamp column
Numeric date, year first

1454946120000

1451433600000

1430032020000

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula unixtimeformat(unixTimestamp,'yyyy-MM-dd')
Parameter: New column name 'newUnixTimestamp'

2016-02-08

2015-12-30

2015-04-26

Numeric date, American style

1454946120000

1451433600000

1430032020000

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula unixtimeformat(unixTimestamp,'M/d/yy')
Parameter: New column name 'newUnixTimestamp'

2/8/16

12/30/15

4/26/15

Full written date

1454946120000

1451433600000

1430032020000

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula unixtimeformat(unixTimestamp,'MMMM dd, yyyy')
Parameter: New column name 'newUnixTimestamp'

February 08, 2016

December 30, 2015

April 26, 2015

Abbreviated date,
including abbreviated
day of week

1454946120000

1451433600000

1430032020000

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula unixtimeformat(unixTimestamp,'EEE MMM dd, yyyy')
Parameter: New column name 'newUnixTimestamp'

Mon Feb 08, 2016

Wed Dec 30, 2015

Sun Apr 26, 2015

Full 24-hour time

1454946120000

1451433600000

1430032020000

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula unixtimeformat(unixTimestamp,'HH:mm:ss.SSS')
Parameter: New column name 'newUnixTimestamp'

15:42:00.000

00:00:00.000

07:07:00.00

Twelve-hour time with AM/PM indicator

1454946120000

1451433600000

1430032020000

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula unixtimeformat(unixTimestamp,'h:mm:ss a')
Parameter: New column name 'newUnixTimestamp'

NOTE: For this function, use of the lower-case hour indicator (h or hh) requires the use of an AM/PM indicator (a).

3:42:00 PM

12:00:00 AM

7:07:00 AM

This page has no comments.