This is an automated email from the ASF dual-hosted git repository.
MaxGekk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 8885867c9be2 [SPARK-57340][SQL] Support EXTRACT/date_part HOUR, MINUTE
and SECOND over nanosecond-precision timestamps
8885867c9be2 is described below
commit 8885867c9be23d66ec0087186d7eb7faafd2d561
Author: Stevo Mitric <[email protected]>
AuthorDate: Sat Jun 13 14:19:25 2026 +0200
[SPARK-57340][SQL] Support EXTRACT/date_part HOUR, MINUTE and SECOND over
nanosecond-precision timestamps
### What changes were proposed in this pull request?
Support `EXTRACT(field FROM source)` and `date_part(field, source)` over
the nanosecond-precision timestamp types `TIMESTAMP_NTZ(p)` /
`TIMESTAMP_LTZ(p)`. Follow-up to SPARK-57315, which covered `hour()` /
`minute()` / `second()` but not `EXTRACT` / `date_part` (they bypass the
function builders via `DatePart.parseExtractField`).
- **HOUR / MINUTE**: cast the nanosecond input down to the matching
microsecond type via the existing `NanosTimestampCast.castToMicros` (lossless
for integer fields).
- **SECOND**: new `SecondWithFractionNanos` expression returning
`DECIMAL(11, 9)` with the full nanosecond fraction, instead of truncating to
the `DECIMAL(8, 6)` of `SecondWithFraction`. Backed by a new
`DateTimeUtils.getSecondsWithFractionNanos`.
Date-based fields (`YEAR`, `MONTH`, ...) are out of scope
### Why are the changes needed?
`EXTRACT` / `date_part` are documented as equivalent to `hour()` /
`minute()` / `second()`, but after SPARK-57315 they still fail analysis on
nanosecond timestamps. Part of the nanosecond timestamp preview (SPARK-56822).
### Does this PR introduce _any_ user-facing change?
Yes, but only with the preview flag `spark.sql.timestampNanosTypes.enabled`
set to `true`
```sql
SELECT extract(HOUR FROM TIMESTAMP_NTZ '2018-02-14 12:58:59.123456789');
-- 12 (previously: analysis error)
SELECT extract(SECOND FROM TIMESTAMP_NTZ '2018-02-14 12:58:59.123456789');
-- 59.123456789 (DECIMAL(11, 9))
```
### How was this patch tested?
Tests in this PR.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code (Claude Fable 5)
Closes #56466 from stevomitric/stevomitric/extract.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
.../catalyst/expressions/datetimeExpressions.scala | 82 ++++++++++++++++--
.../spark/sql/catalyst/util/DateTimeUtils.scala | 15 +++-
.../expressions/DateExpressionsSuite.scala | 29 +++++++
.../analyzer-results/timestamp-ltz-nanos.sql.out | 84 +++++++++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 70 ++++++++++++++++
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 24 ++++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 19 +++++
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 96 ++++++++++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 80 ++++++++++++++++++
.../sql/TimestampNanosFunctionsSuiteBase.scala | 48 +++++++++++
10 files changed, 540 insertions(+), 7 deletions(-)
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 e7867b0ac6b4..922d45c4654c 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
@@ -43,7 +43,7 @@ import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.internal.types.StringTypeWithCollation
import org.apache.spark.sql.types._
import org.apache.spark.sql.types.DayTimeIntervalType.DAY
-import org.apache.spark.unsafe.types.{CalendarInterval, UTF8String}
+import org.apache.spark.unsafe.types.{CalendarInterval, TimestampNanosVal,
UTF8String}
/**
* Common base class for time zone aware expressions.
@@ -68,7 +68,8 @@ trait TimeZoneAwareExpression extends Expression {
@transient lazy val zoneId: ZoneId = DateTimeUtils.getZoneId(timeZoneId.get)
def zoneIdForType(dataType: DataType): ZoneId = dataType match {
- case _: TimestampNTZType => java.time.ZoneOffset.UTC
+ // The TIMESTAMP_NTZ family carries wall-clock fields, so it is evaluated
in UTC.
+ case _: TimestampNTZType | _: TimestampNTZNanosType =>
java.time.ZoneOffset.UTC
case _ => zoneId
}
}
@@ -468,6 +469,62 @@ case class SecondWithFraction(child: Expression,
timeZoneId: Option[String] = No
copy(child = newChild)
}
+/**
+ * Returns the second component with the fractional part of a
nanosecond-precision timestamp
+ * (`TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)`, `p` in `[7, 9]`) as `DECIMAL(11,
9)`.
+ *
+ * This is the nanosecond counterpart of [[SecondWithFraction]] on the
+ * `EXTRACT(SECOND FROM ...)` / `date_part('SECOND', ...)` path. Unlike the
integer time-of-day
+ * fields, the result depends on the sub-microsecond digits, so the input is
not cast down to
+ * microseconds. The scale is fixed at the maximum nanosecond precision, like
+ * [[SecondsOfTimeWithFraction]] does for TIME values: digits below the type's
precision `p` are
+ * always zero because values are floored to `p` at the type boundary.
+ */
+case class SecondWithFractionNanos(child: Expression, timeZoneId:
Option[String] = None)
+ extends UnaryExpression with TimeZoneAwareExpression {
+
+ def this(child: Expression) = this(child, None)
+
+ override def nullIntolerant: Boolean = true
+
+ // 2 digits for the seconds, and 9 digits for the fractional part with
nanosecond precision.
+ override def dataType: DataType = DecimalType(11, 9)
+
+ override def checkInputDataTypes(): TypeCheckResult = child.dataType match {
+ case _: TimestampNTZNanosType | _: TimestampLTZNanosType =>
TypeCheckSuccess
+ case _ =>
+ DataTypeMismatch(
+ errorSubClass = "UNEXPECTED_INPUT_TYPE",
+ messageParameters = Map(
+ "paramIndex" -> ordinalNumber(0),
+ "requiredType" ->
+ Seq(TimestampNTZNanosType(),
TimestampLTZNanosType()).map(toSQLType).mkString(" or "),
+ "inputSql" -> toSQLExpr(child),
+ "inputType" -> toSQLType(child.dataType)))
+ }
+
+ override def withTimeZone(timeZoneId: String): SecondWithFractionNanos =
+ copy(timeZoneId = Option(timeZoneId))
+
+ // TIMESTAMP_NTZ(p) extracts the wall-clock fields, so it is evaluated in
UTC;
+ // TIMESTAMP_LTZ(p) uses the session time zone.
+ @transient private lazy val zoneIdInEval: ZoneId =
zoneIdForType(child.dataType)
+
+ override protected def nullSafeEval(timestamp: Any): Any = {
+ DateTimeUtils.getSecondsWithFractionNanos(
+ timestamp.asInstanceOf[TimestampNanosVal], zoneIdInEval)
+ }
+
+ override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
+ val zid = ctx.addReferenceObj("zoneId", zoneIdInEval,
classOf[ZoneId].getName)
+ val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
+ defineCodeGen(ctx, ev, c => s"$dtu.getSecondsWithFractionNanos($c, $zid)")
+ }
+
+ override protected def withNewChildInternal(newChild: Expression):
SecondWithFractionNanos =
+ copy(child = newChild)
+}
+
trait GetDateField extends UnaryExpression with ImplicitCastInputTypes {
override def nullIntolerant: Boolean = true
val func: Int => Any
@@ -3341,9 +3398,22 @@ object DatePart {
case "DAYOFWEEK" | "DOW" => DayOfWeek(source)
case "DAYOFWEEK_ISO" | "DOW_ISO" => Add(WeekDay(source), Literal(1))
case "DOY" => DayOfYear(source)
- case "HOUR" | "H" | "HOURS" | "HR" | "HRS" => Hour(source)
- case "MINUTE" | "M" | "MIN" | "MINS" | "MINUTES" => Minute(source)
- case "SECOND" | "S" | "SEC" | "SECONDS" | "SECS" =>
SecondWithFraction(source)
+ case "HOUR" | "H" | "HOURS" | "HR" | "HRS" =>
+ // Nanosecond-precision timestamps are cast down to the matching
microsecond timestamp
+ // type, which is lossless for the integer time-of-day fields
(SPARK-57340); other types
+ // are passed through unchanged.
+ Hour(NanosTimestampCast.castToMicros(source))
+ case "MINUTE" | "M" | "MIN" | "MINS" | "MINUTES" =>
+ Minute(NanosTimestampCast.castToMicros(source))
+ case "SECOND" | "S" | "SEC" | "SECONDS" | "SECS" =>
+ source.dataType match {
+ // EXTRACT(SECOND) exposes the fractional digits, so
nanosecond-precision timestamps
+ // keep their sub-microsecond digits and widen the result to
DECIMAL(11, 9) instead of
+ // casting down to microseconds (SPARK-57340).
+ case _: TimestampNTZNanosType | _: TimestampLTZNanosType =>
+ SecondWithFractionNanos(source)
+ case _ => SecondWithFraction(source)
+ }
case _ =>
throw
QueryCompilationErrors.literalTypeUnsupportedForSourceTypeError(extractField,
source)
}
@@ -3413,7 +3483,7 @@ object DatePartExpressionBuilder extends
ExpressionBuilder {
- "DOY" - the day of the year (1 - 365/366)
- "HOUR", ("H", "HOURS", "HR", "HRS") - The hour field (0 - 23)
- "MINUTE", ("M", "MIN", "MINS", "MINUTES") - the minutes field
(0 - 59)
- - "SECOND", ("S", "SEC", "SECONDS", "SECS") - the seconds field,
including fractional parts
+ - "SECOND", ("S", "SEC", "SECONDS", "SECS") - the seconds field,
including fractional parts. Returns a DECIMAL(8, 6) value, or a DECIMAL(11, 9)
value for the nanosecond-precision timestamps TIMESTAMP_NTZ(p) /
TIMESTAMP_LTZ(p) with p in [7, 9]
- Supported string values of `field` for interval(which consists of
`months`, `days`, `microseconds`) are(case insensitive):
- "YEAR", ("Y", "YEARS", "YR", "YRS") - the total `months` / 12
- "MONTH", ("MON", "MONS", "MONTHS") - the total `months` % 12
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
index c5e6b5cbce93..38263a7ebc1c 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
@@ -29,7 +29,7 @@ import org.apache.spark.{QueryContext, SparkException,
SparkIllegalArgumentExcep
import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.errors.QueryExecutionErrors
import org.apache.spark.sql.types.{Decimal, DoubleExactNumeric,
TimestampNTZType, TimestampType}
-import org.apache.spark.unsafe.types.{CalendarInterval, UTF8String}
+import org.apache.spark.unsafe.types.{CalendarInterval, TimestampNanosVal,
UTF8String}
/**
* Helper functions for converting between internal and external date and time
representations.
@@ -148,6 +148,19 @@ object DateTimeUtils extends SparkDateTimeUtils {
Decimal(getMicroseconds(micros, zoneId), 8, 6)
}
+ /**
+ * Returns the seconds part and its fractional part with nanoseconds, of a
nanosecond-precision
+ * timestamp. The integral seconds and the six microsecond fraction digits
come from the
+ * wall-clock fields of `epochMicros` in the given zone; `nanosWithinMicro`
contributes the
+ * three sub-microsecond digits. The scale is fixed at the maximum
nanosecond precision (9):
+ * digits below the value's type precision are always zero because values
are floored to the
+ * type precision at the type boundary.
+ */
+ def getSecondsWithFractionNanos(ts: TimestampNanosVal, zoneId: ZoneId):
Decimal = {
+ val nanosOfMinute =
+ getMicroseconds(ts.epochMicros, zoneId) * NANOS_PER_MICROS +
ts.nanosWithinMicro
+ Decimal(nanosOfMinute, 11, 9)
+ }
/**
* Returns the second value with fraction from a given TIME (TimeType) value.
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 4a2b23fe059b..2ffc30b3d7c4 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
@@ -1272,6 +1272,35 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
}
}
+ test("SPARK-57340: extract the seconds part with fraction from nanosecond
timestamps") {
+ import org.apache.spark.sql.catalyst.util.TimestampNanosTestUtils._
+ // NTZ extracts the wall-clock fields and is zone-independent: the
expression evaluates in
+ // UTC regardless of the session time zone.
+ val ntzValue = localDateTimeToNanosVal(timestampNTZ(2020, 1, 1, 13, 24,
35, 123456789))
+ outstandingTimezonesIds.foreach { timezone =>
+ checkEvaluation(
+ SecondWithFractionNanos(
+ Literal.create(ntzValue, TimestampNTZNanosType(9)), Some(timezone)),
+ Decimal(35123456789L, 11, 9))
+ }
+ // LTZ extracts in the session time zone. The second field (including the
nanosecond
+ // fraction) is invariant under whole-minute zone offsets, such as
Asia/Kolkata (+05:30).
+ val ltzValue =
instantToNanosVal(Instant.parse("2020-01-01T21:24:35.987654321Z"))
+ def ltzExpr(timezone: String): SecondWithFractionNanos =
+ SecondWithFractionNanos(Literal.create(ltzValue,
TimestampLTZNanosType(9)), Some(timezone))
+ checkEvaluation(ltzExpr("America/Los_Angeles"), Decimal(35987654321L, 11,
9))
+ checkEvaluation(ltzExpr("Asia/Kolkata"), Decimal(35987654321L, 11, 9))
+ // Pre-epoch values exercise the negative-epoch path; the wall-clock
fields stay positive.
+ val preEpoch = localDateTimeToNanosVal(timestampNTZ(1960, 1, 1, 5, 6, 7,
123456789))
+ checkEvaluation(
+ SecondWithFractionNanos(Literal.create(preEpoch,
TimestampNTZNanosType(9)), Some("UTC")),
+ Decimal(7123456789L, 11, 9))
+ // NULL input.
+ checkEvaluation(
+ SecondWithFractionNanos(Literal.create(null, TimestampNTZNanosType(9)),
Some("UTC")),
+ null)
+ }
+
test("SPARK-34903: timestamps difference") {
val end = Instant.parse("2019-10-04T11:04:01.123456Z")
outstandingTimezonesIds.foreach { tz =>
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 fa8c562c9416..bbc249f3deb2 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
@@ -221,3 +221,87 @@ SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
UTC')
-- !query analysis
Project [second(cast(TimestampNanosVal(1577885075123456, 789) as timestamp),
Some(America/Los_Angeles)) AS second(TimestampNanosVal(1577885075123456,
789))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT extract(HOUR FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(HOUR, TimestampNanosVal(1577913875123456, 789)) AS
extract(HOUR FROM TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(MINUTE, TimestampNanosVal(1577913875123456, 789)) AS
extract(MINUTE FROM TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(SECOND, TimestampNanosVal(1577913875123456, 789)) AS
extract(SECOND FROM TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_part('HOUR', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [date_part(HOUR, TimestampNanosVal(1577913875123456, 789)) AS
date_part(HOUR, TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_part('MINUTE', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [date_part(MINUTE, TimestampNanosVal(1577913875123456, 789)) AS
date_part(MINUTE, TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_part('SECOND', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [date_part(SECOND, TimestampNanosVal(1577913875123456, 789)) AS
date_part(SECOND, TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ltz(7))
+-- !query analysis
+Project [extract(SECOND, cast(2020-01-01 13:24:35.999999999 as
timestamp_ltz(7))) AS extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_LTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ltz(8))
+-- !query analysis
+Project [extract(SECOND, cast(2020-01-01 13:24:35.999999999 as
timestamp_ltz(8))) AS extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_LTZ(8)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM NULL :: timestamp_ltz(9))
+-- !query analysis
+Project [extract(SECOND, cast(null as timestamp_ltz(9))) AS extract(SECOND
FROM CAST(NULL AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(SECOND, TimestampNanosVal(-315542124876544, 789)) AS
extract(SECOND FROM TimestampNanosVal(-315542124876544, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata')
+-- !query analysis
+Project [extract(MINUTE, TimestampNanosVal(1577865275123456, 789)) AS
extract(MINUTE FROM TimestampNanosVal(1577865275123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata')
+-- !query analysis
+Project [extract(SECOND, TimestampNanosVal(1577865275123456, 789)) AS
extract(SECOND FROM TimestampNanosVal(1577865275123456, 789))#x]
++- OneRowRelation
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 97bcd3b290a0..85192002d55e 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
@@ -179,3 +179,73 @@ SELECT second(TIMESTAMP_NTZ '1960-01-01
13:24:35.123456789')
-- !query analysis
Project [second(cast(TimestampNanosVal(-315570924876544, 789) as
timestamp_ntz), Some(America/Los_Angeles)) AS
second(TimestampNanosVal(-315570924876544, 789))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT extract(HOUR FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(HOUR, TimestampNanosVal(1577885075123456, 789)) AS
extract(HOUR FROM TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(MINUTE FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(MINUTE, TimestampNanosVal(1577885075123456, 789)) AS
extract(MINUTE FROM TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(SECOND, TimestampNanosVal(1577885075123456, 789)) AS
extract(SECOND FROM TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_part('HOUR', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [date_part(HOUR, TimestampNanosVal(1577885075123456, 789)) AS
date_part(HOUR, TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_part('MINUTE', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [date_part(MINUTE, TimestampNanosVal(1577885075123456, 789)) AS
date_part(MINUTE, TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT date_part('SECOND', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [date_part(SECOND, TimestampNanosVal(1577885075123456, 789)) AS
date_part(SECOND, TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ntz(7))
+-- !query analysis
+Project [extract(SECOND, cast(2020-01-01 13:24:35.999999999 as
timestamp_ntz(7))) AS extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_NTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ntz(8))
+-- !query analysis
+Project [extract(SECOND, cast(2020-01-01 13:24:35.999999999 as
timestamp_ntz(8))) AS extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_NTZ(8)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM NULL :: timestamp_ntz(9))
+-- !query analysis
+Project [extract(SECOND, cast(null as timestamp_ntz(9))) AS extract(SECOND
FROM CAST(NULL AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [extract(SECOND, TimestampNanosVal(-315570924876544, 789)) AS
extract(SECOND FROM TimestampNanosVal(-315570924876544, 789))#x]
++- OneRowRelation
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 b318ad3021bc..37fef0416b8a 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
@@ -55,3 +55,27 @@ SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata');
SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC');
SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC');
SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC');
+
+-- EXTRACT / date_part over nanosecond-precision values (SPARK-57340). HOUR
and MINUTE are
+-- equivalent to the hour()/minute() functions; SECOND keeps the
sub-microsecond digits and
+-- widens the result to DECIMAL(11, 9). LTZ extracts in the session time zone.
+SELECT extract(HOUR FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT date_part('HOUR', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT date_part('MINUTE', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT date_part('SECOND', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+
+-- Digits below the type's precision are floored at the type boundary, so they
read back as
+-- zeros in the DECIMAL(11, 9) result.
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' ::
timestamp_ltz(7));
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' ::
timestamp_ltz(8));
+SELECT extract(SECOND FROM NULL :: timestamp_ltz(9));
+
+-- Pre-epoch nanosecond values exercise the negative-epoch path.
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789');
+
+-- A source zone with a sub-hour offset (Asia/Kolkata is UTC+05:30) shifts the
minute field,
+-- while the second field (including the nanosecond fraction) is
offset-invariant.
+SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata');
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata');
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 0ba10ff1ff06..e95530669277 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
@@ -46,3 +46,22 @@ SELECT hour(NULL :: timestamp_ntz(9));
SELECT hour(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789');
SELECT minute(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789');
SELECT second(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789');
+
+-- EXTRACT / date_part over nanosecond-precision values (SPARK-57340). HOUR
and MINUTE are
+-- equivalent to the hour()/minute() functions; SECOND keeps the
sub-microsecond digits and
+-- widens the result to DECIMAL(11, 9).
+SELECT extract(HOUR FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT extract(MINUTE FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT extract(SECOND FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT date_part('HOUR', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT date_part('MINUTE', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT date_part('SECOND', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+
+-- Digits below the type's precision are floored at the type boundary, so they
read back as
+-- zeros in the DECIMAL(11, 9) result.
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' ::
timestamp_ntz(7));
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' ::
timestamp_ntz(8));
+SELECT extract(SECOND FROM NULL :: timestamp_ntz(9));
+
+-- Pre-epoch nanosecond values exercise the negative-epoch path.
+SELECT extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789');
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 aa0d6cc4c187..86e4459072fb 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
@@ -253,3 +253,99 @@ SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
UTC')
struct<second(TimestampNanosVal(1577885075123456, 789)):int>
-- !query output
35
+
+
+-- !query
+SELECT extract(HOUR FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(HOUR FROM TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(MINUTE FROM TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(SECOND FROM TimestampNanosVal(1577913875123456,
789)):decimal(11,9)>
+-- !query output
+35.123456789
+
+
+-- !query
+SELECT date_part('HOUR', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<date_part(HOUR, TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT date_part('MINUTE', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<date_part(MINUTE, TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT date_part('SECOND', TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<date_part(SECOND, TimestampNanosVal(1577913875123456,
789)):decimal(11,9)>
+-- !query output
+35.123456789
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ltz(7))
+-- !query schema
+struct<extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_LTZ(7))):decimal(11,9)>
+-- !query output
+35.999999900
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ltz(8))
+-- !query schema
+struct<extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_LTZ(8))):decimal(11,9)>
+-- !query output
+35.999999990
+
+
+-- !query
+SELECT extract(SECOND FROM NULL :: timestamp_ltz(9))
+-- !query schema
+struct<extract(SECOND FROM CAST(NULL AS TIMESTAMP_LTZ(9))):decimal(11,9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(SECOND FROM TimestampNanosVal(-315542124876544,
789)):decimal(11,9)>
+-- !query output
+35.123456789
+
+
+-- !query
+SELECT extract(MINUTE FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata')
+-- !query schema
+struct<extract(MINUTE FROM TimestampNanosVal(1577865275123456, 789)):int>
+-- !query output
+54
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789
Asia/Kolkata')
+-- !query schema
+struct<extract(SECOND FROM TimestampNanosVal(1577865275123456,
789)):decimal(11,9)>
+-- !query output
+35.123456789
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 25bb7a9322a8..c1aa8b2c2ffb 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
@@ -205,3 +205,83 @@ SELECT second(TIMESTAMP_NTZ '1960-01-01
13:24:35.123456789')
struct<second(TimestampNanosVal(-315570924876544, 789)):int>
-- !query output
35
+
+
+-- !query
+SELECT extract(HOUR FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(HOUR FROM TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT extract(MINUTE FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(MINUTE FROM TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(SECOND FROM TimestampNanosVal(1577885075123456,
789)):decimal(11,9)>
+-- !query output
+35.123456789
+
+
+-- !query
+SELECT date_part('HOUR', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<date_part(HOUR, TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT date_part('MINUTE', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<date_part(MINUTE, TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT date_part('SECOND', TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<date_part(SECOND, TimestampNanosVal(1577885075123456,
789)):decimal(11,9)>
+-- !query output
+35.123456789
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ntz(7))
+-- !query schema
+struct<extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_NTZ(7))):decimal(11,9)>
+-- !query output
+35.999999900
+
+
+-- !query
+SELECT extract(SECOND FROM '2020-01-01 13:24:35.999999999' :: timestamp_ntz(8))
+-- !query schema
+struct<extract(SECOND FROM CAST(2020-01-01 13:24:35.999999999 AS
TIMESTAMP_NTZ(8))):decimal(11,9)>
+-- !query output
+35.999999990
+
+
+-- !query
+SELECT extract(SECOND FROM NULL :: timestamp_ntz(9))
+-- !query schema
+struct<extract(SECOND FROM CAST(NULL AS TIMESTAMP_NTZ(9))):decimal(11,9)>
+-- !query output
+NULL
+
+
+-- !query
+SELECT extract(SECOND FROM TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<extract(SECOND FROM TimestampNanosVal(-315570924876544,
789)):decimal(11,9)>
+-- !query output
+35.123456789
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 0bf45b63f935..8031ed5cf7c2 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
@@ -98,6 +98,54 @@ abstract class TimestampNanosFunctionsSuiteBase extends
SharedSparkSession {
df.selectExpr("hour(ntz)", "minute(ntz)", "second(ntz)"),
Row(5, 6, 7))
}
+
+ test("SPARK-57340: extract/date_part HOUR and MINUTE over
nanosecond-precision timestamps") {
+ Seq(7, 8, 9).foreach { p =>
+ val df = nanosDF(p)
+ val result1 = df.selectExpr(
+ "extract(HOUR FROM ntz)", "extract(MINUTE FROM ntz)",
+ "extract(HOUR FROM ltz)", "extract(MINUTE FROM ltz)")
+ val result2 = df.selectExpr(
+ "date_part('HOUR', ntz)", "date_part('MINUTE', ntz)",
+ "date_part('HOUR', ltz)", "date_part('MINUTE', ltz)")
+ val result3 = df.select(
+ extract(lit("HOUR"), col("ntz")), extract(lit("MINUTE"), col("ntz")),
+ extract(lit("HOUR"), col("ltz")), extract(lit("MINUTE"), col("ltz")))
+ checkAnswer(result1, result2)
+ checkAnswer(result1, result3)
+ checkAnswer(result1, Seq(Row(13, 24, 13, 24), Row(null, null, null,
null)))
+ }
+ }
+
+ test("SPARK-57340: extract/date_part SECOND keeps the nanosecond fraction") {
+ // EXTRACT(SECOND) widens the result to DECIMAL(11, 9); digits below the
type's precision
+ // `p` were floored when the values were created, so they read back as
zeros.
+ Seq(
+ 7 -> ("35.123456700", "35.987654300"),
+ 8 -> ("35.123456780", "35.987654320"),
+ 9 -> ("35.123456789", "35.987654321")
+ ).foreach { case (p, (ntzSec, ltzSec)) =>
+ val df = nanosDF(p)
+ val result1 = df.selectExpr("extract(SECOND FROM ntz)", "extract(SECOND
FROM ltz)")
+ val result2 = df.selectExpr("date_part('SECOND', ntz)",
"date_part('SECOND', ltz)")
+ val result3 = df.select(
+ extract(lit("SECOND"), col("ntz")), extract(lit("SECOND"), col("ltz")))
+ checkAnswer(result1, result2)
+ checkAnswer(result1, result3)
+ checkAnswer(result1, Seq(
+ Row(new java.math.BigDecimal(ntzSec), new
java.math.BigDecimal(ltzSec)),
+ Row(null, null)))
+ }
+ }
+
+ test("SPARK-57340: extract SECOND over pre-epoch nanosecond TIMESTAMP_NTZ") {
+ val schema = new StructType().add("ntz", TimestampNTZNanosType(9))
+ val data = Seq(Row(LocalDateTime.parse("1960-01-01T05:06:07.123456789")))
+ val df = spark.createDataFrame(spark.sparkContext.parallelize(data),
schema)
+ checkAnswer(
+ df.selectExpr("extract(SECOND FROM ntz)"),
+ Row(new java.math.BigDecimal("7.123456789")))
+ }
}
// Runs the nanosecond timestamp function tests with ANSI mode enabled
explicitly.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
