Scala函数计算日期小时数报错求助，涉及java.time API

Hey there! Let’s work through this problem together—since you’re hitting errors when calculating date hour differences using your toEnd, ToStart, and jourOuvree Scala functions with java.time, I’ll break down common pitfalls, share a working implementation, and show how to safely apply it to a Spark DataFrame.

First, let’s start with the most frequent issues that cause errors in this scenario, then build out a robust solution aligned with your use case.

Common Error Triggers #

Before diving into code, here’s what’s likely causing your failures:

• Type mismatches: Mixing legacy java.util.Date/java.sql.Timestamp with modern java.time types (Spark relies on Timestamp for date columns, which maps to java.time.LocalDateTime)
• Unhandled nulls: DataFrames often contain null date values, which throw NPEs if not explicitly handled
• Incorrect duration logic: Using Period (for day/month/year gaps) instead of Duration/ChronoUnit for hour/minute calculations
• Missing timezone handling: Forgetting to align timezones when converting or comparing dates

Step 1: Refine Helper Functions #

I’ll assume your three functions have these intended behaviors (adjust if your logic differs):

• ToStart: Converts a date to the start of its day (e.g., 2024-05-20 14:30 → 2024-05-20 00:00:00)
• toEnd: Converts a date to the end of its day (e.g., 2024-05-20 14:30 → 2024-05-20 23:59:59.999)
• jourOuvree: Checks if a date is a working day (non-weekend/non-holiday)

Here’s how to implement these properly with java.time:

import java.time._
import java.time.temporal.ChronoUnit

object DateUtils {
  // Get start of the day for a given LocalDateTime
  def ToStart(date: LocalDateTime): LocalDateTime = date.truncatedTo(ChronoUnit.DAYS)

  // Get end of the day (down to nanoseconds)
  def toEnd(date: LocalDateTime): LocalDateTime = 
    date.truncatedTo(ChronoUnit.DAYS)
      .plusHours(23)
      .plusMinutes(59)
      .plusSeconds(59)
      .plusNanos(999_999_999)

  // Example working day check (add your holiday logic here)
  def jourOuvree(date: LocalDateTime): Boolean = {
    val day = date.getDayOfWeek
    day != DayOfWeek.SATURDAY && day != DayOfWeek.SUNDAY
  }
}

Step 2: Build the Hour Difference Function #

Now let’s create the function to calculate valid working hours between two dates, using your helper functions:

object DateUtils {
  // ... (keep the helper functions above)

  def calculateWorkingHours(start: LocalDateTime, end: LocalDateTime): Double = {
    // Handle reverse date order
    if (start.isAfter(end)) return 0.0
    // Handle non-working days for both start and end
    if (!jourOuvree(start) && !jourOuvree(end)) return 0.0

    var totalHours = 0.0
    var currentDay = ToStart(start)

    // Add full working days between start and end dates
    while (currentDay.isBefore(ToStart(end))) {
      if (jourOuvree(currentDay)) totalHours += 8.0 // Adjust to your workday length
      currentDay = currentDay.plusDays(1)
    }

    // Add partial hours from the start day
    if (jourOuvree(start)) {
      val dayEnd = toEnd(start)
      val actualEnd = if (dayEnd.isAfter(end)) end else dayEnd
      totalHours += ChronoUnit.MINUTES.between(start, actualEnd) / 60.0
    }

    // Add partial hours from the end day (only if start/end are different days)
    if (!ToStart(start).isEqual(ToStart(end)) && jourOuvree(end)) {
      val dayStart = ToStart(end)
      val actualStart = if (dayStart.isBefore(start)) start else dayStart
      totalHours += ChronoUnit.MINUTES.between(actualStart, end) / 60.0
    }

    totalHours
  }
}

Step 3: Apply to Spark DataFrame #

To use this function in a DataFrame, wrap it in a UDF with null handling (critical for real-world data):

import org.apache.spark.sql.functions._
import org.apache.spark.sql.types.TimestampType

// Register UDF with null safety
val workingHoursUDF = udf((start: java.sql.Timestamp, end: java.sql.Timestamp) => {
  if (start == null || end == null) None
  else {
    val startLdt = start.toLocalDateTime
    val endLdt = end.toLocalDateTime
    Some(DateUtils.calculateWorkingHours(startLdt, endLdt))
  }
})

// Use the UDF in your DataFrame
val dfWithWorkingHours = yourOriginalDF.withColumn(
  "working_hours",
  workingHoursUDF(col("start_timestamp"), col("end_timestamp"))
)

If You’re Still Getting Errors #

If you run into specific exceptions (like ClassCastException, DateTimeParseException, or NPEs), share the exact error message and your existing code snippet—this will help narrow down the issue even further.

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