Contents:
Contents:
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. For more information on those string values, see Supported Data Types.
- For more information on formatting Unix or standard date formats, see DATEFORMAT Function.
- For more information on formatting numeric types, see NUMFORMAT Function.
Basic Usage
derive type:single value: UNIXTIMEFORMAT(MyUnixDate, 'yyyy-MM-dd') as: 'unixDate'
Output: Generates a column of Datetime values in the unixDate column, based on the Unix timestamp values MyUnixDate column, which are converted to year-month-day format.
Syntax and Arguments
derive type:single value: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.
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 Type | Example Value |
|---|---|---|
| Yes | String | 'MM/dd/yyyy' |
Tip: For additional examples, see Common Tasks.
Examples
Example - Unix timestamp formatting variations
| Description | unixTimestamp column | Transform | newUnixTimestamp column |
|---|---|---|---|
| Numeric date, year first | 1454946120000 1451433600000 1430032020000 | derive type:single value:UNIXTIMEFORMAT(unixTimestamp,'yyyy-MM-dd') as:'newUnixTimestamp' | 2016-02-08 2015-12-30 2015-04-26 |
| Numeric date, American style | 1454946120000 1451433600000 1430032020000 | derive type:single value:UNIXTIMEFORMAT(unixTimestamp,'M/d/yy') as:'newUnixTimestamp' | 2/8/16 12/30/15 4/26/15 |
| Full written date | 1454946120000 1451433600000 1430032020000 | derive type:single value:UNIXTIMEFORMAT(unixTimestamp,'MMMM dd, yyyy') as:'newUnixTimestamp' | February 08, 2016 December 30, 2015 April 26, 2015 |
| Abbreviated date, including abbreviated day of week | 1454946120000 1451433600000 1430032020000 | derive type:single value:UNIXTIMEFORMAT(unixTimestamp,'EEE MMM dd, yyyy') as:'newUnixTimestamp' | Mon Feb 08, 2016 Wed Dec 30, 2015 Sun Apr 26, 2015 |
| Full 24-hour time | 1454946120000 1451433600000 1430032020000 | derive type:single value:UNIXTIMEFORMAT(unixTimestamp,'HH:mm:ss.SSS') as:'newUnixTimestamp' | 15:42:00.000 00:00:00.000 07:07:00.00 |
| Twelve-hour time with AM/PM indicator | 1454946120000 1451433600000 1430032020000 | derive type:single value:UNIXTIMEFORMAT(unixTimestamp,'h:mm:ss a') as:'newUnixTimestamp' NOTE: For this function, use of the lower-case hour indicator ( | 3:42:00 PM 12:00:00 AM 7:07:00 AM |
This page has no comments.