| <table class="table"> |
| <thead> |
| <tr> |
| <th style="width:25%">Function</th> |
| <th>Description</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>add_months(start_date, num_months)</td> |
| <td>Returns the date that is `num_months` after `start_date`.</td> |
| </tr> |
| <tr> |
| <td>convert_timezone([sourceTz, ]targetTz, sourceTs)</td> |
| <td>Converts the timestamp without time zone `sourceTs` from the `sourceTz` time zone to `targetTz`.</td> |
| </tr> |
| <tr> |
| <td>curdate()</td> |
| <td>Returns the current date at the start of query evaluation. All calls of curdate within the same query return the same value.</td> |
| </tr> |
| <tr> |
| <td>current_date()</td> |
| <td>Returns the current date at the start of query evaluation. All calls of current_date within the same query return the same value.</td> |
| </tr> |
| <tr> |
| <td> current_date</td> |
| <td>Returns the current date at the start of query evaluation.</td> |
| </tr> |
| <tr> |
| <td>current_time([precision])</td> |
| <td>Returns the current time at the start of query evaluation. |
| All calls of current_time within the same query return the same value.</td> |
| </tr> |
| <tr> |
| <td> current_time</td> |
| <td>Returns the current time at the start of query evaluation.</td> |
| </tr> |
| <tr> |
| <td>current_timestamp()</td> |
| <td>Returns the current timestamp at the start of query evaluation. All calls of current_timestamp within the same query return the same value.</td> |
| </tr> |
| <tr> |
| <td> current_timestamp</td> |
| <td>Returns the current timestamp at the start of query evaluation.</td> |
| </tr> |
| <tr> |
| <td>current_timezone()</td> |
| <td>Returns the current session local timezone.</td> |
| </tr> |
| <tr> |
| <td>date_add(start_date, num_days)</td> |
| <td>Returns the date that is `num_days` after `start_date`.</td> |
| </tr> |
| <tr> |
| <td>date_diff(endDate, startDate)</td> |
| <td>Returns the number of days from `startDate` to `endDate`.</td> |
| </tr> |
| <tr> |
| <td>date_format(timestamp, fmt)</td> |
| <td>Converts `timestamp` to a value of string in the format specified by the date format `fmt`.</td> |
| </tr> |
| <tr> |
| <td>date_from_unix_date(days)</td> |
| <td>Create date from the number of days since 1970-01-01.</td> |
| </tr> |
| <tr> |
| <td>date_part(field, source)</td> |
| <td>Extracts a part of the date/timestamp or interval source.</td> |
| </tr> |
| <tr> |
| <td>date_sub(start_date, num_days)</td> |
| <td>Returns the date that is `num_days` before `start_date`.</td> |
| </tr> |
| <tr> |
| <td>date_trunc(fmt, ts)</td> |
| <td>Returns timestamp `ts` truncated to the unit specified by the format model `fmt`.</td> |
| </tr> |
| <tr> |
| <td>dateadd(start_date, num_days)</td> |
| <td>Returns the date that is `num_days` after `start_date`.</td> |
| </tr> |
| <tr> |
| <td>datediff(endDate, startDate)</td> |
| <td>Returns the number of days from `startDate` to `endDate`.</td> |
| </tr> |
| <tr> |
| <td>datepart(field, source)</td> |
| <td>Extracts a part of the date/timestamp or interval source.</td> |
| </tr> |
| <tr> |
| <td>day(date)</td> |
| <td>Returns the day of month of the date/timestamp.</td> |
| </tr> |
| <tr> |
| <td>dayname(date)</td> |
| <td>Returns the three-letter abbreviated day name from the given date.</td> |
| </tr> |
| <tr> |
| <td>dayofmonth(date)</td> |
| <td>Returns the day of month of the date/timestamp.</td> |
| </tr> |
| <tr> |
| <td>dayofweek(date)</td> |
| <td>Returns the day of the week for date/timestamp (1 = Sunday, 2 = Monday, ..., 7 = Saturday).</td> |
| </tr> |
| <tr> |
| <td>dayofyear(date)</td> |
| <td>Returns the day of year of the date/timestamp.</td> |
| </tr> |
| <tr> |
| <td>extract(field FROM source)</td> |
| <td>Extracts a part of the date or timestamp or time or interval source.</td> |
| </tr> |
| <tr> |
| <td>from_unixtime(unix_time[, fmt])</td> |
| <td>Returns `unix_time` in the specified `fmt`.</td> |
| </tr> |
| <tr> |
| <td>from_utc_timestamp(timestamp, timezone)</td> |
| <td>Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.</td> |
| </tr> |
| <tr> |
| <td>hour(expr)</td> |
| <td>Returns the hour component of the given expression. |
| |
| If `expr` is a TIMESTAMP or a string that can be cast to timestamp, |
| it returns the hour of that timestamp. |
| If `expr` is a TIME type (since 4.1.0), it returns the hour of the time-of-day.</td> |
| </tr> |
| <tr> |
| <td>last_day(date)</td> |
| <td>Returns the last day of the month which the date belongs to.</td> |
| </tr> |
| <tr> |
| <td>localtimestamp()</td> |
| <td>Returns the current timestamp without time zone at the start of query evaluation. All calls of localtimestamp within the same query return the same value.</td> |
| </tr> |
| <tr> |
| <td> localtimestamp</td> |
| <td>Returns the current local date-time at the session time zone at the start of query evaluation.</td> |
| </tr> |
| <tr> |
| <td>make_date(year, month, day)</td> |
| <td>Create date from year, month and day fields. If the configuration `spark.sql.ansi.enabled` is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.</td> |
| </tr> |
| <tr> |
| <td>make_dt_interval([days[, hours[, mins[, secs]]]])</td> |
| <td>Make DayTimeIntervalType duration from days, hours, mins and secs.</td> |
| </tr> |
| <tr> |
| <td>make_interval([years[, months[, weeks[, days[, hours[, mins[, secs]]]]]]])</td> |
| <td>Make interval from years, months, weeks, days, hours, mins and secs.</td> |
| </tr> |
| <tr> |
| <td>make_time(hour, minute, second)</td> |
| <td>Create time from hour, minute and second fields. For invalid inputs it will throw an error.</td> |
| </tr> |
| <tr> |
| <td>make_timestamp(year, month, day, hour, min, sec[, timezone])</td> |
| <td>Create timestamp from year, month, day, hour, min, sec and timezone fields. The result data type is consistent with the value of configuration `spark.sql.timestampType`. If the configuration `spark.sql.ansi.enabled` is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.</td> |
| </tr> |
| <tr> |
| <td>make_timestamp_ltz(year, month, day, hour, min, sec[, timezone])</td> |
| <td>Create the current timestamp with local time zone from year, month, day, hour, min, sec and timezone fields. If the configuration `spark.sql.ansi.enabled` is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.</td> |
| </tr> |
| <tr> |
| <td>make_timestamp_ntz(year, month, day, hour, min, sec)</td> |
| <td>Create local date-time from year, month, day, hour, min, sec fields. If the configuration `spark.sql.ansi.enabled` is false, the function returns NULL on invalid inputs. Otherwise, it will throw an error instead.</td> |
| </tr> |
| <tr> |
| <td> make_timestamp_ntz(date, time)</td> |
| <td>Create a local date-time from date and time fields.</td> |
| </tr> |
| <tr> |
| <td>make_ym_interval([years[, months]])</td> |
| <td>Make year-month interval from years, months.</td> |
| </tr> |
| <tr> |
| <td>minute(expr)</td> |
| <td>Returns the minute component of the given expression. |
| |
| If `expr` is a TIMESTAMP or a string that can be cast to timestamp, |
| it returns the minute of that timestamp. |
| If `expr` is a TIME type (since 4.1.0), it returns the minute of the time-of-day.</td> |
| </tr> |
| <tr> |
| <td>month(date)</td> |
| <td>Returns the month component of the date/timestamp.</td> |
| </tr> |
| <tr> |
| <td>monthname(date)</td> |
| <td>Returns the three-letter abbreviated month name from the given date.</td> |
| </tr> |
| <tr> |
| <td>months_between(timestamp1, timestamp2[, roundOff])</td> |
| <td>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.</td> |
| </tr> |
| <tr> |
| <td>next_day(start_date, day_of_week)</td> |
| <td>Returns the first date which is later than `start_date` and named as indicated. |
| The function returns NULL if at least one of the input parameters is NULL. |
| When both of the input parameters are not NULL and day_of_week is an invalid input, |
| the function throws SparkIllegalArgumentException if `spark.sql.ansi.enabled` is set to true, otherwise NULL.</td> |
| </tr> |
| <tr> |
| <td>now()</td> |
| <td>Returns the current timestamp at the start of query evaluation.</td> |
| </tr> |
| <tr> |
| <td>quarter(date)</td> |
| <td>Returns the quarter of the year for date, in the range 1 to 4.</td> |
| </tr> |
| <tr> |
| <td>second(expr)</td> |
| <td>Returns the second component of the given expression. |
| |
| If `expr` is a TIMESTAMP or a string that can be cast to timestamp, |
| it returns the second of that timestamp. |
| If `expr` is a TIME type (since 4.1.0), it returns the second of the time-of-day.</td> |
| </tr> |
| <tr> |
| <td>session_window(time_column, gap_duration)</td> |
| <td>Generates session window given a timestamp specifying column and gap duration. |
| See <a href="https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html#types-of-time-windows">'Types of time windows'</a> in Structured Streaming guide doc for detailed explanation and examples.</td> |
| </tr> |
| <tr> |
| <td>timestamp_micros(microseconds)</td> |
| <td>Creates timestamp from the number of microseconds since UTC epoch.</td> |
| </tr> |
| <tr> |
| <td>timestamp_millis(milliseconds)</td> |
| <td>Creates timestamp from the number of milliseconds since UTC epoch.</td> |
| </tr> |
| <tr> |
| <td>timestamp_seconds(seconds)</td> |
| <td>Creates timestamp from the number of seconds (can be fractional) since UTC epoch.</td> |
| </tr> |
| <tr> |
| <td>to_date(date_str[, fmt])</td> |
| <td>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.</td> |
| </tr> |
| <tr> |
| <td>to_time(str[, format])</td> |
| <td>Parses the `str` expression with the `format` expression to a time. |
| If `format` is malformed or its application does not result in a well formed time, the function |
| raises an error. By default, it follows casting rules to a time if the `format` is omitted.</td> |
| </tr> |
| <tr> |
| <td>to_timestamp(timestamp_str[, fmt])</td> |
| <td>Parses the `timestamp_str` 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. The result data type is consistent with the value of |
| configuration `spark.sql.timestampType`.</td> |
| </tr> |
| <tr> |
| <td>to_timestamp_ltz(timestamp_str[, fmt])</td> |
| <td>Parses the `timestamp_str` expression with the `fmt` expression |
| to a timestamp with local time zone. Returns null with invalid input. By default, it follows casting rules to |
| a timestamp if the `fmt` is omitted.</td> |
| </tr> |
| <tr> |
| <td>to_timestamp_ntz(timestamp_str[, fmt])</td> |
| <td>Parses the `timestamp_str` expression with the `fmt` expression |
| to a timestamp without time zone. Returns null with invalid input. By default, it follows casting rules to |
| a timestamp if the `fmt` is omitted.</td> |
| </tr> |
| <tr> |
| <td>to_unix_timestamp(timeExp[, fmt])</td> |
| <td>Returns the UNIX timestamp of the given time.</td> |
| </tr> |
| <tr> |
| <td>to_utc_timestamp(timestamp, timezone)</td> |
| <td>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. For example, 'GMT+1' would yield '2017-07-14 01:40:00.0'.</td> |
| </tr> |
| <tr> |
| <td>trunc(date, fmt)</td> |
| <td>Returns `date` with the time portion of the day truncated to the unit specified by the format model `fmt`.</td> |
| </tr> |
| <tr> |
| <td>try_make_interval([years[, months[, weeks[, days[, hours[, mins[, secs]]]]]]])</td> |
| <td>This is a special version of `make_interval` that performs the same operation, but returns NULL when an overflow occurs.</td> |
| </tr> |
| <tr> |
| <td>try_make_timestamp(year, month, day, hour, min, sec[, timezone])</td> |
| <td>Try to create a timestamp from year, month, day, hour, min, sec and timezone fields. The result data type is consistent with the value of configuration `spark.sql.timestampType`. The function returns NULL on invalid inputs.</td> |
| </tr> |
| <tr> |
| <td>try_make_timestamp_ltz(year, month, day, hour, min, sec[, timezone])</td> |
| <td>Try to create the current timestamp with local time zone from year, month, day, hour, min, sec and timezone fields. The function returns NULL on invalid inputs.</td> |
| </tr> |
| <tr> |
| <td>try_make_timestamp_ntz(year, month, day, hour, min, sec)</td> |
| <td>Try to create local date-time from year, month, day, hour, min, sec fields. The function returns NULL on invalid inputs.</td> |
| </tr> |
| <tr> |
| <td> try_make_timestamp_ntz(date, time)</td> |
| <td>Create a local date-time from date and time fields.</td> |
| </tr> |
| <tr> |
| <td>try_to_time(str[, format])</td> |
| <td>Parses the `str` expression with the `format` expression to a time. |
| If `format` is malformed or its application does not result in a well formed time, the function |
| returns NULL. By default, it follows casting rules to a time if the `format` is omitted.</td> |
| </tr> |
| <tr> |
| <td>try_to_timestamp(timestamp_str[, fmt])</td> |
| <td>Parses the `timestamp_str` expression with the `fmt` expression |
| to a timestamp. The function always returns null on an invalid input with/without ANSI SQL |
| mode enabled. By default, it follows casting rules to a timestamp if the `fmt` is omitted. |
| The result data type is consistent with the value of configuration `spark.sql.timestampType`.</td> |
| </tr> |
| <tr> |
| <td>unix_date(date)</td> |
| <td>Returns the number of days since 1970-01-01.</td> |
| </tr> |
| <tr> |
| <td>unix_micros(timestamp)</td> |
| <td>Returns the number of microseconds since 1970-01-01 00:00:00 UTC.</td> |
| </tr> |
| <tr> |
| <td>unix_millis(timestamp)</td> |
| <td>Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.</td> |
| </tr> |
| <tr> |
| <td>unix_seconds(timestamp)</td> |
| <td>Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.</td> |
| </tr> |
| <tr> |
| <td>unix_timestamp([timeExp[, fmt]])</td> |
| <td>Returns the UNIX timestamp of current or specified time.</td> |
| </tr> |
| <tr> |
| <td>weekday(date)</td> |
| <td>Returns the day of the week for date/timestamp (0 = Monday, 1 = Tuesday, ..., 6 = Sunday).</td> |
| </tr> |
| <tr> |
| <td>weekofyear(date)</td> |
| <td>Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.</td> |
| </tr> |
| <tr> |
| <td>window(time_column, window_duration[, slide_duration[, start_time]])</td> |
| <td>Bucketize rows into one or more time windows given a timestamp specifying column. |
| Window starts are inclusive but the window ends are exclusive, e.g. 12:05 will be in the window [12:05,12:10) but not in [12:00,12:05). |
| Windows can support microsecond precision. Windows in the order of months are not supported. |
| See <a href="https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html#window-operations-on-event-time">'Window Operations on Event Time'</a> in Structured Streaming guide doc for detailed explanation and examples.</td> |
| </tr> |
| <tr> |
| <td>window_time(window_column)</td> |
| <td>Extract the time value from time/session window column which can be used for event time value of window. |
| The extracted time is (window.end - 1) which reflects the fact that the aggregating |
| windows have exclusive upper bound - [start, end) |
| See <a href="https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html#window-operations-on-event-time">'Window Operations on Event Time'</a> in Structured Streaming guide doc for detailed explanation and examples.</td> |
| </tr> |
| <tr> |
| <td>year(date)</td> |
| <td>Returns the year component of the date/timestamp.</td> |
| </tr> |
| </tbody> |
| </table> |
