TIME Function
Generates time values from three inputs of Integer type: hour, minute, and second.
Source values can be Integer literals or column references to values that can be inferred as Integers.
If any of the source values are invalid or out of range, a missing value is returned.
This function must be nested within another function that accepts date values as arguments. See the example below.
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
Integer literal values:
dateformat(time(23,58,59),'HH mm ss')
Output: Returns the following map:
23 58 59
Column reference values:
dateformat(time(myHour, myMinute, mySecond), 'hh-mm-ss')
Output: Generates a column of values where:
hh= values frommyHourcolumnmm= values frommyMinutecolumnss= values frommySecondcolumn
Syntax and Arguments
dateformat(time(hour_integer_col,minute_integer_col,second_integer_col ),'time_format_string')
Argument | Required? | Data Type | Description |
|---|---|---|---|
hour_integer_col | Y | integer | Name of column or Integer literal representing the hour value to apply to the function |
minute_integer_col | Y | integer | Name of column or Integer literal representing the minute value to apply to the function |
second_integer_col | Y | integer | Name of column or Integer literal representing the second value to apply to the function |
time_format_string | Y | string | String literal identifying the time format to apply to the value |
For more information on syntax standards, see Language Documentation Syntax Notes.
hour_integer_col
Integer literal or name of the column containing integer values for the hour. Values must integers between 0 and 23, inclusive.
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 | Integer (literal or column reference) | 15 |
minute_integer_col
Integer literal or name of the column containing integer values for the minutes. Values must integers between 0 and 59, inclusive.
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 | Integer (literal or column reference) | 23 |
second_integer_col
Integer literal or name of the column containing integer values for the second. Values must integers between 0 and 59, inclusive.
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 | Integer (literal or column reference) | 45 |
time_format_string
For more information, seeDATEFORMAT Function.
Examples
Astuce
For additional examples, see Common Tasks.
Example - date and time functions
This example provides an overview on various date and time functions.
Functions:
Item | Description |
|---|---|
DATE Function | Generates a date value from three inputs of Integer type: year, month, and day. |
TIME Function | Generates time values from three inputs of Integer type: hour, minute, and second. |
DATETIME Function | Generates a Datetime value from the following inputs of Integer type: year, month, day, hour, minute, and second. |
DATEFORMAT Function | Formats a specified Datetime set of values according to the specified date format. Source values can be a reference to a column containing Datetime values. |
Source:
year | month | day | hour | minute | second |
|---|---|---|---|---|---|
2016 | 10 | 11 | 2 | 3 | 0 |
2015 | 11 | 20 | 15 | 22 | 30 |
2014 | 12 | 25 | 18 | 30 | 45 |
Transformation:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | DATEFORMAT(DATE (year, month, day),'yyyy-MM-dd') |
Parameter: New column name | 'fctn_date' |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | DATEFORMAT(TIME (hour, minute, second),'HH-mm-ss') |
Parameter: New column name | 'fctn_time' |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | DATEFORMAT(DATETIME (year, month, day, hour, minute, second),'yyyy-MM-dd-HH:mm:ss') |
Parameter: New column name | 'fctn_datetime' |
Results:
Note
All inputs must be inferred as Integer type and must be valid values for the specified input. For example, month values must be integers between 1 and 12, inclusive.
year | month | day | hour | minute | second | fctn_date | fctn_time | fctn_datetime |
|---|---|---|---|---|---|---|---|---|
2016 | 10 | 11 | 2 | 3 | 0 | 2016-10-11 | 02-03-00 | 2016-10-11-02:03:00 |
2015 | 11 | 20 | 15 | 22 | 30 | 2015-11-20 | 15-22-30 | 2015-11-20-15:22:30 |
2014 | 12 | 25 | 18 | 30 | 45 | 2014-12-25 | 18-30-45 | 2014-12-25-18:30:45 |
You can apply other date and time functions to the generated columns. For an example, see YEAR Function.