is there any pyspark function for add next month like DATE_ADD(date, month(int type))

Question

I am new in spark , is there any built in function which will show next month date from current date like today is 27-12-2016 then the function will return 27-01-2017. i have used date_add() but no function for adding month. I have tried date_add(date, 31)but what if the month has 30 days .

spark.sql("select date_add(current_date(),31)") .show()

could anyone help me about this problem. do i need to write custom function for that ? cause i didn't find any built in code still Thanks in advance Kalyan

Answer 1

The most straightforward dataframe-friendly solution I found for adding/subtracting months

from pyspark.sql import functions as F
# assume df has "current_date" column as type DateType
months_to_add = 1  # int value, could be negative
df = df.withColumn("new_date", F.add_months("current_date", months_to_add))

This result will include any other columns previously contained in df.

Answer 2

This is not pyspark specific. You can use add_months. It's available since Spark 1.5. e.g :

spark.sql("select current_date(), add_months(current_date(),1)").show()
# +--------------+-----------------------------+
# |current_date()|add_months(current_date(), 1)|
# +--------------+-----------------------------+
# |    2016-12-27|                   2017-01-27|
# +--------------+-----------------------------+

You can also use negative integers to remove months :

spark.sql("select current_date(), add_months(current_date(),-1) as last_month").show()
# +--------------+----------+
# |current_date()|last_month|
# +--------------+----------+
# |    2016-12-27|2016-11-27|
# +--------------+----------+