Calculate Difference Between Dates in Spark Scala

How to calculate the difference between two dates in days, months and years in Spark with Scala. To get the differences between two dates in days, months, and years, you can use the SQL functions datediff() and months_between(). The datediff() actually returns the differences in days and monts_between() returns differences in months and use any of these functions to calculate the difference in years.

In this tutorial, I will show you a SQL Dataframe example of how to calculate a difference between two dates in Days, Months, and Years using Scala language.

Spark Get Date Difference in Days

The Spark SQL datediff() function is used to calculate the difference between two dates in days. This function takes the end date as the first argument and the start date as the second argument and returns the difference between the end date and the start date in days.


// Imports
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.{col, current_date, datediff}

// Initialize SparkSession
val spark = SparkSession.builder
  .appName("aistudylabs")
  .getOrCreate()

// Sample data
val data = Seq("2019-07-01", "2019-06-24", "2019-08-24", "2018-07-23")

// Create DataFrame and name the column as "date"
import spark.implicits._
val df = data.toDF("date")

// Calculate date difference with current date
val df2 = df.select(
  col("date"),
  current_date().as("current_date"),
  datediff(current_date(), col("date")).as("datediff")
)

// Show the result
df2.show()

Here, df.select()is used to select specific columns from the DataFrame df or apply transformations on columns. It creates a new DataFrame containing only the specified columns or the result of the transformations.

The current_date() returns the systems current date.

The datediff(current_date(), col("date")).as("datediff") calculates the difference in days between the current date and the values in the "date" column using the datediff() function. It then renames the resulting column as "datediff".


// Output:
+----------+------------+--------+
|      date|current_date|datediff|
+----------+------------+--------+
|2019-07-01|  2024-03-26|    1730|
|2019-06-24|  2024-03-26|    1737|
|2019-08-24|  2024-03-26|    1676|
|2018-07-23|  2024-03-26|    2073|
+----------+------------+--------+

Spark Get Date Difference in Months

The months_between() function in Spark SQL calculates the difference in months between two dates or timestamps. It returns a floating-point number representing the number of months between the two specified dates. You can use the round() function to round the result if needed.


// Imports
import org.apache.spark.sql.functions.{col, current_date, datediff, months_between, round}
import spark.implicits._

// Create DataFrame
val df3 = Seq(("2019-07-01"),("2019-06-24"),("2019-08-24"),
    ("2018-12-23"),("2018-07-20"))
    .toDF("startDate").select(
    col("startDate"),current_date().as("endDate")
  )

// Calculate difference in months
df3.withColumn("datesDiff", datediff(col("endDate"),col("startDate")))
    .withColumn("montsDiff", months_between(
      col("endDate"),col("startDate")))
    .withColumn("montsDiff_round",round(months_between(
      col("endDate"),col("startDate")),2))
    .show()

Output:


// Output:
+----------+----------+---------+-----------+---------------+
| startDate|   endDate|datesDiff|  montsDiff|montsDiff_round|
+----------+----------+---------+-----------+---------------+
|2019-07-01|2024-03-26|     1730|56.80645161|          56.81|
|2019-06-24|2024-03-26|     1737|57.06451613|          57.06|
|2019-08-24|2024-03-26|     1676|55.06451613|          55.06|
|2018-12-23|2024-03-26|     1920|63.09677419|           63.1|
|2018-07-20|2024-03-26|     2076|68.19354839|          68.19|
+----------+----------+---------+-----------+---------------+

Spark Get Date Difference in Years

In Spark, you can use the datediff () or months_between() functions to calculate the difference between two dates in years. If you use datediff(), you should divide the difference in days by 365.0 to get the difference in years. For months_between(), you should divide the difference in months by 12.0 to get the difference in years.


// Imports
import org.apache.spark.sql.functions.{col, current_date, datediff, months_between, round}
df3.withColumn("datesDiff", datediff(col("endDate"),col("startDate")))
    .withColumn("yearsDiff",months_between(
      col("endDate"),col("startDate"),true).divide(12))
    .withColumn("yearsDiff_round",round(months_between(
      col("endDate"),col("startDate"),true).divide(12),2))
    .show()

This yields the below output.


// Output:
+----------+----------+---------+------------------+---------------+
| startDate|   endDate|datesDiff|         yearsDiff|yearsDiff_round|
+----------+----------+---------+------------------+---------------+
|2019-07-01|2024-03-26|     1730| 4.733870967500001|           4.73|
|2019-06-24|2024-03-26|     1737| 4.755376344166667|           4.76|
|2019-08-24|2024-03-26|     1676|      4.5887096775|           4.59|
|2018-12-23|2024-03-26|     1920|5.2580645158333335|           5.26|
|2018-07-20|2024-03-26|     2076| 5.682795699166667|           5.68|
+----------+----------+---------+------------------+---------------+

Differences when Dates are not it DateType Format

Let's see another example of the difference between two dates when dates are not in DateType format 'yyyy-MM-dd'. when dates are not in DateType format, all functions return null. Hence, first, convert the input dates to DateType using to_date function and then calculate the differences.


// When dates are are in DateType format
val dfDate = Seq(("07-01-2019"),("06-24-2019"),("08-24-2019"),
("12-23-2018"),("07-20-2018"))
    .toDF("startDate").select(
    to_date(col("startDate"),"MM-dd-yyyy").as("startDate"),
    current_date().as("endDate")

Conclusion:

By using Spark SQL Date and Time functions datediff(), and months_between() you can calculate the differences between two dates in days, months, and years. The months_between() function calculates the difference in months between two dates or timestamps, whereas the datediff() function calculates the difference in days between two dates or timestamps. Use any of these two functions to get the difference in years.

Happy Learning !!