The datediff() is a PySpark SQL function used to calculate the difference in days between two date or timestamp values. This function is commonly used in SQL queries or DataFrame transformations when you need to compute the number of days between two dates.
In this article, we’ll go through a series of practical examples showing how to use datediff() to calculate differences between two dates in days, months, years, hours, minutes, and seconds. We'll also see how to use it in SQL queries.
Note: To get the current date, we’ll use the current_date() function available in pyspark.sql.functions.
Key Points-
datediff()returns the number of days between two dates or timestamps, calculated asend_date - start_date.- You must cast string columns to
DateTypeusing.cast("date")before usingdatediff(). current_date()is used to retrieve the current system date for comparisons.- The result of
datediff()can be positive, negative, or zero, depending on the date order. - To get approximate months, divide the days result by
30, but this is not precise. - For exact month difference, use the
months_between()function instead of dividing days. - You can compute hours, minutes, and seconds by multiplying the day difference with
24,24*60, and24*60*60respectively. datediff()ignores the time part of timestamps; it only compares the date component.- You can use
datediff()in Spark SQL queries by registering a DataFrame as a temporary view. - The return type of
datediff()is an Integer, representing the day difference between two dates.
<a id="h-pyspark-datediff"></a>
PySpark datediff
The datediff() is a PySpark SQL function that is used to return the number of days between two date or timestamp columns. It supports columns of DateType or TimestampType. If your column is a string, you must cast it to date Otherwise, it will throw a type mismatch error. It returns the difference in days only. To get months or years, you can use months_between() for months and divide the days by 365 for an approximate year.
<a id="h-pyspark-datediff-syntax"></a>
PySpark datediff() Syntax
The following is the syntax of the datediff() function.
# Syntax of the datediff()
datediff(end, start)
<a id="h-parameters"></a>
Parameters:
start: The start date or timestamp column.end: The end date or timestamp column.
<a id="h-return-value"></a>
Return Value
It returns an Integer value representing the number of days between two dates.
<a id="h-pyspark-datediff-days"></a>
PySpark datediff Days
You can use the datediff() function to calculate the number of days between each entry in the date column and the current system date. To retrieve today’s date, you can use the current_date() function. In the example below, we’ll create a DataFrame with a column of date values and apply datediff() to find the difference between the specified date and the current date.
# Calculate the difference between current date and given date in days
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, current_date, datediff, months_between
# Create SparkSession
spark = SparkSession.builder.appName("MonthsBetweenExample").getOrCreate()
# Sample DataFrame with date column
data = [("1", "2019-07-01"),
("2", "2019-06-24"),
("3", "2019-08-24")]
df = spark.createDataFrame(data, ["id", "date"])
df = df.withColumn("date", col("date").cast("date"))
df.show()
# Calculate the difference between current date and given date in days
df.select(
col("date"),
current_date().alias("current_date"),
datediff(current_date(), col("date")).alias("datediff_days")
).show()
Yields below the output.
<a id="h-pyspark-datediff-months"></a>
PySpark datediff Months
The datediff() function calculates the difference between two dates in terms of days, not months. If you divide the number of days by 30 to estimate the number of months, keep in mind that this is only an approximation, as it assumes each month has exactly 30 days.
While this method may be acceptable for rough estimates, it doesn't account for variations in month lengths (e.g., February has 28 or 29 days, others have 30 or 31).
To calculate the exact number of months between two dates in PySpark, you should use the months_between() function from the pyspark.sql.functions module. This function returns a precise value that includes fractional months, making it ideal for accurate month-based calculations.
# Calculate the difference between current date and given date in months
df.select(
col("date"),
current_date().alias("current_date"),
months_between(current_date(), col("date")).alias("months_diff")
).show()
Yields below the output.
<a id="h-pyspark-datediff-years"></a>
PySpark datediff Years
To find the difference in years, divide the number of days returned by datediff() by 365. Again, this is an approximation and assumes no leap years.
# Calculate the difference between current date and given date in years
from pyspark.sql.functions import round
df.select(
col("date"),
round(datediff(current_date(), col("date")) / 365, 2).alias("years_diff")
).show()
Yields below the output.
# Output:
+----------+----------+
| date|years_diff|
+----------+----------+
|2019-07-01| 6.05|
|2019-06-24| 6.07|
|2019-08-24| 5.9|
+----------+----------+
<a id="h-pyspark-datediff-hours"></a>
PySpark datediff Hours
To calculate the total number of hours between two dates, multiply the result of datediff() by 24 (hours in a day).
# Calculate the difference between current date and given date in hours
df.select(
col("date"),
(datediff(current_date(), col("date")) * 24).alias("hours_diff")
).show()
Yields below the output.
# Output:
+----------+----------+
| date|hours_diff|
+----------+----------+
|2019-07-01| 53016|
|2019-06-24| 53184|
|2019-08-24| 51720|
+----------+----------+
<a id="h-pyspark-datediff-minutes"></a>
PySpark datediff Minutes
To calculate the difference in minutes, multiply the number of days by 24 * 60, since each day has 24 hours and each hour contains 60 minutes.
# Calculate the difference between current date and given date in minutes
df.select(
col("date"),
(datediff(current_date(), col("date")) * 24 * 60).alias("minutes_diff")
).show()
Yields below the output.
# Output:
+----------+------------+
| date|minutes_diff|
+----------+------------+
|2019-07-01| 3180960|
|2019-06-24| 3191040|
|2019-08-24| 3103200|
+----------+------------+
<a id="h-pyspark-datediff-seconds"></a>
PySpark datediff Seconds
To get the difference in seconds, multiply the day difference by 24 * 60 * 60, as each minute has 60 seconds.
# Calculate the difference between current date and given date in seconds
df.select(
col("date"),
(datediff(current_date(), col("date")) * 24 * 60 * 60).alias("seconds_diff")
).show()
Yields below the output.
# Output:
+----------+------------+
| date|seconds_diff|
+----------+------------+
|2019-07-01| 190857600|
|2019-06-24| 191462400|
|2019-08-24| 186192000|
+----------+------------+
<a id="h-pyspark-datediff-sql"></a>
PySpark datediff SQL
You can also use the datediff() function inside Spark SQL queries. First, register your DataFrame as a temporary view. Then use a SQL query to calculate the day difference between the date column and the current date.
# use a SQL query to calculate the day difference between the date column and current column
df.createOrReplaceTempView("dates_table")
spark.sql("""
SELECT
date,
current_date() AS current_date,
datediff(current_date(), date) AS datediff_days
FROM dates_table
""").show()
Yields below the output.
# Output:
+----------+------------+-------------+
| date|current_date|datediff_days|
+----------+------------+-------------+
|2019-07-01| 2025-07-18| 2209|
|2019-06-24| 2025-07-18| 2216|
|2019-08-24| 2025-07-18| 2155|
+----------+------------+-------------+
<a id="h-frequently-asked-questions-of-pyspark-datediff"></a>
Frequently Asked Questions of PySpark datediff()
The datediff() function returns the number of days between two date or timestamp columns. The result is calculated as: Example:
datediff(end_date, start_date)
datediff() supports columns of DateType or TimestampType. If your column is a string, you must cast it to date using: Example:
col("your_column").cast("date")
If the column is in string format, you must first cast it to a date. Otherwise, it will throw a type mismatch error.
The datediff() returns the difference in days only. To get months or years:
Use months_between() for months and divide the days by 365 for an approximate years.
Multiply the day difference by:
24 for hours
24 * 60 for minutes
24 * 60 * 60 for seconds
You can use it inside SQL queries after registering the DataFrame as a temporary view:
df.createOrReplaceTempView("my_table")
spark.sql("""
SELECT datediff(current_date(), date_column) AS diff_days
FROM my_table
""")
<a id="h-conclusion"></a>
Conclusion
In this article, you learned how to use the datediff() function in PySpark to calculate the difference between two date values in days, and how to extend that logic to compute approximate differences in months, years, hours, minutes, and seconds. You also explored how to use the datediff() function in both DataFrame API and Spark SQL, along with casting and formatting best practices.
Happy Learning!!
<a id="h-references"></a>