[jira] [Created] (SPARK-57832) Support timestamp - timestamp subtraction for nanosecond-precision timestamps (microsecond-precision interval result)

["Max Gekk (Jira)" <ji...@apache.org>]

Max Gekk created SPARK-57832:
--------------------------------

             Summary: Support timestamp - timestamp subtraction for nanosecond-precision timestamps (microsecond-precision interval result)
                 Key: SPARK-57832
                 URL: https://issues.apache.org/jira/browse/SPARK-57832
             Project: Spark
          Issue Type: Sub-task
          Components: SQL
    Affects Versions: 4.3.0
            Reporter: Max Gekk


This sub-task is part of the umbrella SPARK-56822 (timestamps with nanosecond precision).

h2. Problem
{{SubtractTimestamps}} (datetimeExpressions.scala ~L4040-4090) types both operands as {{AnyTimestampType}} (microsecond only) and evaluates on {{Long}} micros, returning {{DayTimeIntervalType()}} (or legacy {{CalendarIntervalType}}). Subtracting two nanosecond timestamps ({{ts_nanos - ts_nanos}}) currently fails analysis. The SPIP had deferred this because the day-time interval result type has only microsecond precision; per decision we now support it and accept the microsecond-precision result.

h2. Goal
Allow {{TIMESTAMP_NTZ(p) - TIMESTAMP_NTZ(p)}} and {{TIMESTAMP_LTZ(p) - TIMESTAMP_LTZ(p)}} (and microsecond/nanosecond mixed operands via existing widening, SPARK-57454), returning a {{DayTimeIntervalType}} computed from the two {{TimestampNanosVal}} values. The result is microsecond-precision: the sub-microsecond remainder difference is dropped, with the rounding/truncation rule documented (default: truncate toward the microsecond grid, i.e. compute from epoch micros).

h2. Scope
Extend {{SubtractTimestamps.inputTypes}} to accept {{AnyTimestampNanoType}}; add a {{TimestampNanosVal}} eval/codegen branch computing {{leftMicros - rightMicros}} (documented handling of the {{nanosWithinMicro}} remainder); keep the result type {{DayTimeIntervalType()}} / legacy {{CalendarIntervalType}} unchanged; ensure mixed-precision operands resolve through type coercion. Wire the nanosecond cases into the subtraction path in {{BinaryArithmeticWithDatetimeResolver}} if needed.

h2. Non-goals
No new sub-microsecond interval type; the day-time interval remains microsecond-precision (this is the accepted precision loss).

h2. Acceptance criteria
* {{ts_nanos - ts_nanos}} returns a {{DayTimeIntervalType}} value equal to the microsecond-grid difference; NTZ/LTZ zone semantics match the microsecond behavior; mixed microsecond/nanosecond operands work; interpreted + codegen tested.

h2. Testing
{{DateExpressionsSuite}}; {{timestamp-ntz-nanos.sql}} / {{timestamp-ltz-nanos.sql}} golden files.

h2. Dependencies
None hard - coordinate with the overflow/range-hardening sub-task; mixed microsecond/nanosecond operands via resolved SPARK-57454.




--
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