Spark SQL - Date and Timestamp Function

Current date

Function current_date() or current_date can be used to return the current date at the start of query evaluation. 

Example:

spark-sql> select current_date();
current_date()
2021-01-09

spark-sql> select current_date;
current_date()
2021-01-09

*Brackets are optional for this function.

Current datetime

Function current_timestamp() or current_timestamp or now() can be used to return the current timestamp at the start of query evaluation.

Example:

spark-sql> select current_timestamp();
current_timestamp()
2021-01-09 17:03:51.586

spark-sql> select current_timestamp;
current_timestamp()
2021-01-09 17:04:09.158

spark-sql> select now();
now()
2021-01-09 17:10:29.592

Add or subtract months

Function add_months(start_date, num_months) can be used to add or substract months from a date.

Example:

spark-sql> select add_months(DATE'2020-01-01',5) AS 5M_LATER, add_months(DATE'2020-01-01', -5) AS 5M_BEFORE;
5M_LATER        5M_BEFORE
2020-06-01      2019-08-01

As shown in the code snippet, to subtract months, just pass the second parameter as a negative integer. 

Add or subtract days

Use function date_add(start_date, num_days) and date_sub(start_date, num_days) to add and subtract days from a date.

Example:

spark-sql> select date_add(current_date, 5) 5D_LATER, date_sub(current_date, 5) 5D_BEFORE;
5D_LATER        5D_BEFORE
2021-01-14      2021-01-04

Date difference between two date

Function date_diff(end_date, start_date) returns the days difference between start and end date. 

Example:

spark-sql> select datediff(current_date, DATE'2021-01-01');
datediff(current_date(), DATE '2021-01-01')
8

Last day of month

Function last_day(date) returns the date of the last day in the same month as the input date.

Example:

spark-sql> select last_day(current_date);
last_day(current_date())
2021-01-31

First day of month

There is no function to directly return the first day of month but we can use multiple ways to derive it. For example, use function day(date) to return the day component from the date and then subtract it from the date to get the date of month start. 

Example:

spark-sql> select current_date - day(current_date) + 1;
date_add(date_sub(current_date(), day(current_date())), 1)
2021-01-01

spark-sql> select add_months(last_day(current_date)+1, -1);
add_months(date_add(last_day(current_date()), 1), -1)
2021-01-01

Day of year, month, week

Function day, dayofyear, dayofweek can be used to return the day number of the year, month or week.

Example:

spark-sql> select dayofyear(current_date), day(current_date), dayofweek(current_date);
dayofyear(current_date())       day(current_date())     dayofweek(current_date())
9       9       7

Timestamp to date

Use cast function to convert timestamp to date.

Example:

spark-sql> select cast(current_timestamp as date);
CAST(current_timestamp() AS DATE)
2021-01-09

Date to timestamp

Similarly, use cast function to convert timestamp to date.

Example:

spark-sql> select cast(current_date as timestamp);
CAST(current_date() AS TIMESTAMP)
2021-01-09 00:00:00

There are many other date or timestamp functions. Refer to Spark SQL official documentation for details.