Spark SQL datediff 以秒为单位 [英] Spark SQL datediff in seconds
本文介绍了Spark SQL datediff 以秒为单位的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下代码:
table.select(datediff(table.col("Start Time"), table.col("End Time"))).show()
<块引用>
日期格式为 2016-05-19 09:23:28 (YYYY-MM-DD HH:mm:SS
)
函数 datediff 计算天数差异.但我想以秒为单位.
解决方案
您可以使用 unix_timestamp()
函数将日期转换为秒.
import org.apache.spark.sql.functions._//对于 $ 符号列//Spark 2.0导入 spark.implicits._table.withColumn(date_diff",(unix_timestamp($"Start Time") - unix_timestamp($"End Time"))).展示()
(根据评论)
UDF 将秒数转换为 HH:mm:ss
sqlContext.udf.register(sec_to_time", (s: Long) =>((s/3600L) + ":" + (s/60L) + ":" + (s % 60L)))//现在使用注册的UDFtable.withColumn(date_diff",sec_to_time(unix_timestamp($"Start Time") - unix_timestamp($"End Time"))).展示()
I've got following code:
table.select(datediff(table.col("Start Time"), table.col("End Time"))).show()
Date format is 2016-05-19 09:23:28 (
YYYY-MM-DD HH:mm:SS
)
Function datediff calculate the difference in days. But I would like to have the difference in seconds.
解决方案
You can use unix_timestamp()
function to convert date to seconds.
import org.apache.spark.sql.functions._
//For $ notation columns // Spark 2.0
import spark.implicits._
table.withColumn("date_diff",
(unix_timestamp($"Start Time") - unix_timestamp($"End Time"))
).show()
Edit:(As per comment)
UDF to covert Seconds to HH:mm:ss
sqlContext.udf.register("sec_to_time", (s: Long) =>
((s / 3600L) + ":" + (s / 60L) + ":" + (s % 60L))
)
//Use registered UDF now
table.withColumn("date_diff",
sec_to_time(unix_timestamp($"Start Time") - unix_timestamp($"End Time"))
).show()
这篇关于Spark SQL datediff 以秒为单位的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文