Datediff in Spark Sql
I Am New to Spark Sql. We Are Migrating Data from Sql Server to Databricks. I Am Using Spark Sql. Can You Please Suggest How to Achieve Below Functionality in...
I am new to Spark SQL. We are migrating data from SQL server to Databricks. I am using SPARK SQL . Can you please suggest how to achieve below functionality in SPARK sql for the below datefunctions. I can see datediff gives only days in spark sql.
DATEDIFF(YEAR,StartDate,EndDate)
DATEDIFF(Month,StartDate,EndDate)
DATEDIFF(Quarter,StartDate,EndDate)
2 Answers
As you have mentioned SparkSQL does support DATEDIFF but for days only. I would also be careful as it seems the parameters are the opposite way round for Spark, ie
--SQL Server
DATEDIFF ( datepart , startdate , enddate )
--Spark
DATEDIFF ( enddate , startdate )
Spark does however support a similar function called months_between which you could use in place of DATEDIFF( month .... This function also returns a decimal amount so optionally cast it to INT for similar functionality to the
SELECT startDate, endDate,
DATEDIFF( endDate, startDate ) AS diff_days,
CAST( months_between( endDate, startDate ) AS INT ) AS diff_months
FROM yourTable
ORDER BY 1;
There are also year and quarter functions for determining the year and quarter of a date respectively. You could simply minus the years but quarters would be more tricky. It may be you have to 'do the math' or end up using a calendar table.
As Spark doesn't provide the other unit, I use below method,
select
(bigint(to_timestamp(endDate))) - (bigint(to_timestamp(startDate))) as time_diff
This results in the second unit, so dividing by 60 or 3600 can transform the units.
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
