# 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" } } } ```