Scala中BIGINT转TIMESTAMP出现无效值，寻求技术协助

Hey there, let's tackle this BIGINT to TIMESTAMP conversion issue in Scala together! I've run into similar headaches before, so here's a breakdown of common causes and fixes to get you sorted:

1. First: Confirm if your BIGINT is seconds or milliseconds #

The most common culprit here is mismatched timestamp units. TIMESTAMP in Scala (via java.sql.Timestamp) expects milliseconds since the Unix epoch (1970-01-01 00:00:00 UTC). If your BIGINT is storing seconds instead, you'll end up with invalid, way-too-early dates.

Example of the problem: #

• A valid second-level timestamp: 1690000000 → should map to 2023-07-22
• If you pass this directly to Timestamp, you get 1970-01-20 03:46:40 (totally wrong!)

Fix: #

• For second-level timestamps: Multiply by 1000 first

  import java.sql.Timestamp
  val secondLevelTs: Long = 1690000000L
  val validTimestamp = new Timestamp(secondLevelTs * 1000)
  

• For millisecond-level timestamps: Convert directly

  val millisLevelTs: Long = 1690000000000L
  val validTimestamp = new Timestamp(millisLevelTs)
  

2. Check if your BIGINT is out of TIMESTAMP range #

java.sql.Timestamp has a valid range (for 32-bit systems, it's 1970-01-01 00:00:00.000 to 2038-01-19 03:14:07.999; 64-bit supports much further into the future, but edge cases still exist). If your BIGINT falls outside this window, you'll get invalid values.

Fix: #

Add a range check before conversion:

val minValidMillis = Timestamp.valueOf("1970-01-01 00:00:00").getTime
val maxValidMillis = Timestamp.valueOf("2038-01-19 03:14:07").getTime
val yourBigInt: Long = // Your value here

if (yourBigInt < minValidMillis || yourBigInt > maxValidMillis) {
  println(s"Invalid timestamp value: $yourBigInt is outside valid range")
} else {
  val validTimestamp = new Timestamp(yourBigInt)
  // Proceed with your logic
}

3. Handle nulls or invalid BIGINT values #

If your data source has nulls or non-numeric values masquerading as BIGINTs, conversion will fail silently or produce garbage.

Fix: #

Use Option to safely handle edge cases:

val optionalBigInt: Option[Long] = // Fetch value from your data source
optionalBigInt match {
  case Some(millis) if millis >= minValidMillis && millis <= maxValidMillis =>
    val validTimestamp = new Timestamp(millis)
    // Process valid timestamp
  case Some(invalidVal) =>
    println(s"Skipping invalid timestamp value: $invalidVal")
    // Handle invalid value (e.g., set default, log error)
  case None =>
    println("Encountered null BIGINT value")
    // Handle null case
}

4. If you're using Spark SQL for conversion #

If your query is in Spark, make sure you're using the right built-in functions:

• For second-level BIGINTs: Use from_unixtime(bigint_column)
• For millisecond-level BIGINTs: Use timestamp_millis(bigint_column)

Example of wrong vs right: #

-- Wrong: Using millisecond function on second-level value
SELECT timestamp_millis(1690000000) -- Returns 1970-01-20 03:46:40

-- Correct: For second-level values
SELECT from_unixtime(1690000000) -- Returns 2023-07-22 02:13:20

-- Correct: For millisecond-level values
SELECT timestamp_millis(1690000000000) -- Returns 2023-07-22 02:13:20

If you share your exact query, I can help pinpoint the exact issue even faster!

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