Date Functions

Enables to perform calculations involving dates.

add_months #

Calculates the new date by adding the given number of months.

Input Parameters: Column arg0, int arg1.

arg0 (required) – The string/date/timestamp column to which months are to be added.

arg1 (required)– Number of months to be added.

Output Type Date Column Configuration Parameters No Returns Returns the date that is arg1 after arg0. Throws Application Exception

Use Case:

add_months(Column arg0, int arg1)

Example:

add_months(“2009-03-01”,2) will return “2009-05-01”

current_date #

Computes the current date as a date column.

Input Parameters: There are no function arguments.

Output Type Date Column Configuration Parameters No Returns Returns the current date as date column. Throws Application Exception

Use Case:

current_date()

Example:

current_date() will return the current date.

If used on 2018-06-15 will return 2018-06-15

current_timestamp #

Computes the current timestamp as a timestamp column.

Input Parameters: There are no function arguments.

Output Type Timestamp Column. Configuration Parameters No. Returns Returns the current timestamp as a timestamp column. Throws Application Exception

Use Case:

current_timestamp()

Example:

current timestamp() will return the current timestamp.

date_add #

Adds the specified number of months in ‘arg1’ to a date time value in ‘arg0’.

Input Parameters: Column arg0, int arg1

arg0 (required) – The string/date/timestamp column to which days to be added.

arg1 (required)– Number of days to be added.

Output Type Date Column. Configuration Parameters No. Returns Returns the date that is arg0 after arg1. Throws Application Exception

Use Case:

date_add(Column arg0, int arg1)

Example:

date_add(“2009-03-01”,2) will return “2009-03-03”.

get_json_object #

Extracts a json object from path.

Input Parameters: Column arg0, int arg1

arg0:The json txt column

arg1: The path

Output Type Date Column Configuration Parameters No Returns Returns the extracted json object. Throws Application Exception

Use Case:

get_json_object(Column arg0, Object arg1)

Example:

get_json_object(‘{“a”:“b”}’, ‘$.a’) will return b

months_between #

If timestamp1 is later than timestamp2, then the result is positive. If timestamp1 and timestamp2 are on the same day of month, or both are the last day of month, time of day will be ignored. Otherwise, the difference is calculated based on 31 days per month, and rounded to 8 digits unless roundOff=false.

Input Parameters: Column arg0, int arg1

arg0:timestamp1 column.

arg1:timestamp2 column.

arg2:roundoff boolean

Output Type Date Column Configuration Parameters No Returns Returns the months difference Throws Application Exception

Use Case:

months_between(Column arg0, Column arg1, Object arg2)

Example:

months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’, false) will return 3.9495967741935485

to_utc_timestamp #

Given a timestamp like ‘2017-07-14 02:40:00.0’, interprets it as a time in the given time zone, and renders that time as a timestamp in UTC.

Input Parameters:

arg0:The timestamp column.

arg1:The timezone column.

Output Type Date Column Configuration Parameters No Returns Returns the timestamp. Throws Application Exception

Use Case:

to_utc_timestamp(Column arg0, Column arg1)

Example:

to_utc_timestamp(‘2016-08-31’, ‘Asia/Seoul’) will return 2016-08-30 15:00:00

date_trunc #

Returns timestamp ts truncated to the unit specified by the format model fmt. fmt should be one of [“YEAR” “YYYY” “YY” “MON” “MONTH” “MM” “DAY” “DD” “HOUR” “MINUTE” “SECOND” “WEEK” “QUARTER”]

Input Parameters:

arg0: The FMT format.

arg1: The ts timestamp

Configuration Parameters NO Returns Returns timestamp ts truncated to the unit specified by the format model fmt. Throws ApplicationException

Use Case:

date_trunc(Object arg0, Column arg1)

Example:

date_trunc(‘YEAR’, ‘2015-03-05T09:32:05.359’) will return 2015-01-01 00:00:00

dayofweek #

Returns the day of the week for date/timestamp (1 = Sunday).

Input Parameters:

arg0: The date column.

Configuration Parameters NO Returns Returns the day of the week for date/timestamp Throws ApplicationException

Use Case:

dayofweek(Column arg0)

Example:

Dayofweek(‘2009-07-30’) will return 5

to_date #

Parses the date_str expression with the fmt expression to a date. Returns null with invalid input. By default, it follows casting rules to a date if the fmt is omitted.

Input Parameters:

arg0: str date_str column column.

arg1:the format string.

Configuration Parameters NO Returns Returns the formatted date. Throws ApplicationException

Use Case:

to_date(Column arg0, Object arg1)

Example:

to_date(‘2016-12-31’, ‘yyyy-MM-dd’) will return 2016-12-31

to_timestamp #

Parses the timestamp expression with the fmt expression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if the fmt is omitted.

Input Parameters:

arg0:The timestamp column.

arg1:The format string.

Configuration Parameters NO Returns Returns the formated timestamp. Throws ApplicationException

Use Case:

to_timestamp(Column arg0, Object arg1)

Example:

to_timestamp(‘2016-12-31’, ‘yyyy-MM-dd’) will return 2016-12-31 00:00:00

date_format #

Converts a date/timestamp/string column ‘arg0’ to a value of string in the format specified by the date format given by the ‘arg1’.

Input Parameters: Column arg0, String arg1

arg0 (required) – The date/timestamp/string column to be converted.

arg1 (required)– The format to which the date/timestamp/string to be converted.

Output Type String Column Configuration Parameters No Returns The formatted date as string. Throws Application Exception

Use Case:

date_format(Column arg0, String arg1)

Example:

date_format(“2009-03-01”,“MM-dd-yyyy”) will return “03-01-2009”.

date_sub #

Computes the date that is ‘arg1’ days before ‘arg0’.

Input Parameters: Column arg0, int arg1

arg0 (required) – The string/date/timestamp column from which days to be subtracted.

arg1 (required)– Number of days to be subtracted.

Output Type Date Column Configuration Parameters No Returns The formatted date as string. Throws Application Exception

Use Case:

date_sub(Column arg0, int arg1)

Example:

date_format(“2009-03-01”,“MM-dd-yyyy”) will return “03-01-2009”.

datediff #

Calculates the difference between the dates ‘arg1’ and ‘arg0’ date and returns the number of days.

Input Parameters: Column arg0, Column arg1

arg0 (required) – The end string/date/timestamp column.

arg1 (required)– The start string/date/timestamp column.

Output Type Integer Column Configuration Parameters No Returns The number of days from ‘arg1’ date to ‘arg0’ date. Throws Application Exception

Use Case:

datediff(Column arg0, Column arg1)

Example:

datediff(“2009-03-01”,“2009-02-27”) will return 2.

dayofyear #

Extracts the day of the year as an integer from a given date/timestamp/string.

Input Parameters: Column arg0

arg0 (required) – The date/timestamp/string from which the day of year to be extracted.

Output Type Integer Column Configuration Parameters No Returns The extracted day as an integer. Throws Application Exception

Use Case:

dayofyear(Column arg0)

Example:

dayofyear(“2017-12-15”) will return 349

unix_timestamp #

Convert time string with given pattern to Unix time stamp (in seconds), returns null if fails.

https://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html

Input Parameters: Column arg0,Object arg1

arg0 (required) – The string/date/timestamp column to be converted.

arg1 (required)– The string/date/timestamp format.

Output Type Long Column Configuration Parameters No Returns The converted Unix time stamp Throws Application Exception

Use Case:

unix_timestamp(Column arg0, Object arg1)

Example:

unix_timestamp(“2017-12-15 11:56”,“yyyy-MM-dd hh:mm”) will return 1513339008.

from_utc_timestamp #

Given a timestamp, which corresponds to a certain time of day in UTC, returns another timestamp that corresponds to the same time of day in the given timezone.

Input Parameters: Column arg0, Object arg1

arg0 (required) – The string/date/timestamp column in UTC.

arg1 (required) – The timezone to which the timestamp has to converted.

Output Type Timestamp Column Configuration Parameters No Returns The timestamp in the given timezone. Throws Application Exception

Use Case:

from_utc_timestamp(Column arg0, Column arg1)

Example:

from_utc_timestamp(“2017-12-15 11:40”,IST) will return “2017-12-15 17:10”

hour #

Extracts the hours as an integer from a given date/timestamp/string.

Input Parameters: Column arg0

arg0 (required) – The date/timestamp/string column from which the hours to be extracted.

Output Type Integer Column Configuration Parameters No Returns Returns the extracted hours as an integer. Throws Application Exception

Use Case:

hour(Column arg0)

Example:

date_format(“2009-03-01”,“MM-dd-yyyy”) will return “03-01-2009”.

last_day #

The date/timestamp/string column, returns the last day of the month which the given date belongs to.

Input Parameters: Column arg0

arg0 (required) – The date/timestamp/string column from which last day of month to be extracted.

Output Type Date Column Configuration Parameters No Returns Returns the computed last day. Throws Application Exception

Use Case:

last_day(Column arg0)

Example:

last_day(“2017-12-15”) will return “2017-12-31”.