This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new 58f5de6bc186 [SPARK-57814][SQL] Support NTZ and nanosecond-precision
timestamps in unix_seconds, unix_millis and unix_micros
58f5de6bc186 is described below
commit 58f5de6bc186cf5a145cf2b669782778bf2d5db9
Author: Stevo Mitric <[email protected]>
AuthorDate: Fri Jul 24 11:24:30 2026 +0000
[SPARK-57814][SQL] Support NTZ and nanosecond-precision timestamps in
unix_seconds, unix_millis and unix_micros
### What changes were proposed in this pull request?
Widen the input types of the `unix_seconds` / `unix_millis` / `unix_micros`
functions to accept `TIMESTAMP_NTZ` and the nanosecond-precision timestamp
types.
For the nanosecond carrier the child evaluates to a `TimestampNanosVal`, so
`nullSafeEval` reads its `epochMicros` field and `doGenCode` routes the value
through a `toMicros` helper.
### Why are the changes needed?
Previously these functions declared inputTypes = Seq(TimestampType) and,
via ExpectsInputTypes, accepted only TIMESTAMP_LTZ directly. A TIMESTAMP_NTZ or
nanosecond-precision timestamp argument was rejected with a DATATYPE_MISMATCH
error - there was no implicit cast to TIMESTAMP_LTZ. This change widens the
accepted input types so those timestamps are read directly, with no time-zone
shift, bringing unix_seconds/unix_millis/unix_micros to parity across the
timestamp families.
### Does this PR introduce _any_ user-facing change?
Yes, but purely additive. unix_seconds/unix_millis/unix_micros now accept
TIMESTAMP_NTZ and the nanosecond-precision timestamp types directly, reading
them with no time-zone shift.
This does not change the result of any query that previously succeeded:
those argument types were previously rejected with DATATYPE_MISMATCH, so there
is no prior value to reproduce (e.g. by casting to TIMESTAMP_LTZ). Only queries
that used to error now return a result.
### How was this patch tested?
Added a `DateExpressionsSuite` case and end-to-end coverage in
`TimestampNanosFunctionsSuiteBase`.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Opus 4.8
Closes #57085 from stevomitric/stevomitric/spark-57814-nanos.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
(cherry picked from commit bb4741e52fbd3e8fdeb8a8ac296884d7bd27d9b3)
Signed-off-by: Uros Bojanic <[email protected]>
---
docs/sql-migration-guide.md | 1 +
.../catalyst/expressions/datetimeExpressions.scala | 45 +++++++++++++++-----
.../expressions/DateExpressionsSuite.scala | 44 ++++++++++++++++++++
.../analyzer-results/timestamp-ltz-nanos.sql.out | 42 +++++++++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 42 +++++++++++++++++++
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 12 ++++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 12 ++++++
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 48 ++++++++++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 48 ++++++++++++++++++++++
.../sql/TimestampNanosFunctionsSuiteBase.scala | 43 +++++++++++++++++++
10 files changed, 326 insertions(+), 11 deletions(-)
diff --git a/docs/sql-migration-guide.md b/docs/sql-migration-guide.md
index 97a2041dfb66..144fd17d9394 100644
--- a/docs/sql-migration-guide.md
+++ b/docs/sql-migration-guide.md
@@ -33,6 +33,7 @@ license: |
- Since Spark 4.3, the exact `percentile`, `percentile_cont`, and `median`
aggregate functions (including their `WITHIN GROUP (ORDER BY ...)` forms)
compute the linear interpolation between two neighboring values as `lower +
fraction * (higher - lower)` instead of `(1 - fraction) * lower + fraction *
higher`. The two are equal in exact arithmetic, but the new form is
monotonically non-decreasing in the requested percentage and avoids a rounding
error the old form could introduce. As a re [...]
- Since Spark 4.3, non-deterministic filters (for example predicates involving
`rand()`) are no longer pushed down to DataSource V2 sources that implement
`SupportsPushDownV2Filters`; they are evaluated by Spark after the scan
instead. This prevents a source from evaluating such a predicate a different
number of times than Spark, or using it for pruning while also returning it for
post-scan re-evaluation.
- Since Spark 4.3, the new `COMMENT ON COLUMN ... IS NULL` syntax removes a
column comment by passing a `null` comment to
`TableChange.updateColumnComment(String[], String)`, so a `UpdateColumnComment`
table change may now carry a `null` `newComment()`. Previously `newComment()`
was always non-null. DataSource V2 catalogs that handle `UpdateColumnComment`
should null-check `newComment()` and treat `null` as "remove the column
comment" (mirroring how `UpdateColumnDefaultValue` already car [...]
+- Since Spark 4.3, `unix_seconds`, `unix_millis`, and `unix_micros` accept
`TIMESTAMP_NTZ` and the nanosecond-precision timestamp types directly, reading
them with no time-zone shift. Previously these functions accepted only
`TIMESTAMP_LTZ`; a `TIMESTAMP_NTZ` or nanosecond-timestamp argument was
rejected with a `DATATYPE_MISMATCH` error. This is a new capability and does
not change the result of any query that previously succeeded.
- Since Spark 4.3, `hash()` and `xxhash64()` include the `days` field of
`CalendarInterval` when computing the hash, so their output for interval values
differs from earlier releases. Previously the codegen path dropped `days`,
disagreeing with interpreted evaluation.
## Upgrading from Spark SQL 4.1 to 4.2
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
index 7d4d07f6e790..835f1cda947b 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
@@ -995,24 +995,35 @@ abstract class TimestampToLongBase extends UnaryExpression
protected def scaleFactor: Long
- override def inputTypes: Seq[AbstractDataType] = Seq(TimestampType)
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(TypeCollection(AnyTimestampType, AnyTimestampNanoType))
override def dataType: DataType = LongType
override def nullSafeEval(input: Any): Any = {
- Math.floorDiv(input.asInstanceOf[Number].longValue(), scaleFactor)
+ val micros = input match {
+ case v: TimestampNanosVal => v.epochMicros
+ case n: Number => n.longValue()
+ }
+ Math.floorDiv(micros, scaleFactor)
}
override protected def doGenCode(ctx: CodegenContext, ev: ExprCode):
ExprCode = {
+ // For the nanosecond carrier the child value is a TimestampNanosVal
object, so read its
+ // epochMicros field; for the microsecond timestamp types it is already a
primitive long.
+ val toMicros: String => String = child.dataType match {
+ case _: AnyTimestampNanoType => c => s"$c.epochMicros"
+ case _ => c => c
+ }
if (scaleFactor == 1) {
- defineCodeGen(ctx, ev, c => c)
+ defineCodeGen(ctx, ev, c => toMicros(c))
} else {
- defineCodeGen(ctx, ev, c => s"java.lang.Math.floorDiv($c,
${scaleFactor}L)")
+ defineCodeGen(ctx, ev, c => s"java.lang.Math.floorDiv(${toMicros(c)},
${scaleFactor}L)")
}
}
}
-// scalastyle:off line.size.limit
+// scalastyle:off line.size.limit line.contains.tab
@ExpressionDescription(
usage = "_FUNC_(timestamp) - Returns the number of seconds since 1970-01-01
00:00:00 UTC. Truncates higher levels of precision.",
arguments = """
@@ -1024,10 +1035,14 @@ abstract class TimestampToLongBase extends
UnaryExpression
Examples:
> SELECT _FUNC_(TIMESTAMP('1970-01-01 00:00:01Z'));
1
+ > SET spark.sql.timestampNanosTypes.enabled=true;
+ spark.sql.timestampNanosTypes.enabled true
+ > SELECT _FUNC_(TIMESTAMP_NTZ '2008-12-25 15:30:00.123456789');
+ 1230219000
""",
group = "datetime_funcs",
since = "3.1.0")
-// scalastyle:on line.size.limit
+// scalastyle:on line.size.limit line.contains.tab
case class UnixSeconds(child: Expression) extends TimestampToLongBase {
override def scaleFactor: Long = MICROS_PER_SECOND
@@ -1050,7 +1065,7 @@ case class CastTimestampNTZToLong(child: Expression)
extends TimestampToLongBase
copy(child = newChild)
}
-// scalastyle:off line.size.limit
+// scalastyle:off line.size.limit line.contains.tab
@ExpressionDescription(
usage = "_FUNC_(timestamp) - Returns the number of milliseconds since
1970-01-01 00:00:00 UTC. Truncates higher levels of precision.",
arguments = """
@@ -1062,10 +1077,14 @@ case class CastTimestampNTZToLong(child: Expression)
extends TimestampToLongBase
Examples:
> SELECT _FUNC_(TIMESTAMP('1970-01-01 00:00:01Z'));
1000
+ > SET spark.sql.timestampNanosTypes.enabled=true;
+ spark.sql.timestampNanosTypes.enabled true
+ > SELECT _FUNC_(TIMESTAMP_NTZ '2008-12-25 15:30:00.123456789');
+ 1230219000123
""",
group = "datetime_funcs",
since = "3.1.0")
-// scalastyle:on line.size.limit
+// scalastyle:on line.size.limit line.contains.tab
case class UnixMillis(child: Expression) extends TimestampToLongBase {
override def scaleFactor: Long = MICROS_PER_MILLIS
@@ -1075,9 +1094,9 @@ case class UnixMillis(child: Expression) extends
TimestampToLongBase {
copy(child = newChild)
}
-// scalastyle:off line.size.limit
+// scalastyle:off line.size.limit line.contains.tab
@ExpressionDescription(
- usage = "_FUNC_(timestamp) - Returns the number of microseconds since
1970-01-01 00:00:00 UTC.",
+ usage = "_FUNC_(timestamp) - Returns the number of microseconds since
1970-01-01 00:00:00 UTC. Truncates higher levels of precision.",
arguments = """
Arguments:
* timestamp - The timestamp to convert to microseconds since the epoch.
@@ -1087,10 +1106,14 @@ case class UnixMillis(child: Expression) extends
TimestampToLongBase {
Examples:
> SELECT _FUNC_(TIMESTAMP('1970-01-01 00:00:01Z'));
1000000
+ > SET spark.sql.timestampNanosTypes.enabled=true;
+ spark.sql.timestampNanosTypes.enabled true
+ > SELECT _FUNC_(TIMESTAMP_NTZ '2008-12-25 15:30:00.123456789');
+ 1230219000123456
""",
group = "datetime_funcs",
since = "3.1.0")
-// scalastyle:on line.size.limit
+// scalastyle:on line.size.limit line.contains.tab
case class UnixMicros(child: Expression) extends TimestampToLongBase {
override def scaleFactor: Long = 1L
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
index 69908974367d..226165b7b36f 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
@@ -1866,6 +1866,50 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
checkEvaluation(UnixMicros(Literal(timestampWithNanos)), 1000001L)
}
+ test("SPARK-57814: unix_seconds / unix_millis / unix_micros over " +
+ "NTZ and nanosecond-precision timestamps") {
+ import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils._
+
+ // 2008-12-25 15:30:00.123456789 -> epochMicros 1230219000123456 (+789 ns
remainder dropped).
+ // unix_* apply no zone shift, so the NTZ wall-clock value and the LTZ
instant at the same UTC
+ // reading produce identical results.
+ val ntz = localDateTimeToNanosVal(timestampNTZ(2008, 12, 25, 15, 30, 0,
123456789))
+ val ltz =
instantToNanosVal(Instant.parse("2008-12-25T15:30:00.123456789Z"))
+ foreachNanosPrecision { p =>
+ checkEvaluation(UnixSeconds(Literal.create(ntz,
TimestampNTZNanosType(p))), 1230219000L)
+ checkEvaluation(UnixSeconds(Literal.create(ltz,
TimestampLTZNanosType(p))), 1230219000L)
+ checkEvaluation(UnixMillis(Literal.create(ntz,
TimestampNTZNanosType(p))), 1230219000123L)
+ checkEvaluation(UnixMillis(Literal.create(ltz,
TimestampLTZNanosType(p))), 1230219000123L)
+ checkEvaluation(UnixMicros(Literal.create(ntz,
TimestampNTZNanosType(p))), 1230219000123456L)
+ checkEvaluation(UnixMicros(Literal.create(ltz,
TimestampLTZNanosType(p))), 1230219000123456L)
+ }
+
+ // Micro TIMESTAMP_NTZ is now accepted directly and read as a physical
Long with no zone shift.
+ // (Before this change it was rejected with DATATYPE_MISMATCH, since
TimestampToLongBase uses
+ // ExpectsInputTypes and only accepted TIMESTAMP_LTZ.) The direct reads
below are zoneless
+ // (session-zone-independent); explicitly casting the same NTZ value to
TIMESTAMP_LTZ under
+ // America/Los_Angeles shifts the wall clock by +8h to a different value.
The contrasting pair
+ // documents that reading NTZ directly is not the same as an explicit
TIMESTAMP_LTZ cast.
+ val microNtz =
+ Literal.create(LocalDateTime.parse("2008-12-25T15:30:00.123456"),
TimestampNTZType)
+ checkEvaluation(UnixSeconds(microNtz), 1230219000L)
+ checkEvaluation(UnixMicros(microNtz), 1230219000123456L)
+ checkEvaluation(UnixSeconds(Cast(microNtz, TimestampType,
Some(LA.getId))), 1230247800L)
+ checkEvaluation(UnixMicros(Cast(microNtz, TimestampType, Some(LA.getId))),
1230247800123456L)
+
+ // Pre-epoch sub-second value: 1969-12-31 23:59:59.5 has epochMicros
-500000; Math.floorDiv
+ // floors toward -inf, so unix_seconds -> -1 (matching micro-timestamp
behavior), NOT 0.
+ val preEpoch = localDateTimeToNanosVal(timestampNTZ(1969, 12, 31, 23, 59,
59, 500000000))
+ checkEvaluation(UnixSeconds(Literal.create(preEpoch,
TimestampNTZNanosType(9))), -1L)
+ checkEvaluation(UnixMillis(Literal.create(preEpoch,
TimestampNTZNanosType(9))), -500L)
+ checkEvaluation(UnixMicros(Literal.create(preEpoch,
TimestampNTZNanosType(9))), -500000L)
+
+ // NULL input over each unit and each nanos family.
+ checkEvaluation(UnixSeconds(Literal.create(null,
TimestampNTZNanosType(9))), null)
+ checkEvaluation(UnixMillis(Literal.create(null,
TimestampLTZNanosType(9))), null)
+ checkEvaluation(UnixMicros(Literal.create(null,
TimestampNTZNanosType(9))), null)
+ }
+
test("SPARK-57527: unix_nanos over nanosecond-precision timestamps") {
import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils._
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
index 3d38acc777e6..cdfb8a78bb22 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
@@ -1036,6 +1036,48 @@ Sort [v#x ASC NULLS FIRST], true
+- OneRowRelation
+-- !query
+SELECT unix_seconds(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query analysis
+Project [unix_seconds(2020-01-01 05:24:35.123456789) AS
unix_seconds(TIMESTAMP_LTZ '2020-01-01 05:24:35.123456789')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_millis(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query analysis
+Project [unix_millis(2020-01-01 05:24:35.123456789) AS
unix_millis(TIMESTAMP_LTZ '2020-01-01 05:24:35.123456789')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_micros(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query analysis
+Project [unix_micros(2020-01-01 05:24:35.123456789) AS
unix_micros(TIMESTAMP_LTZ '2020-01-01 05:24:35.123456789')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_micros('2020-01-01 13:24:35.999999999 UTC' :: timestamp_ltz(7))
+-- !query analysis
+Project [unix_micros(cast(2020-01-01 13:24:35.999999999 UTC as
timestamp_ltz(7))) AS unix_micros(CAST(2020-01-01 13:24:35.999999999 UTC AS
TIMESTAMP_LTZ(7)))#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_seconds(TIMESTAMP_LTZ '1969-12-31 23:59:59.500000000 UTC')
+-- !query analysis
+Project [unix_seconds(1969-12-31 15:59:59.5) AS unix_seconds(TIMESTAMP_LTZ
'1969-12-31 15:59:59.500000000')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_seconds(NULL :: timestamp_ltz(9)), unix_millis(NULL ::
timestamp_ltz(9)), unix_micros(NULL :: timestamp_ltz(9))
+-- !query analysis
+Project [unix_seconds(cast(null as timestamp_ltz(9))) AS
unix_seconds(CAST(NULL AS TIMESTAMP_LTZ(9)))#xL, unix_millis(cast(null as
timestamp_ltz(9))) AS unix_millis(CAST(NULL AS TIMESTAMP_LTZ(9)))#xL,
unix_micros(cast(null as timestamp_ltz(9))) AS unix_micros(CAST(NULL AS
TIMESTAMP_LTZ(9)))#xL]
++- OneRowRelation
+
+
-- !query
SELECT date_format(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789', 'yyyy-MM-dd
HH:mm:ss.SSSSSSSSS')
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
index ff49a5870e29..7b03461ea2ac 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
@@ -870,6 +870,48 @@ Sort [v#x ASC NULLS FIRST], true
+- OneRowRelation
+-- !query
+SELECT unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [unix_seconds(2020-01-01 13:24:35.123456789) AS
unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_millis(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [unix_millis(2020-01-01 13:24:35.123456789) AS
unix_millis(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_micros(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [unix_micros(2020-01-01 13:24:35.123456789) AS
unix_micros(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_micros('2020-01-01 13:24:35.999999999' :: timestamp_ntz(7))
+-- !query analysis
+Project [unix_micros(cast(2020-01-01 13:24:35.999999999 as timestamp_ntz(7)))
AS unix_micros(CAST(2020-01-01 13:24:35.999999999 AS TIMESTAMP_NTZ(7)))#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_seconds(TIMESTAMP_NTZ '1969-12-31 23:59:59.500000000')
+-- !query analysis
+Project [unix_seconds(1969-12-31 23:59:59.5) AS unix_seconds(TIMESTAMP_NTZ
'1969-12-31 23:59:59.500000000')#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT unix_seconds(NULL :: timestamp_ntz(9)), unix_millis(NULL ::
timestamp_ntz(9)), unix_micros(NULL :: timestamp_ntz(9))
+-- !query analysis
+Project [unix_seconds(cast(null as timestamp_ntz(9))) AS
unix_seconds(CAST(NULL AS TIMESTAMP_NTZ(9)))#xL, unix_millis(cast(null as
timestamp_ntz(9))) AS unix_millis(CAST(NULL AS TIMESTAMP_NTZ(9)))#xL,
unix_micros(cast(null as timestamp_ntz(9))) AS unix_micros(CAST(NULL AS
TIMESTAMP_NTZ(9)))#xL]
++- OneRowRelation
+
+
-- !query
SELECT date_format(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789', 'yyyy-MM-dd
HH:mm:ss.SSSSSSSSS')
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
index 427f89c96666..62e9b8eb93b5 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
@@ -329,6 +329,18 @@ SELECT v, lead(v) OVER (ORDER BY v) AS next_v FROM (
UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000100'
UNION ALL SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.000000500') ORDER BY v;
+-- SPARK-57814: unix_seconds / unix_millis / unix_micros over
nanosecond-precision values. The result
+-- is a whole BIGINT count of the unit; sub-unit digits are dropped.
Explicit-zone literals fix the
+-- instant directly, independent of the session time zone.
+SELECT unix_seconds(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC');
+SELECT unix_millis(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC');
+SELECT unix_micros(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC');
+SELECT unix_micros('2020-01-01 13:24:35.999999999 UTC' :: timestamp_ltz(7));
+-- Pre-epoch value: floorDiv floors toward -inf, so unix_seconds -> -1 (not 0).
+SELECT unix_seconds(TIMESTAMP_LTZ '1969-12-31 23:59:59.500000000 UTC');
+-- NULL nanosecond timestamp.
+SELECT unix_seconds(NULL :: timestamp_ltz(9)), unix_millis(NULL ::
timestamp_ltz(9)), unix_micros(NULL :: timestamp_ltz(9));
+
-- SPARK-57816: date_format / to_char / to_varchar over nanosecond-precision
values. The pattern's
-- fractional-second placeholders render up to nanosecond digits; a 9-`S`
field is fixed width, so
-- digits below the type's precision floor to zeros rather than being dropped.
LTZ renders in the
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
index 384acca073be..971a2f8e148f 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
@@ -269,6 +269,18 @@ SELECT v, lead(v) OVER (ORDER BY v) AS next_v FROM (
UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000100'
UNION ALL SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.000000500') ORDER BY v;
+-- SPARK-57814: unix_seconds / unix_millis / unix_micros over
nanosecond-precision values. The result
+-- is a whole BIGINT count of the unit; sub-unit digits (incl. the
sub-microsecond remainder) are
+-- dropped and NTZ applies no zone shift, so the wall-clock value is read as
the epoch instant.
+SELECT unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT unix_millis(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT unix_micros(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT unix_micros('2020-01-01 13:24:35.999999999' :: timestamp_ntz(7));
+-- Pre-epoch value: floorDiv floors toward -inf, so unix_seconds -> -1 (not 0).
+SELECT unix_seconds(TIMESTAMP_NTZ '1969-12-31 23:59:59.500000000');
+-- NULL nanosecond timestamp.
+SELECT unix_seconds(NULL :: timestamp_ntz(9)), unix_millis(NULL ::
timestamp_ntz(9)), unix_micros(NULL :: timestamp_ntz(9));
+
-- SPARK-57816: date_format / to_char / to_varchar over nanosecond-precision
values. The pattern's
-- fractional-second placeholders render up to nanosecond digits; a 9-`S`
field is fixed width, so
-- digits below the type's precision floor to zeros rather than being dropped.
NTZ renders its
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
index f1f3b7f61022..388ce96f7396 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
@@ -1127,6 +1127,54 @@ struct<v:timestamp_ltz(9),next_v:timestamp_ltz(9)>
2020-01-01 00:00:00.0000009 NULL
+-- !query
+SELECT unix_seconds(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query schema
+struct<unix_seconds(TIMESTAMP_LTZ '2020-01-01 05:24:35.123456789'):bigint>
+-- !query output
+1577885075
+
+
+-- !query
+SELECT unix_millis(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query schema
+struct<unix_millis(TIMESTAMP_LTZ '2020-01-01 05:24:35.123456789'):bigint>
+-- !query output
+1577885075123
+
+
+-- !query
+SELECT unix_micros(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query schema
+struct<unix_micros(TIMESTAMP_LTZ '2020-01-01 05:24:35.123456789'):bigint>
+-- !query output
+1577885075123456
+
+
+-- !query
+SELECT unix_micros('2020-01-01 13:24:35.999999999 UTC' :: timestamp_ltz(7))
+-- !query schema
+struct<unix_micros(CAST(2020-01-01 13:24:35.999999999 UTC AS
TIMESTAMP_LTZ(7))):bigint>
+-- !query output
+1577885075999999
+
+
+-- !query
+SELECT unix_seconds(TIMESTAMP_LTZ '1969-12-31 23:59:59.500000000 UTC')
+-- !query schema
+struct<unix_seconds(TIMESTAMP_LTZ '1969-12-31 15:59:59.500000000'):bigint>
+-- !query output
+-1
+
+
+-- !query
+SELECT unix_seconds(NULL :: timestamp_ltz(9)), unix_millis(NULL ::
timestamp_ltz(9)), unix_micros(NULL :: timestamp_ltz(9))
+-- !query schema
+struct<unix_seconds(CAST(NULL AS
TIMESTAMP_LTZ(9))):bigint,unix_millis(CAST(NULL AS
TIMESTAMP_LTZ(9))):bigint,unix_micros(CAST(NULL AS TIMESTAMP_LTZ(9))):bigint>
+-- !query output
+NULL NULL NULL
+
+
-- !query
SELECT date_format(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789', 'yyyy-MM-dd
HH:mm:ss.SSSSSSSSS')
-- !query schema
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
index dbb3a77295ee..821aad995bc6 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
@@ -924,6 +924,54 @@ struct<v:timestamp_ntz(9),next_v:timestamp_ntz(9)>
2020-01-01 00:00:00.0000009 NULL
+-- !query
+SELECT unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<unix_seconds(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789'):bigint>
+-- !query output
+1577885075
+
+
+-- !query
+SELECT unix_millis(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<unix_millis(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789'):bigint>
+-- !query output
+1577885075123
+
+
+-- !query
+SELECT unix_micros(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<unix_micros(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789'):bigint>
+-- !query output
+1577885075123456
+
+
+-- !query
+SELECT unix_micros('2020-01-01 13:24:35.999999999' :: timestamp_ntz(7))
+-- !query schema
+struct<unix_micros(CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_NTZ(7))):bigint>
+-- !query output
+1577885075999999
+
+
+-- !query
+SELECT unix_seconds(TIMESTAMP_NTZ '1969-12-31 23:59:59.500000000')
+-- !query schema
+struct<unix_seconds(TIMESTAMP_NTZ '1969-12-31 23:59:59.500000000'):bigint>
+-- !query output
+-1
+
+
+-- !query
+SELECT unix_seconds(NULL :: timestamp_ntz(9)), unix_millis(NULL ::
timestamp_ntz(9)), unix_micros(NULL :: timestamp_ntz(9))
+-- !query schema
+struct<unix_seconds(CAST(NULL AS
TIMESTAMP_NTZ(9))):bigint,unix_millis(CAST(NULL AS
TIMESTAMP_NTZ(9))):bigint,unix_micros(CAST(NULL AS TIMESTAMP_NTZ(9))):bigint>
+-- !query output
+NULL NULL NULL
+
+
-- !query
SELECT date_format(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789', 'yyyy-MM-dd
HH:mm:ss.SSSSSSSSS')
-- !query schema
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
index 14e346753d8e..adfeb902cc03 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
@@ -450,6 +450,49 @@ abstract class TimestampNanosFunctionsSuiteBase extends
SharedSparkSession {
}
}
+ test("SPARK-57814: unix_seconds / unix_millis / unix_micros over nanosecond
timestamps") {
+ // unix_* apply no zone shift; sub-microsecond digits are dropped. The
nanos result equals the
+ // microsecond-timestamp result for the same instant, and is the same for
every precision p.
+ val ntzStr = "2020-01-01T13:24:35.123456789"
+ val ltzStr = "2020-01-01T21:24:35.987654321Z"
+ // NTZ 13:24:35.123456 -> 1577885075123456 micros; LTZ 21:24:35.987654 UTC
-> 1577913875987654.
+ val ntzExpected = Row(1577885075L, 1577885075123L, 1577885075123456L)
+ val ltzExpected = Row(1577913875L, 1577913875987L, 1577913875987654L)
+ Seq(7, 8, 9).foreach { p =>
+ val ntz = ntzNanos(ntzStr, p)
+ checkAnswer(
+ ntz.select(unix_seconds(col("c")), unix_millis(col("c")),
unix_micros(col("c"))),
+ ntzExpected)
+ checkAnswer(
+ ntz.selectExpr("unix_seconds(c)", "unix_millis(c)", "unix_micros(c)"),
+ ntzExpected)
+ val ltz = ltzNanos(ltzStr, p)
+ checkAnswer(
+ ltz.select(unix_seconds(col("c")), unix_millis(col("c")),
unix_micros(col("c"))),
+ ltzExpected)
+ checkAnswer(
+ ltz.selectExpr("unix_seconds(c)", "unix_millis(c)", "unix_micros(c)"),
+ ltzExpected)
+ }
+ }
+
+ test("SPARK-57814: unix_seconds / unix_millis / unix_micros over NULL
nanosecond timestamps") {
+ Seq(7, 8, 9).foreach { p =>
+ val ntz = spark.createDataFrame(
+ spark.sparkContext.parallelize(Seq(Row(null))),
+ new StructType().add("c", TimestampNTZNanosType(p)))
+ val ltz = spark.createDataFrame(
+ spark.sparkContext.parallelize(Seq(Row(null))),
+ new StructType().add("c", TimestampLTZNanosType(p)))
+ checkAnswer(
+ ntz.select(unix_seconds(col("c")), unix_millis(col("c")),
unix_micros(col("c"))),
+ Row(null, null, null))
+ checkAnswer(
+ ltz.select(unix_seconds(col("c")), unix_millis(col("c")),
unix_micros(col("c"))),
+ Row(null, null, null))
+ }
+ }
+
// ===== max_by / min_by over nanosecond-precision timestamps (SPARK-56822)
=====
// `MaxBy`/`MinBy` gate only on the ordering expression's orderability
// (`MaxMinBy.checkInputDataTypes` -> `TypeUtils.checkForOrderingExpr`),
which the nanosecond
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
