CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
data_person
data_person

Reputation: 4500

running timestamp difference in spark scala

Input DF:

main_id sub_id time
 1 .     11 .  12:00
 1 .     12     1:00
 1 .     12 .   3:00
 1 .     12 .   5:00
 1 .     13 .   7:00
 1 .     13 .   8:00
 2 .     21 .  12:00
 2 .     21     5:00

I am trying to find the running timestamp difference with respect to the main_id alone

Output DF:

main_id  sub_id .  time    diff
  1 .      11 .    12:00    null
  1.       12 .    1:00 .    1
  1 .      12 .    3:00 .    2
  1 .      12 .    5:00 .    2
  1 .      13 .    7:00 .    2
  1 .      13 .    8:00 .    1
  2 .      21 .   12:00 .   null
  2 .      21 .    5:00 .    5

Code Tried:

val needed_window = Window.partitionBy($"main_id").orderBy($"main_id")
val diff_time = diff($"time").over(partitionWindow)
df.select($"*", diff_time as "time_diff").show

I am getting error in the diff function, is there a way to implement this. Any suggestions please.

Upvotes: 2

Views: 5193

Answers (1)

Leo C
Leo C

Reputation: 22449

Assuming your time column is of type Timestamp, you can calculate the time difference between the current row and previous row using unix_timestamp along with the lag Window function.

import java.sql.Timestamp
import org.apache.spark.sql.functions._
import org.apache.spark.sql.expressions.Window

val df = Seq(
  (1, 11, Timestamp.valueOf("2018-06-01 12:00:00")),
  (1, 12, Timestamp.valueOf("2018-06-01 13:00:00")),
  (1, 12, Timestamp.valueOf("2018-06-01 15:00:00")),
  (1, 12, Timestamp.valueOf("2018-06-01 17:00:00")),
  (1, 13, Timestamp.valueOf("2018-06-01 19:00:00")),
  (1, 13, Timestamp.valueOf("2018-06-01 20:00:00")),
  (2, 21, Timestamp.valueOf("2018-06-01 12:00:00")),
  (2, 21, Timestamp.valueOf("2018-06-01 17:00:00"))
).toDF("main_id", "sub_id", "time")

val window = Window.partitionBy($"main_id").orderBy($"main_id")

df.withColumn("diff",
  (unix_timestamp($"time") - unix_timestamp(lag($"time", 1).over(window))) / 3600.0
).show
// +-------+------+-------------------+----+
// |main_id|sub_id|               time|diff|
// +-------+------+-------------------+----+
// |      1|    11|2018-06-01 12:00:00|null|
// |      1|    12|2018-06-01 13:00:00| 1.0|
// |      1|    12|2018-06-01 15:00:00| 2.0|
// |      1|    12|2018-06-01 17:00:00| 2.0|
// |      1|    13|2018-06-01 19:00:00| 2.0|
// |      1|    13|2018-06-01 20:00:00| 1.0|
// |      2|    21|2018-06-01 12:00:00|null|
// |      2|    21|2018-06-01 17:00:00| 5.0|
// +-------+------+-------------------+----+

Upvotes: 3

Related Questions