You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Vinod KC (Jira)" <ji...@apache.org> on 2025/12/05 03:53:00 UTC
[jira] [Created] (SPARK-54601) Data loss in TimestampType when writing to MS SQL Server: Microsecond precision truncated to milliseconds
Vinod KC created SPARK-54601:
--------------------------------
Summary: Data loss in TimestampType when writing to MS SQL Server: Microsecond precision truncated to milliseconds
Key: SPARK-54601
URL: https://issues.apache.org/jira/browse/SPARK-54601
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.0.0, 3.5.0
Reporter: Vinod KC
Spark silently loses microsecond precision when writing *timestamps* to MS SQL Server via JDBC, causing data corruption.
Spark maps TimestampType/TimestampTypeNTZ to SQL Server's legacy DATETIME type which only supports millisecond precision (3 decimal places). The last 3 decimal places (microseconds) are automatically truncated with no warning.
Data Loss Example - Complete Round-Trip
{code:java}
import java.sql.Timestamp
// Create data with Spark's full microsecond precision
val data = Seq(
(1, Timestamp.valueOf("2024-12-03 14:25:37.789123")),
(2, Timestamp.valueOf("2024-12-03 14:25:37.789456"))
).toDF("id", "timestamp")
// Original microseconds: 789123, 789456 (Spark stores 6 decimal places)
data.collect().foreach(r =>
println(s"Microseconds: ${r.getAs[Timestamp]("timestamp").getNanos / 1000}")
)
// Output: Microseconds: 789123, Microseconds: 789456
// Write to SQL Server (mapped to DATETIME - only 3 decimal places!)
data.write.mode("overwrite").jdbc(jdbcUrl, "test_table", props)
// Read back
val result = spark.read.jdbc(jdbcUrl, "test_table", props)
// Retrieved microseconds: 789000, 789000 ❌ DATA LOST!
result.collect().foreach(r =>
println(s"Microseconds: ${r.getAs[Timestamp]("timestamp").getNanos / 1000}")
)
// Output: Microseconds: 789000, Microseconds: 789000
// Lost: 123 and 456 microseconds respectively (digits 4-6)
// Both records now appear simultaneous - ordering corrupted
{code}
*Root cause :*
Spark currently maps TimestampType and TimestampNTZType to SQL Server's legacy DATETIME type, which only supports 3 decimal places (millisecond precision). This causes automatic truncation of any microsecond data beyond the third decimal place.
*References:*
Microsoft Documentation: "Avoid using datetime for new work. Instead, use the datetime2 data type."
[https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql]
{code:java}
DATETIME limitations: 3.33 milliseconds precision (rounded)
DATETIME2 precision: 100 nanoseconds (0.0000001 seconds){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org