Spark SQL - Add Day, Month and Year to Date
Spark SQL provides quite a few functions we can use to add days, months or years to a date or timestamp. These functions include
date_add
, date_sub
, add_months
, etc. These functions can also be used to subtract days, months or years from a date.The following are the examples.
Add days
spark-sql> select date_add(DATE'2022-06-01', 1); 2022-06-02
Subtract days
spark-sql> select date_add(DATE'2022-06-01', -1); 2022-05-31 spark-sql> select date_sub(DATE'2022-06-01', 2); 2022-05-30
Add months
spark-sql> select add_months(DATE'2022-06-01',1); 2022-07-01
Substract months
spark-sql> select add_months(DATE'2022-06-01',-2); 2022-04-01
Add years
There is no direct function we can use but we can use add_months
. For example, adding one year is equivalent to add 12 months.
spark-sql> select add_months(DATE'2022-06-01',12) as add_one_year, add_months(DATE'2022-06-01',24) as add_two_years; 2023-06-01 2024-06-01
Subtract years
spark-sql> select add_months(DATE'2022-06-01',-12); 2021-06-01
info Last modified by Kontext 2 years ago
copyright
This page is subject to Site terms.
comment Comments
No comments yet.
Log in with external accounts
warning Please login first to view stats information.
article
Spark SQL - Convert String to Timestamp
article
Spark SQL - Left and Right Padding (lpad and rpad) Functions
article
Spark SQL - NTILE Window Function
article
Spark SQL - Convert Date/Timestamp to String via date_format Function
article
Spark SQL - Check if String Contains a String
Read more (46)