# Date and time functions
Following, find reference topics for date and time functions that work with recipe actions.
**Topics**
+ [CONVERT\$1TIMEZONE](recipe-actions.functions.CONVERT_TIMEZONE.md)
+ [DATE](recipe-actions.functions.DATE.md)
+ [DATE\$1ADD](recipe-actions.functions.DATEADD.md)
+ [DATE\$1DIFF](recipe-actions.functions.DATEDIFF.md)
+ [DATE\$1FORMAT](recipe-actions.functions.DATE_FORMAT.md)
+ [DATE\$1TIME](recipe-actions.functions.DATETIME.md)
+ [DAY](recipe-actions.functions.DAY.md)
+ [HOUR](recipe-actions.functions.HOUR.md)
+ [MILLISECOND](recipe-actions.functions.MILLISECOND.md)
+ [MINUTE](recipe-actions.functions.MINUTE.md)
+ [MONTH](recipe-actions.functions.MONTH.md)
+ [MONTH\$1NAME](recipe-actions.functions.MONTH_NAME.md)
+ [NOW](recipe-actions.functions.NOW.md)
+ [QUARTER](recipe-actions.functions.QUARTER.md)
+ [SECOND](recipe-actions.functions.SECOND.md)
+ [TIME](recipe-actions.functions.TIME.md)
+ [TODAY](recipe-actions.functions.TODAY.md)
+ [UNIX\$1TIME](recipe-actions.functions.UNIX_TIME.md)
+ [UNIX\$1TIME\$1FORMAT](recipe-actions.functions.UNIX_TIME_FORMAT.md)
+ [WEEK\$1DAY](recipe-actions.functions.WEEK_DAY.md)
+ [WEEK\$1NUMBER](recipe-actions.functions.WEEK_NUMBER.md)
+ [YEAR](recipe-actions.functions.YEAR.md)
# CONVERT\$1TIMEZONE
Converts a time value from the source column into a new column based on a specified timezone.
**Parameters**
+ `sourceColumn` – The name of an existing column. The source column can be of type `string`, `date`, or `timestamp`.
+ `fromTimeZone` – Source value timezone. If nothing is specified, the default timezone is UTC.
+ `toTimeZone` – Timezone to be converted to. If nothing is specified, the default timezone is UTC.
+ `targetColumn` – A name for the newly-created column.
+ `dateTimeFormat` – Optional. A format string for the date. If the format isn't specified, the default format is used: `yyyy-mm-dd HH:MM:SS`.
**Example**
```
{
"RecipeAction": {
"Operation": "CONVERT_TIMEZONE",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"fromTimeZone": "UTC+08:00",
"toTimeZone": "UTC+08:00",
"targetColumn": "DATETIME Column CONVERT_TIMEZONE",
"dateTimeFormat": "yyyy-mm-dd HH:MM:SS"
}
}
}
```
# DATE
Creates a new column containing the date value, from the source columns or from values provided.
**Parameters**
+ `dateTimeFormat` – Optional. A format string for the date, as it is to appear in the new column. If this string isn't specified, the default format is `yyyy-mm-dd HH:MM:SS`.
+ `dateTimeParameters` – A JSON-encoded string representing the components of the date and time:
+ `year`
+ `value`
+ `month`
+ `day`
+ `hour`
+ `second`
Each component must specify one of the following:
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Example**
```
{
"RecipeAction": {
"Operation": "DATE",
"Parameters": {
"dateTimeFormat": "mm/dd/yy",
"dateTimeParameters": "{\"year\":{\"value\":\"2019\"},\"month\":{\"value\":\"12\"},\"day\":{\"value\":\"31\"},\"hour\":{},\"minute\":{},\"second\":{}}",
"targetColumn": "DATE Column 1"
}
}
}
```
# DATE\$1ADD
Adds a year, month, or day to the date from a source column or value, and creates a new column containing the results.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `units` – A unit of measure for adjusting the date. Valid values are `MONTHS`, `YEARS`, `MILLISECONDS`, `QUARTERS`, `HOURS`, `MICROSECONDS`, `WEEKS`, `SECONDS`, `DAYS`, and `MINUTES`.
+ `dateAddValue` – The number of `units` to be added to the date.
+ `dateTimeFormat` – Optional. A format string for the date, as it is to appear in the new column. If not specified, the default format is `yyyy-mm-dd HH:MM:SS`.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "DATE_ADD",
"Parameters": {
"sourceColumn": "DATE Column 1",
"units": "DAYS",
"dateAddValue": "14",
"dateTimeFormat": "mm/dd/yyyy",
"targetColumn": "DATE Column 1_DATEADD"
}
}
}
```
# DATE\$1DIFF
Creates a new column containing the difference between two dates.
**Parameters**
+ `sourceColumn1` – The name of an existing column.
+ `sourceColumn2` – The name of an existing column.
+ `value1` – A character string to evaluate.
+ `value2` – A character string to evaluate.
+ `units` – A unit of measure for describe the difference between the dates. Valid values are `MONTHS`, `YEARS`, `MILLISECONDS`, `QUARTERS`, `HOURS`, `MICROSECONDS`, `WEEKS`, `SECONDS`, `DAYS`, and `MINUTES`.
+ `targetColumn` – A name for the newly created column.
**Note**
You can only specify one of the following combinations:
Both of `sourceColumn1` and `sourceColumn2`.
One of `sourceColumn1` or `sourceColumn2` and one of `value1` or `value2`.
Both of `value1` and `value2`.
**Example**
```
{
"RecipeAction": {
"Operation": "DATE_DIFF",
"Parameters": {
"value1": "2020-01-01",
"value2": "2020-10-06",
"units": "DAYS",
"targetColumn": "DATEDIFF Column 1"
}
}
}
```
# DATE\$1FORMAT
Creates a new column containing a date, in a specific format, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A string to evaluate.
+ `dateTimeFormat` – Optional. A format string for the date, as it is to appear in the new column. If not specified, the default format is `yyyy-mm-dd HH:MM:SS`.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Examples**
```
{
"RecipeAction": {
"Operation": "DATE_FORMAT",
"Parameters": {
"sourceColumn": "DATE Column 1",
"dateTimeFormat": "month*dd*yyyy",
"targetColumn": "DATE Column 1_DATEFORMAT"
}
}
}
```
```
{
"RecipeAction": {
"Operation": "DATE_FORMAT",
"Parameters": {
"value": "22:10:47",
"dateTimeFormat": "HH:MM:SS",
"targetColumn": "formatted_date_value"
}
}
}
```
# DATE\$1TIME
Creates a new column containing the date and time value, from the source columns or from values provided.
**Parameters**
+ `dateTimeFormat` – Optional. A format string for the date, as it is to appear in the new column. If this string isn't specified, the default format is `yyyy-mm-dd HH:MM:SS`.
+ `dateTimeParameters` – A JSON-encoded string representing the components of the date and time:
+ `year`
+ `value`
+ `month`
+ `day`
+ `hour`
+ `second`
Each component must specify one of the following:
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
**Example**
```
{
"RecipeAction": {
"Operation": "DATE_TIME",
"Parameters": {
"dateTimeFormat": "yyyy-mm-dd HH:MM:SS",
"dateTimeParameters": "{\"year\":{\"value\":\"2010\"},\"month\":{\"value\":\"5\"},\"day\":{\"value\":\"21\"},\"hour\":{\"value\":\"13\"},\"minute\":{\"value\":\"34\"},\"second\":{\"value\":\"25\"}}",
"targetColumn": "DATETIME Column 1"
}
}
}
```
# DAY
Creates a new column containing the day of the month, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "DAY",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_DAY"
}
}
}
```
# HOUR
Creates a new column containing the hour value, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "HOUR",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_HOUR"
}
}
}
```
# MILLISECOND
Creates a new column containing the millisecond value from a source column or input value.
**Parameters**
+ `sourceColumn` – The name of an existing column. The source column can be of type `string`, `date`, or `timestamp`.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly-created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "MILLISECOND",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_MILLISECOND"
}
}
}
```
# MINUTE
Creates a new column containing the minute value, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "MINUTE",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_MINUTE"
}
}
}
```
# MONTH
Creates a new column containing the number of the month, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "MONTH",
"Parameters": {
"value": "2018-05-27",
"targetColumn": "MONTH Column 1"
}
}
}
```
# MONTH\$1NAME
Creates a new column containing the name of the month, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "MONTH_NAME",
"Parameters": {
"value": "2018-05-27",
"targetColumn": "MONTHNAME Column 1"
}
}
}
```
# NOW
Creates a new column containing the current date and time in the format `yyyy-mm-dd HH:MM:SS`.
**Parameters**
+ `timeZone` – The name of a time zone. If no time zone is specified, then the default is Universal Coordinated Time (UTC).
+ `targetColumn` – A name for the newly created column.
**Example**
```
{
"RecipeAction": {
"Operation": "NOW",
"Parameters": {
"timeZone": "US/Pacific",
"targetColumn": "NOW Column 1"
}
}
```
# QUARTER
Creates a new column containing the date-based quarter from a string that represents a date.
**Note**
Quarters are designated in the new column as 1, 2, 3, or 4.
1 is January, February, and March.
2 is April, May, and June.
3 is July, August, and September.
4 is October, November, and December.
**Parameters**
+ `sourceColumn` – The name of an existing column. The source column can be of type `string`, `date`, or `timestamp`.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly-created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "QUARTER",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_QUARTER"
}
}
}
```
# SECOND
Creates a new column containing the second value, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "SECOND",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_SECOND"
}
}
}
```
# TIME
Creates a new column containing the time value, from the source columns or values provided.
**Parameters**
+ `dateTimeFormat` – Optional. A format string for the date, as it is to appear in the new column. If this string isn't specified, the default format is `yyyy-mm-dd HH:MM:SS`.
+ `dateTimeParameters` – A JSON-encoded string representing the components of the date and time:
+ `year`
+ `value`
+ `month`
+ `day`
+ `hour`
+ `second`
Each component must specify one of the following:
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Example**
```
{
"RecipeAction": {
"Operation": "TIME",
"Parameters": {
"dateTimeFormat": "HH:MM:SS",
"dateTimeParameters": "{\"year\":{},\"month\":{},\"day\":{},\"hour\":{\"sourceColumn\":\"rand_hour\"},\"minute\":{\"sourceColumn\":\"rand_minute\"},\"second\":{\"sourceColumn\":\"rand_second\"}}",
"targetColumn": "TIME Column 1"
}
}
}
```
# TODAY
Creates a new column containing the current date in the format `yyyy-mm-dd`.
**Parameters**
+ `timeZone` – The name of a time zone. If no time zone is specified, then the default is Universal Coordinated Time (UTC).
+ `targetColumn` – A name for the newly created column.
**Example**
```
{
"RecipeAction": {
"Operation": "TODAY",
"Parameters": {
"timeZone": "US/Pacific",
"targetColumn": "TODAY Column 1"
}
}
}
```
# UNIX\$1TIME
Creates a new column containing a number representing epoch time (Unix time)—the number of seconds since January 1, 1970—based on a source column or input value. If time zone can be inferred, the output is in that time zone. Otherwise, the output is in Universal Coordinated Time (UTC).
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "UNIX_TIME",
"Parameters": {
"sourceColumn": "TIME Column 1",
"targetColumn": "TIME Column 1_UNIXTIME"
}
}
}
```
# UNIX\$1TIME\$1FORMAT
Converts Unix time for a source column or input value to a specified numerical date format, and returns the result in a new column.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – An integer that represents a Unix epoch timestamp.
+ `dateTimeFormat` – Optional. A format string for the date, as it is to appear in the new column. If not specified, the default format is `yyyy-mm-dd HH:MM:SS`.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "UNIX_TIME_FORMAT",
"Parameters": {
"value": "1601936554",
"dateTimeFormat": "yyyy-mm-dd HH:MM:SS",
"targetColumn": "UNIXTIMEFORMAT Column 1"
}
}
}
```
# WEEK\$1DAY
Creates a new column containing the day of the week, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "WEEK_DAY",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_WEEKDAY"
}
}
}
```
# WEEK\$1NUMBER
Creates a new column containing the number of the week (from 1 to 52), from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "WEEK_NUMBER",
"Parameters": {
"sourceColumn": "DATETIME Column 1",
"targetColumn": "DATETIME Column 1_WEEK_NUMBER"
}
}
}
```
# YEAR
Creates a new column containing the year, from a string that represents a date.
**Parameters**
+ `sourceColumn` – The name of an existing column.
+ `value` – A character string to evaluate.
+ `targetColumn` – A name for the newly created column.
**Note**
You can specify either `sourceColumn` or `value`, but not both.
**Example**
```
{
"RecipeAction": {
"Operation": "YEAR",
"Parameters": {
"value": "2019-06-12",
"targetColumn": "YEAR Column 1"
}
}
}
```