trunc / add_months

What they do

Note the argument order: trunc(column, unit) but date_trunc(unit, column). trunc returns a date and only knows date units; date_trunc returns a timestamp and also handles hours, minutes and weeks.

How add_months handles month ends

Adding a month to January 31 cannot give February 31. Spark returns the last day of the target month instead: February 28 (or 29 in a leap year). Going back is not symmetric: add_months("2025-02-28", 1) is March 28, not March 31.

Step by step: monthly revenue

Run the example

from pyspark.sql import functions as F
result = (orders
    .withColumn("month", F.trunc("order_date", "month"))
    .groupBy("month").agg(F.sum("amount").alias("revenue"))
    .withColumn("next_month", F.add_months("month", 1))
    .orderBy("month"))

SELECT trunc(order_date, 'month')                AS month,
       SUM(amount)                                 AS revenue,
       add_months(trunc(order_date, 'month'), 1)   AS next_month
FROM orders
GROUP BY trunc(order_date, 'month')
ORDER BY month

Month over month

Two common ways to compare each month with the previous one: lag(revenue) over months ordered in time (fast, but skips months with no data), or join the monthly table to itself on a.month = add_months(b.month, 1) (correct even when months are missing, and clearer about which month is compared). For guaranteed rows for every month, generate a calendar with sequence(start, end, interval 1 month) and left join to it.

Parsing first

These functions need real dates. A string in ISO format (yyyy-MM-dd) is cast automatically; anything else must be parsed with F.to_date(col, "dd/MM/yyyy"). In Spark 3, an unparseable string returns null; with ANSI mode in Spark 4.0, to_date raises an error unless you use try_to_date.

Common mistakes