Scala中DataFrame时间列求差值：datediff返回0的解决办法

Hey there! Let's figure out why your datediff is returning 0 and fix that time difference calculation properly.

The issue here is that datediff() is designed to calculate the number of days between two dates—not the difference between times within the same day. Since your time1 and time2 only have hour-minute-second values (no date), Spark automatically appends the current system date to both timestamps when you use to_timestamp(). That means both timestamps fall on the exact same day, so datediff() always returns 0.

We need to focus on computing differences at the hour/minute/second level instead. Here are a few reliable methods:

Method 1: Use unix_timestamp() for Second-Level Differences #

unix_timestamp() converts a time string to the number of seconds since the Unix epoch (1970-01-01). Subtracting these values gives you the exact second difference between your two times:

import org.apache.spark.sql.functions.{unix_timestamp, col}

val resultDF = df.withColumn("diff_seconds", 
  unix_timestamp(col("time1"), "hh:mm:ss") - unix_timestamp(col("time2"), "hh:mm:ss")
)

For your sample data, this would produce:

Method 2: Use timestamp_diff() (Spark 3.0+) #

If you're using Spark 3.0 or later, timestamp_diff() is the cleaner, more readable option. It lets you specify the unit of time you want the difference in (seconds, minutes, hours, etc.):

import org.apache.spark.sql.functions.{timestamp_diff, to_timestamp, col}

// Calculate difference in seconds
val dfWithSecDiff = df.withColumn("diff_seconds", 
  timestamp_diff(to_timestamp(col("time1"), "hh:mm:ss"), to_timestamp(col("time2"), "hh:mm:ss"), "second")
)

// Calculate difference in minutes (automatically rounds)
val dfWithMinDiff = df.withColumn("diff_minutes", 
  timestamp_diff(to_timestamp(col("time1"), "hh:mm:ss"), to_timestamp(col("time2"), "hh:mm:ss"), "minute")
)

Method 3: Manually Split Time Components (For Full Control) #

If you need more flexibility (like formatting the difference back into hh:mm:ss), you can split the time strings into hours, minutes, seconds, convert them to total seconds, then subtract:

import org.apache.spark.sql.functions.{split, col}

// Convert time1 to total seconds
val dfWithTime1Sec = df.withColumn("time1_sec", 
  split(col("time1"), ":").getItem(0).cast("int") * 3600 + 
  split(col("time1"), ":").getItem(1).cast("int") * 60 + 
  split(col("time1"), ":").getItem(2).cast("int")
)

// Convert time2 to total seconds
val dfWithTime2Sec = dfWithTime1Sec.withColumn("time2_sec", 
  split(col("time2"), ":").getItem(0).cast("int") * 3600 + 
  split(col("time2"), ":").getItem(1).cast("int") * 60 + 
  split(col("time2"), ":").getItem(2).cast("int")
)

// Calculate second difference
val resultDF = dfWithTime2Sec.withColumn("diff_seconds", col("time1_sec") - col("time2_sec"))

If you want the difference in a human-readable hh:mm:ss format, use from_unixtime()—just handle negative values explicitly:

import org.apache.spark.sql.functions.{from_unixtime, when, abs, concat}

val dfWithFormattedDiff = resultDF.withColumn("diff_hhmmss", 
  when(col("diff_seconds") >= 0, from_unixtime(col("diff_seconds").cast("int"), "HH:mm:ss"))
  .otherwise(concat("-", from_unixtime(abs(col("diff_seconds")), "HH:mm:ss")))
)

If your time pairs might span midnight (e.g., time1 = 23:59:59 and time2 = 00:00:01), the above methods will return a negative difference since Spark treats both times as the same day. To handle this, you'll need an additional date column to indicate which day each time belongs to, or adjust the logic to assume the later time is the next day if time1 is earlier than time2.

内容的提问来源于stack exchange，提问作者Nakeuh