SPARK SQl中的DATEDIFF [英] DATEDIFF in SPARK SQl
问题描述
我是Spark SQL的新手.我们正在将数据从SQL Server迁移到Databricks. 我正在使用SPARK SQL.您能否为以下datefunctions建议如何在SPARK sql中实现以下功能.我可以看到datediff在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 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)
DATEDIFF(YEAR,StartDate,EndDate) DATEDIFF(Month,StartDate,EndDate) DATEDIFF(Quarter,StartDate,EndDate)
推荐答案
正如您提到的,SparkSQL确实支持DATEDIFF
,但仅支持几天.我也要小心,因为参数似乎与Spark相反,即
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确实支持类似的功能,称为months_between
,可以代替DATEDIFF( month ...
来使用.此函数还会返回一个十进制数,因此可以选择将其强制转换为INT
,以获得与
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;
还有year
和quarter
函数,分别用于确定日期的年和季度.您可以简单地减去年份,但是季度会更加棘手.可能是您必须做数学"或最终使用日历表.
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.
这篇关于SPARK SQl中的DATEDIFF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!