Skip to main content

UNIXTIMEFORMAT Function

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.

Nota

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)

Argument

Required?

Data Type

Description

unixtime_col

Y

datetime

Name of column whose Unix timestamp values are to be formatted

date_format_string

Y

string

String 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 Type

Example Value

Yes

Datetime (formatted as Unix time integer values)

myDate

date_format_string

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

Nota

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 Type

Example Value

Yes

String

'MM/dd/yyyy'

Examples

Suggerimento

For additional examples, see Common Tasks.

Example - Unix timestamp formatting variations

Description

unixTimestamp column

Transform

newUnixTimestamp 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'

Nota

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