Spark dataframe convert integer to timestamp and find date difference

Question

I have this DataFrame org.apache.spark.sql.DataFrame:

|-- timestamp: integer (nullable = true)
|-- checkIn: string (nullable = true)

| timestamp|   checkIn|
+----------+----------+
|1521710892|2018-05-19|
|1521710892|2018-05-19|

Desired result: obtain a new column with day difference between date checkIn and timestamp (2018-03-03 23:59:59 and 2018-03-04 00:00:01 should have a difference of 1)

Thus, i need to

• convert timestamp to date (This is where i'm stuck)
• take out one date from another
• use some function to extract day(Have not found this function yet)

Answer 1

You can use from_unixtime to convert your timestamp to date and datediff to calculate the difference in days:

val df = Seq(
  (1521710892, "2018-05-19"),
  (1521730800, "2018-01-01")
).toDF("timestamp", "checkIn")

df.withColumn("tsDate", from_unixtime($"timestamp")).
  withColumn("daysDiff", datediff($"tsDate", $"checkIn")).
  show

// +----------+----------+-------------------+--------+
// | timestamp|   checkIn|             tsDate|daysDiff|
// +----------+----------+-------------------+--------+
// |1521710892|2018-05-19|2018-03-22 02:28:12|     -58|
// |1521730800|2018-01-01|2018-03-22 08:00:00|      80|
// +----------+----------+-------------------+--------+