This is an automated email from the ASF dual-hosted git repository.
MaxGekk 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 e09a4fb15a45 [SPARK-57315][SQL] Support HOUR, MINUTE and SECOND
functions over nanosecond-precision timestamps
e09a4fb15a45 is described below
commit e09a4fb15a451cb00c266af30df540126b6fe3ab
Author: Maxim Gekk <[email protected]>
AuthorDate: Wed Jun 10 13:36:28 2026 +0200
[SPARK-57315][SQL] Support HOUR, MINUTE and SECOND functions over
nanosecond-precision timestamps
### What changes were proposed in this pull request?
This PR lets `hour()`, `minute()` and `second()` operate on the
nanosecond-precision timestamp types `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)`
(`p` in `[7, 9]`), the preview types under the nanosecond timestamp umbrella
(SPARK-56822).
It extends the function expression builders `HourExpressionBuilder`,
`MinuteExpressionBuilder` and `SecondExpressionBuilder`. When the argument is a
nanosecond-precision timestamp, the builder casts it down to the matching
microsecond timestamp type and reuses the existing `Hour` / `Minute` / `Second`
expressions:
- `TimestampNTZNanosType(p)` -> `TimestampNTZType`
- `TimestampLTZNanosType(p)` -> `TimestampType`
The cast keeps `epochMicros` and drops the sub-microsecond digits, which is
lossless for these integer time-of-day fields. The conversion is centralized in
a small `NanosTimestampCast.castToMicros` helper. `SecondWithFraction` (the
`extract(SECOND)` path returning `DECIMAL(8, 6)`) is intentionally not covered
because its result depends on the sub-microsecond digits.
This is an alternative implementation to #56366, which addresses the same
JIRA with a dedicated analyzer rule (`ResolveTimestampNanosExpressions`)
instead of changing the builders.
Closes #56366
### Why are the changes needed?
To support `HOUR` / `MINUTE` / `SECOND` over the nanosecond timestamp types
as part of the nanosecond timestamp preview (SPARK-56822), reusing the existing
microsecond expressions rather than introducing new ones.
### Does this PR introduce _any_ user-facing change?
Yes, but only when the preview flag `spark.sql.timestampNanosTypes.enabled`
is set to `true` (default `false`). With the flag on, `hour`/`minute`/`second`
now accept `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` arguments; previously they
failed analysis with a type-check error. Behavior for microsecond `TIMESTAMP` /
`TIMESTAMP_NTZ`, `DATE`, strings, and `TIME` is unchanged.
Example (flag enabled):
```sql
SELECT hour(TIMESTAMP_NTZ '2018-02-14 12:58:59.123456789'); -- 12
SELECT minute(TIMESTAMP_LTZ '2009-07-30 12:58:59.123456789'); -- 58
```
### How was this patch tested?
- New end-to-end golden tests in `timestamp-ntz-nanos.sql` /
`timestamp-ltz-nanos.sql` covering: full nanosecond precision (`p = 9`, via
typed literals), explicit precision via `::` casts (`p = 7/8`), NULLs,
pre-epoch values (negative-epoch path), and `TIMESTAMP_LTZ` extraction across
source time zones (`Asia/Kolkata`, `UTC`) while the session zone is
`America/Los_Angeles`.
- Added runnable nanosecond-literal examples to the
`hour`/`minute`/`second` function docs, validated by `ExpressionInfoSuite`
("check outputs of expression examples") and `ExpressionsSchemaSuite`.
- Updated the `hour`/`minute`/`second` scaladoc in `functions.scala`.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Cursor (Claude Opus 4.8)
Closes #56368 from MaxGekk/nanos-hour-builders.
Authored-by: Maxim Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
(cherry picked from commit 19aec7afac4d32d36b46da0186426b0a4e4490bf)
Signed-off-by: Max Gekk <[email protected]>
---
.../scala/org/apache/spark/sql/functions.scala | 12 ++-
.../sql/catalyst/expressions/timeExpressions.scala | 53 ++++++++-
.../analyzer-results/timestamp-ltz-nanos.sql.out | 105 ++++++++++++++++++
.../analyzer-results/timestamp-ntz-nanos.sql.out | 63 +++++++++++
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 25 +++++
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 16 +++
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 120 +++++++++++++++++++++
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 72 +++++++++++++
.../sql/TimestampNanosFunctionsSuiteBase.scala | 111 +++++++++++++++++++
9 files changed, 570 insertions(+), 7 deletions(-)
diff --git a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
index ab21a784796d..fe91d0bd7b69 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
@@ -7726,7 +7726,9 @@ object functions {
def dayofyear(e: Column): Column = Column.fn("dayofyear", e)
/**
- * Extracts the hours as an integer from a given date/time/timestamp/string.
+ * Extracts the hours as an integer from a given date/time/timestamp/string.
The input may also
+ * be a nanosecond-precision timestamp `TIMESTAMP_NTZ(p)` or
`TIMESTAMP_LTZ(p)` (`p` in
+ * `[7, 9]`, since 4.3.0), in which case the sub-microsecond digits are
ignored.
* @return
* An integer, or null if the input was a string that could not be cast to
a date
* @group datetime_funcs
@@ -7799,7 +7801,9 @@ object functions {
def last_day(e: Column): Column = Column.fn("last_day", e)
/**
- * Extracts the minutes as an integer from a given
date/time/timestamp/string.
+ * Extracts the minutes as an integer from a given
date/time/timestamp/string. The input may
+ * also be a nanosecond-precision timestamp `TIMESTAMP_NTZ(p)` or
`TIMESTAMP_LTZ(p)` (`p` in
+ * `[7, 9]`, since 4.3.0), in which case the sub-microsecond digits are
ignored.
* @return
* An integer, or null if the input was a string that could not be cast to
a date
* @group datetime_funcs
@@ -7905,7 +7909,9 @@ object functions {
Column.fn("next_day", date, dayOfWeek)
/**
- * Extracts the seconds as an integer from a given
date/time/timestamp/string.
+ * Extracts the seconds as an integer from a given
date/time/timestamp/string. The input may
+ * also be a nanosecond-precision timestamp `TIMESTAMP_NTZ(p)` or
`TIMESTAMP_LTZ(p)` (`p` in
+ * `[7, 9]`, since 4.3.0), in which case the sub-microsecond digits are
ignored.
* @return
* An integer, or null if the input was a string that could not be cast to
a timestamp
* @group datetime_funcs
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
index 6469a70349a7..85afdf72eecc 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala
@@ -34,7 +34,7 @@ import
org.apache.spark.sql.catalyst.util.TypeUtils.ordinalNumber
import org.apache.spark.sql.errors.{QueryCompilationErrors,
QueryExecutionErrors}
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.internal.types.StringTypeWithCollation
-import org.apache.spark.sql.types.{AbstractDataType, AnyTimeType, ByteType,
DataType, DayTimeIntervalType, DecimalType, IntegerType, IntegralType,
LongType, NumericType, ObjectType, TimeType}
+import org.apache.spark.sql.types.{AbstractDataType, AnyTimeType, ByteType,
DataType, DayTimeIntervalType, DecimalType, IntegerType, IntegralType,
LongType, NumericType, ObjectType, TimestampLTZNanosType,
TimestampNTZNanosType, TimestampNTZType, TimestampType, TimeType}
import org.apache.spark.sql.types.DayTimeIntervalType.{HOUR, SECOND}
import org.apache.spark.unsafe.types.UTF8String
@@ -157,6 +157,24 @@ object TimePart {
}
}
+private[expressions] object NanosTimestampCast {
+ /**
+ * Casts a nanosecond-precision timestamp (`TIMESTAMP_NTZ(p)` /
`TIMESTAMP_LTZ(p)`, `p` in
+ * `[7, 9]`) down to the matching microsecond timestamp type so that
expressions accepting only
+ * the microsecond timestamp types can reuse it:
+ * - TimestampNTZNanosType(p) -> TimestampNTZType
+ * - TimestampLTZNanosType(p) -> TimestampType
+ *
+ * The cast keeps `epochMicros` and drops the sub-microsecond digits, which
is lossless for the
+ * integer time-of-day fields (hour/minute/second). Inputs of any other type
are returned as is.
+ */
+ def castToMicros(child: Expression): Expression = child.dataType match {
+ case _: TimestampNTZNanosType => Cast(child, TimestampNTZType)
+ case _: TimestampLTZNanosType => Cast(child, TimestampType)
+ case _ => child
+ }
+}
+
/**
* * Parses a column to a time based on the supplied format.
*/
@@ -240,12 +258,19 @@ case class MinutesOfTime(child: Expression)
If `expr` is a TIMESTAMP or a string that can be cast to timestamp,
it returns the minute of that timestamp.
+ If `expr` is a nanosecond-precision timestamp TIMESTAMP_NTZ(p) or
TIMESTAMP_LTZ(p)
+ with p in [7, 9] (since 4.3.0), it returns the minute of that timestamp,
ignoring the
+ sub-microsecond digits.
If `expr` is a TIME type (since 4.1.0), it returns the minute of the
time-of-day.
""",
examples = """
Examples:
> SELECT _FUNC_('2009-07-30 12:58:59');
58
+ > SELECT _FUNC_(TIMESTAMP_NTZ '2009-07-30 12:58:59.123456789');
+ 58
+ > SELECT _FUNC_(TIMESTAMP_LTZ '2009-07-30 12:58:59.123456789');
+ 58
> SELECT _FUNC_(TIME'23:59:59.999999');
59
""",
@@ -262,7 +287,9 @@ object MinuteExpressionBuilder extends ExpressionBuilder {
case _: TimeType =>
MinutesOfTime(child)
case _ =>
- Minute(child)
+ // Casts nanosecond-precision timestamps down to the microsecond
timestamp type;
+ // other types are passed through unchanged.
+ Minute(NanosTimestampCast.castToMicros(child))
}
}
}
@@ -298,12 +325,19 @@ case class HoursOfTime(child: Expression)
If `expr` is a TIMESTAMP or a string that can be cast to timestamp,
it returns the hour of that timestamp.
+ If `expr` is a nanosecond-precision timestamp TIMESTAMP_NTZ(p) or
TIMESTAMP_LTZ(p)
+ with p in [7, 9] (since 4.3.0), it returns the hour of that timestamp,
ignoring the
+ sub-microsecond digits.
If `expr` is a TIME type (since 4.1.0), it returns the hour of the
time-of-day.
""",
examples = """
Examples:
> SELECT _FUNC_('2018-02-14 12:58:59');
12
+ > SELECT _FUNC_(TIMESTAMP_NTZ '2018-02-14 12:58:59.123456789');
+ 12
+ > SELECT _FUNC_(TIMESTAMP_LTZ '2018-02-14 12:58:59.123456789');
+ 12
> SELECT _FUNC_(TIME'13:59:59.999999');
13
""",
@@ -319,7 +353,9 @@ object HourExpressionBuilder extends ExpressionBuilder {
case _: TimeType =>
HoursOfTime(child)
case _ =>
- Hour(child)
+ // Casts nanosecond-precision timestamps down to the microsecond
timestamp type;
+ // other types are passed through unchanged.
+ Hour(NanosTimestampCast.castToMicros(child))
}
}
}
@@ -381,12 +417,19 @@ case class SecondsOfTime(child: Expression)
If `expr` is a TIMESTAMP or a string that can be cast to timestamp,
it returns the second of that timestamp.
+ If `expr` is a nanosecond-precision timestamp TIMESTAMP_NTZ(p) or
TIMESTAMP_LTZ(p)
+ with p in [7, 9] (since 4.3.0), it returns the second of that timestamp,
ignoring the
+ sub-microsecond digits.
If `expr` is a TIME type (since 4.1.0), it returns the second of the
time-of-day.
""",
examples = """
Examples:
> SELECT _FUNC_('2018-02-14 12:58:59');
59
+ > SELECT _FUNC_(TIMESTAMP_NTZ '2018-02-14 12:58:59.123456789');
+ 59
+ > SELECT _FUNC_(TIMESTAMP_LTZ '2018-02-14 12:58:59.123456789');
+ 59
> SELECT _FUNC_(TIME'13:25:59.999999');
59
""",
@@ -402,7 +445,9 @@ object SecondExpressionBuilder extends ExpressionBuilder {
case _: TimeType =>
SecondsOfTime(child)
case _ =>
- Second(child)
+ // Casts nanosecond-precision timestamps down to the microsecond
timestamp type;
+ // other types are passed through unchanged.
+ Second(NanosTimestampCast.castToMicros(child))
}
}
}
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 6f896ab8014c..fa8c562c9416 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
@@ -116,3 +116,108 @@ SELECT named_struct('f', CAST(NULL AS timestamp_ltz(9)))
-- !query analysis
Project [named_struct(f, cast(null as timestamp_ltz(9))) AS named_struct(f,
CAST(NULL AS TIMESTAMP_LTZ(9)))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [hour(cast(TimestampNanosVal(1577913875123456, 789) as timestamp),
Some(America/Los_Angeles)) AS hour(TimestampNanosVal(1577913875123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [minute(cast(TimestampNanosVal(1577913875123456, 789) as timestamp),
Some(America/Los_Angeles)) AS minute(TimestampNanosVal(1577913875123456,
789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [second(cast(TimestampNanosVal(1577913875123456, 789) as timestamp),
Some(America/Los_Angeles)) AS second(TimestampNanosVal(1577913875123456,
789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour('2020-01-01 13:24:35.999999999' :: timestamp_ltz(7))
+-- !query analysis
+Project [hour(cast(cast(2020-01-01 13:24:35.999999999 as timestamp_ltz(7)) as
timestamp), Some(America/Los_Angeles)) AS hour(CAST(2020-01-01
13:24:35.999999999 AS TIMESTAMP_LTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT second('2020-01-01 13:24:35.999999999' :: timestamp_ltz(8))
+-- !query analysis
+Project [second(cast(cast(2020-01-01 13:24:35.999999999 as timestamp_ltz(8))
as timestamp), Some(America/Los_Angeles)) AS second(CAST(2020-01-01
13:24:35.999999999 AS TIMESTAMP_LTZ(8)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour(NULL :: timestamp_ltz(9))
+-- !query analysis
+Project [hour(cast(cast(null as timestamp_ltz(9)) as timestamp),
Some(America/Los_Angeles)) AS hour(CAST(NULL AS TIMESTAMP_LTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [hour(cast(TimestampNanosVal(-315542124876544, 789) as timestamp),
Some(America/Los_Angeles)) AS hour(TimestampNanosVal(-315542124876544, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [minute(cast(TimestampNanosVal(-315542124876544, 789) as timestamp),
Some(America/Los_Angeles)) AS minute(TimestampNanosVal(-315542124876544,
789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [second(cast(TimestampNanosVal(-315542124876544, 789) as timestamp),
Some(America/Los_Angeles)) AS second(TimestampNanosVal(-315542124876544,
789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata')
+-- !query analysis
+Project [hour(cast(TimestampNanosVal(1577865275123456, 789) as timestamp),
Some(America/Los_Angeles)) AS hour(TimestampNanosVal(1577865275123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata')
+-- !query analysis
+Project [minute(cast(TimestampNanosVal(1577865275123456, 789) as timestamp),
Some(America/Los_Angeles)) AS minute(TimestampNanosVal(1577865275123456,
789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata')
+-- !query analysis
+Project [second(cast(TimestampNanosVal(1577865275123456, 789) as timestamp),
Some(America/Los_Angeles)) AS second(TimestampNanosVal(1577865275123456,
789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query analysis
+Project [hour(cast(TimestampNanosVal(1577885075123456, 789) as timestamp),
Some(America/Los_Angeles)) AS hour(TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query analysis
+Project [minute(cast(TimestampNanosVal(1577885075123456, 789) as timestamp),
Some(America/Los_Angeles)) AS minute(TimestampNanosVal(1577885075123456,
789))#x]
++- OneRowRelation
+
+
+-- !query
+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
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 94570f139e84..97bcd3b290a0 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
@@ -116,3 +116,66 @@ SELECT named_struct('f', CAST(NULL AS timestamp_ntz(9)))
-- !query analysis
Project [named_struct(f, cast(null as timestamp_ntz(9))) AS named_struct(f,
CAST(NULL AS TIMESTAMP_NTZ(9)))#x]
+- OneRowRelation
+
+
+-- !query
+SELECT hour(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [hour(cast(TimestampNanosVal(1577885075123456, 789) as timestamp_ntz),
Some(America/Los_Angeles)) AS hour(TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT minute(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [minute(cast(TimestampNanosVal(1577885075123456, 789) as
timestamp_ntz), Some(America/Los_Angeles)) AS
minute(TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT second(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query analysis
+Project [second(cast(TimestampNanosVal(1577885075123456, 789) as
timestamp_ntz), Some(America/Los_Angeles)) AS
second(TimestampNanosVal(1577885075123456, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour('2020-01-01 13:24:35.999999999' :: timestamp_ntz(7))
+-- !query analysis
+Project [hour(cast(cast(2020-01-01 13:24:35.999999999 as timestamp_ntz(7)) as
timestamp_ntz), Some(America/Los_Angeles)) AS hour(CAST(2020-01-01
13:24:35.999999999 AS TIMESTAMP_NTZ(7)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT second('2020-01-01 13:24:35.999999999' :: timestamp_ntz(8))
+-- !query analysis
+Project [second(cast(cast(2020-01-01 13:24:35.999999999 as timestamp_ntz(8))
as timestamp_ntz), Some(America/Los_Angeles)) AS second(CAST(2020-01-01
13:24:35.999999999 AS TIMESTAMP_NTZ(8)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour(NULL :: timestamp_ntz(9))
+-- !query analysis
+Project [hour(cast(cast(null as timestamp_ntz(9)) as timestamp_ntz),
Some(America/Los_Angeles)) AS hour(CAST(NULL AS TIMESTAMP_NTZ(9)))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT hour(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [hour(cast(TimestampNanosVal(-315570924876544, 789) as timestamp_ntz),
Some(America/Los_Angeles)) AS hour(TimestampNanosVal(-315570924876544, 789))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT minute(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query analysis
+Project [minute(cast(TimestampNanosVal(-315570924876544, 789) as
timestamp_ntz), Some(America/Los_Angeles)) AS
minute(TimestampNanosVal(-315570924876544, 789))#x]
++- OneRowRelation
+
+
+-- !query
+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
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 f7c36256a6be..b318ad3021bc 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
@@ -30,3 +30,28 @@ SELECT CAST(NULL AS timestamp_ltz(9));
SELECT array(CAST(NULL AS timestamp_ltz(9)));
SELECT map('k', CAST(NULL AS timestamp_ltz(9)));
SELECT named_struct('f', CAST(NULL AS timestamp_ltz(9)));
+
+-- HOUR/MINUTE/SECOND over nanosecond-precision values (SPARK-57315). LTZ
extracts in the
+-- session time zone; the sub-microsecond digits never affect the integer
field.
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789');
+SELECT hour('2020-01-01 13:24:35.999999999' :: timestamp_ltz(7));
+SELECT second('2020-01-01 13:24:35.999999999' :: timestamp_ltz(8));
+SELECT hour(NULL :: timestamp_ltz(9));
+
+-- Pre-epoch nanosecond values exercise the negative-epoch path;
HOUR/MINUTE/SECOND
+-- read the wall-clock fields in the session time zone.
+SELECT hour(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789');
+SELECT minute(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789');
+SELECT second(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789');
+
+-- LTZ nanos: the literal's time zone defines the instant, which is then
extracted in the
+-- session time zone (America/Los_Angeles, UTC-08:00). A source zone with a
sub-hour offset
+-- (Asia/Kolkata is UTC+05:30) shifts both the hour and the minute fields.
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata');
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata');
+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');
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 c1db88eb409b..0ba10ff1ff06 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
@@ -30,3 +30,19 @@ SELECT CAST(NULL AS timestamp_ntz(9));
SELECT array(CAST(NULL AS timestamp_ntz(9)));
SELECT map('k', CAST(NULL AS timestamp_ntz(9)));
SELECT named_struct('f', CAST(NULL AS timestamp_ntz(9)));
+
+-- HOUR/MINUTE/SECOND over nanosecond-precision values (SPARK-57315). NTZ
extracts the
+-- wall-clock components, so the result is zone-independent and the
sub-microsecond digits
+-- never affect the integer field.
+SELECT hour(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT minute(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT second(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789');
+SELECT hour('2020-01-01 13:24:35.999999999' :: timestamp_ntz(7));
+SELECT second('2020-01-01 13:24:35.999999999' :: timestamp_ntz(8));
+SELECT hour(NULL :: timestamp_ntz(9));
+
+-- Pre-epoch nanosecond values exercise the negative-epoch path;
HOUR/MINUTE/SECOND
+-- still read the wall-clock fields and remain zone-independent.
+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');
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 75171edef611..aa0d6cc4c187 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
@@ -133,3 +133,123 @@ SELECT named_struct('f', CAST(NULL AS timestamp_ltz(9)))
struct<named_struct(f, CAST(NULL AS
TIMESTAMP_LTZ(9))):struct<f:timestamp_ltz(9)>>
-- !query output
{"f":null}
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<hour(TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<minute(TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<second(TimestampNanosVal(1577913875123456, 789)):int>
+-- !query output
+35
+
+
+-- !query
+SELECT hour('2020-01-01 13:24:35.999999999' :: timestamp_ltz(7))
+-- !query schema
+struct<hour(CAST(2020-01-01 13:24:35.999999999 AS TIMESTAMP_LTZ(7))):int>
+-- !query output
+13
+
+
+-- !query
+SELECT second('2020-01-01 13:24:35.999999999' :: timestamp_ltz(8))
+-- !query schema
+struct<second(CAST(2020-01-01 13:24:35.999999999 AS TIMESTAMP_LTZ(8))):int>
+-- !query output
+35
+
+
+-- !query
+SELECT hour(NULL :: timestamp_ltz(9))
+-- !query schema
+struct<hour(CAST(NULL AS TIMESTAMP_LTZ(9))):int>
+-- !query output
+NULL
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<hour(TimestampNanosVal(-315542124876544, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<minute(TimestampNanosVal(-315542124876544, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<second(TimestampNanosVal(-315542124876544, 789)):int>
+-- !query output
+35
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata')
+-- !query schema
+struct<hour(TimestampNanosVal(1577865275123456, 789)):int>
+-- !query output
+23
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata')
+-- !query schema
+struct<minute(TimestampNanosVal(1577865275123456, 789)):int>
+-- !query output
+54
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 Asia/Kolkata')
+-- !query schema
+struct<second(TimestampNanosVal(1577865275123456, 789)):int>
+-- !query output
+35
+
+
+-- !query
+SELECT hour(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query schema
+struct<hour(TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+5
+
+
+-- !query
+SELECT minute(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query schema
+struct<minute(TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT second(TIMESTAMP_LTZ '2020-01-01 13:24:35.123456789 UTC')
+-- !query schema
+struct<second(TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+35
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 39542fdd121e..25bb7a9322a8 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
@@ -133,3 +133,75 @@ SELECT named_struct('f', CAST(NULL AS timestamp_ntz(9)))
struct<named_struct(f, CAST(NULL AS
TIMESTAMP_NTZ(9))):struct<f:timestamp_ntz(9)>>
-- !query output
{"f":null}
+
+
+-- !query
+SELECT hour(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<hour(TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT minute(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<minute(TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT second(TIMESTAMP_NTZ '2020-01-01 13:24:35.123456789')
+-- !query schema
+struct<second(TimestampNanosVal(1577885075123456, 789)):int>
+-- !query output
+35
+
+
+-- !query
+SELECT hour('2020-01-01 13:24:35.999999999' :: timestamp_ntz(7))
+-- !query schema
+struct<hour(CAST(2020-01-01 13:24:35.999999999 AS TIMESTAMP_NTZ(7))):int>
+-- !query output
+13
+
+
+-- !query
+SELECT second('2020-01-01 13:24:35.999999999' :: timestamp_ntz(8))
+-- !query schema
+struct<second(CAST(2020-01-01 13:24:35.999999999 AS TIMESTAMP_NTZ(8))):int>
+-- !query output
+35
+
+
+-- !query
+SELECT hour(NULL :: timestamp_ntz(9))
+-- !query schema
+struct<hour(CAST(NULL AS TIMESTAMP_NTZ(9))):int>
+-- !query output
+NULL
+
+
+-- !query
+SELECT hour(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<hour(TimestampNanosVal(-315570924876544, 789)):int>
+-- !query output
+13
+
+
+-- !query
+SELECT minute(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<minute(TimestampNanosVal(-315570924876544, 789)):int>
+-- !query output
+24
+
+
+-- !query
+SELECT second(TIMESTAMP_NTZ '1960-01-01 13:24:35.123456789')
+-- !query schema
+struct<second(TimestampNanosVal(-315570924876544, 789)):int>
+-- !query output
+35
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
new file mode 100644
index 000000000000..0bf45b63f935
--- /dev/null
+++
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
@@ -0,0 +1,111 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql
+
+import java.time.{Instant, LocalDateTime}
+
+import org.apache.spark.SparkConf
+import org.apache.spark.sql.functions._
+import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.test.SharedSparkSession
+import org.apache.spark.sql.types._
+
+/**
+ * End-to-end tests for the `hour`, `minute` and `second` functions over the
nanosecond-precision
+ * timestamp types `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (`p` in `[7, 9]`),
part of the
+ * nanosecond timestamp preview (SPARK-56822). Each test exercises both the
SQL path
+ * (`selectExpr`) and the Scala `Column` API (`functions.hour` / `minute` /
`second`).
+ */
+abstract class TimestampNanosFunctionsSuiteBase extends SharedSparkSession {
+
+ // The nanosecond timestamp types are gated behind a preview flag that is
enabled by default
+ // under tests (Utils.isTesting), so it is not set here. The session time
zone is fixed so that
+ // the TIMESTAMP_LTZ extraction below is deterministic.
+ override def sparkConf: SparkConf = super.sparkConf
+ .set(SQLConf.SESSION_LOCAL_TIMEZONE.key, "America/Los_Angeles")
+
+ // A DataFrame with one TIMESTAMP_NTZ(p) and one TIMESTAMP_LTZ(p) nanosecond
column plus a NULL
+ // row. The wall-clock time of both non-null values is 13:24:35 in the
session time zone, so the
+ // hour/minute/second fields match regardless of `p`; the sub-microsecond
digits never affect the
+ // integer result.
+ private def nanosDF(precision: Int): DataFrame = {
+ val schema = new StructType()
+ .add("ntz", TimestampNTZNanosType(precision))
+ .add("ltz", TimestampLTZNanosType(precision))
+ val data = Seq(
+ Row(
+ // TIMESTAMP_NTZ is zone-independent: 13:24:35 wall-clock.
+ LocalDateTime.parse("2020-01-01T13:24:35.123456789"),
+ // TIMESTAMP_LTZ: 21:24:35 UTC -> 13:24:35 in America/Los_Angeles
(UTC-8 in January).
+ Instant.parse("2020-01-01T21:24:35.987654321Z")),
+ Row(null, null))
+ spark.createDataFrame(spark.sparkContext.parallelize(data), schema)
+ }
+
+ test("SPARK-57315: hour over nanosecond-precision timestamps") {
+ Seq(7, 8, 9).foreach { p =>
+ val df = nanosDF(p)
+ val result1 = df.selectExpr("hour(ntz)", "hour(ltz)")
+ val result2 = df.select(hour(col("ntz")), hour(col("ltz")))
+ checkAnswer(result1, result2)
+ checkAnswer(result1, Seq(Row(13, 13), Row(null, null)))
+ }
+ }
+
+ test("SPARK-57315: minute over nanosecond-precision timestamps") {
+ Seq(7, 8, 9).foreach { p =>
+ val df = nanosDF(p)
+ val result1 = df.selectExpr("minute(ntz)", "minute(ltz)")
+ val result2 = df.select(minute(col("ntz")), minute(col("ltz")))
+ checkAnswer(result1, result2)
+ checkAnswer(result1, Seq(Row(24, 24), Row(null, null)))
+ }
+ }
+
+ test("SPARK-57315: second over nanosecond-precision timestamps") {
+ Seq(7, 8, 9).foreach { p =>
+ val df = nanosDF(p)
+ val result1 = df.selectExpr("second(ntz)", "second(ltz)")
+ val result2 = df.select(second(col("ntz")), second(col("ltz")))
+ checkAnswer(result1, result2)
+ checkAnswer(result1, Seq(Row(35, 35), Row(null, null)))
+ }
+ }
+
+ test("SPARK-57315: hour/minute/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.select(hour(col("ntz")), minute(col("ntz")), second(col("ntz"))),
+ Row(5, 6, 7))
+ checkAnswer(
+ df.selectExpr("hour(ntz)", "minute(ntz)", "second(ntz)"),
+ Row(5, 6, 7))
+ }
+}
+
+// Runs the nanosecond timestamp function tests with ANSI mode enabled
explicitly.
+class TimestampNanosFunctionsAnsiOnSuite extends
TimestampNanosFunctionsSuiteBase {
+ override def sparkConf: SparkConf =
super.sparkConf.set(SQLConf.ANSI_ENABLED.key, "true")
+}
+
+// Runs the nanosecond timestamp function tests with ANSI mode disabled
explicitly.
+class TimestampNanosFunctionsAnsiOffSuite extends
TimestampNanosFunctionsSuiteBase {
+ override def sparkConf: SparkConf =
super.sparkConf.set(SQLConf.ANSI_ENABLED.key, "false")
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
