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.

Use Case:

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.

Use Case:

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.

Use Case:

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.

Use Case:

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

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

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.** 

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

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.

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.

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.

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.

Use Case:

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.

Use Case:

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.

Use Case:

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.

Use Case:

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.

Customizing Formats

Input Parameters: Column arg0,Object arg1

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

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

Use Case:

Example:

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

default_unix_timestamp #

Gets current Unix timestamp in seconds.

Input Parameters: There are no function arguments.

Use Case:

Example:

default_unix_timestamp() will return current time in long format.

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.

Use Case:

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..

Use Case:

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…

Use Case:

Example:

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