You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ma...@apache.org on 2025/04/19 07:41:12 UTC
(spark) branch master updated: [SPARK-51553][SQL] Modify EXTRACT to support TIME data type
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 2e16568ddc96 [SPARK-51553][SQL] Modify EXTRACT to support TIME data type
2e16568ddc96 is described below
commit 2e16568ddc964045c0bdf9be7983135b2e961d61
Author: vinodkc <vi...@gmail.com>
AuthorDate: Sat Apr 19 09:40:58 2025 +0200
[SPARK-51553][SQL] Modify EXTRACT to support TIME data type
This PR
1) Modifies the `EXTRACT` method to support the TIME data type.
Supported values of field when source is **TIME**:
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 (a DECIMAL(8, 6))
```
scala> spark.sql("SELECT extract(HOUR FROM time'09:08:01.987654')").show
+-----------------------------------------+
|extract(HOUR FROM TIME '09:08:01.987654')|
+-----------------------------------------+
| 9|
+-----------------------------------------+
scala> spark.sql("SELECT extract(MINUTE FROM time'09:08:01.987654')").show
+-------------------------------------------+
|extract(MINUTE FROM TIME '09:08:01.987654')|
+-------------------------------------------+
| 8|
+-------------------------------------------+
scala> spark.sql("SELECT extract(SECOND FROM time'09:08:01.987654')").show
+-------------------------------------------+
|extract(SECOND FROM TIME '09:08:01.987654')|
+-------------------------------------------+
| 1.987654|
+-------------------------------------------+
```
With TimeType precisions 0 to 6
```
spark.sql("""select
extract(SECOND FROM cast('09:08:01.987654' as time(0))) as t0,
extract(SECOND FROM cast('09:08:01.987654' as time(1))) as t1,
extract(SECOND FROM cast('09:08:01.987654' as time(2))) as t2,
extract(SECOND FROM cast('09:08:01.987654' as time(3))) as t3,
extract(SECOND FROM cast('09:08:01.987654' as time(4))) as t4,
extract(SECOND FROM cast('09:08:01.987654' as time(5))) as t5,
extract(SECOND FROM cast('09:08:01.987654' as time(6))) as t6
""").show
+--------+--------+--------+--------+--------+--------+--------+
| t0| t1| t2| t3| t4| t5| t6|
+--------+--------+--------+--------+--------+--------+--------+
|1.000000|1.900000|1.980000|1.987000|1.987600|1.987650|1.987654|
+--------+--------+--------+--------+--------+--------+--------+
```
Note : `secondoftime_with_fraction` added to support seconds with fraction in the built-in function `extract()`
eg:
`SELECT extract(SECOND FROM time'09:08:01.987654')`
### What changes were proposed in this pull request?
### Why are the changes needed?
SPIP: Add the TIME data type [SPARK-51162](https://issues.apache.org/jira/browse/SPARK-51162)
### Does this PR introduce _any_ user-facing change?
Yes, this PR modified the existing built-in function `extract` to support `TimeType`. Also added a new built-in function `secondoftime_with_fraction` to get second of time with the fraction.
### How was this patch tested?
Manual testing, as shown above, and UTs added:
```
build/sbt "sql/testOnly *ExpressionsSchemaSuite"
build/sbt "test:testOnly *TimeExpressionsSuite.scala"
build/sbt "test:testOnly *SQLQuerySuite.scala"
```
### Was this patch authored or co-authored using generative AI tooling?
No
Closes #50558 from vinodkc/br_SPARK-51553.
Authored-by: vinodkc <vi...@gmail.com>
Signed-off-by: Max Gekk <ma...@gmail.com>
---
.../catalyst/expressions/datetimeExpressions.scala | 16 +-
.../sql/catalyst/expressions/timeExpressions.scala | 41 ++++
.../spark/sql/catalyst/util/DateTimeUtils.scala | 17 ++
.../expressions/TimeExpressionsSuite.scala | 29 +++
.../sql-tests/analyzer-results/time.sql.out | 210 ++++++++++++++++++
.../src/test/resources/sql-tests/inputs/time.sql | 38 +++-
.../test/resources/sql-tests/results/time.sql.out | 240 +++++++++++++++++++++
7 files changed, 588 insertions(+), 3 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 6767731fc25b..b4b81dade83f 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
@@ -3093,7 +3093,7 @@ object DatePartExpressionBuilder extends ExpressionBuilder {
// scalastyle:off line.size.limit
@ExpressionDescription(
- usage = "_FUNC_(field FROM source) - Extracts a part of the date/timestamp or interval source.",
+ usage = "_FUNC_(field FROM source) - Extracts a part of the date or timestamp or time or interval source.",
arguments = """
Arguments:
* field - selects which part of the source should be extracted
@@ -3117,7 +3117,11 @@ object DatePartExpressionBuilder extends ExpressionBuilder {
- "HOUR", ("H", "HOURS", "HR", "HRS") - how many hours the `microseconds` contains
- "MINUTE", ("M", "MIN", "MINS", "MINUTES") - how many minutes left after taking hours from `microseconds`
- "SECOND", ("S", "SEC", "SECONDS", "SECS") - how many second with fractions left after taking hours and minutes from `microseconds`
- * source - a date/timestamp or interval column from where `field` should be extracted
+ - Supported string values of `field` for time (which consists of `hour`, `minute`, `second`) are(case insensitive):
+ - "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 up to micro second precision. Returns a DECIMAL(8, 6) precision value.
+ * source - a date or timestamp or time or interval column from where `field` should be extracted
""",
examples = """
Examples:
@@ -3137,6 +3141,12 @@ object DatePartExpressionBuilder extends ExpressionBuilder {
11
> SELECT _FUNC_(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND);
55
+ > SELECT _FUNC_(HOUR FROM time '09:08:01.000001');
+ 9
+ > SELECT _FUNC_(MINUTE FROM time '09:08:01.000001');
+ 8
+ > SELECT _FUNC_(SECOND FROM time '09:08:01.000001');
+ 1.000001
""",
note = """
The _FUNC_ function is equivalent to `date_part(field, source)`.
@@ -3175,6 +3185,8 @@ object Extract {
source.dataType match {
case _: AnsiIntervalType | CalendarIntervalType =>
ExtractIntervalPart.parseExtractField(fieldStr.toString, source)
+ case _: TimeType =>
+ TimePart.parseExtractField(fieldStr.toString, source)
case _ =>
DatePart.parseExtractField(fieldStr.toString, source)
}
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 a4a5edcb61e5..9f584875914c 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
@@ -18,6 +18,7 @@
package org.apache.spark.sql.catalyst.expressions
import java.time.DateTimeException
+import java.util.Locale
import org.apache.spark.sql.catalyst.analysis.{ExpressionBuilder, TypeCheckResult}
import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.{DataTypeMismatch, TypeCheckSuccess}
@@ -127,6 +128,20 @@ case class ToTimeParser(fmt: Option[String]) {
}
}
+object TimePart {
+
+ def parseExtractField(extractField: String, source: Expression): Expression =
+ extractField.toUpperCase(Locale.ROOT) match {
+ case "HOUR" | "H" | "HOURS" | "HR" | "HRS" => HoursOfTime(source)
+ case "MINUTE" | "M" | "MIN" | "MINS" | "MINUTES" => MinutesOfTime(source)
+ case "SECOND" | "S" | "SEC" | "SECONDS" | "SECS" => SecondsOfTimeWithFraction(source)
+ case _ =>
+ throw QueryCompilationErrors.literalTypeUnsupportedForSourceTypeError(
+ extractField,
+ source)
+ }
+}
+
/**
* * Parses a column to a time based on the supplied format.
*/
@@ -297,6 +312,32 @@ object HourExpressionBuilder extends ExpressionBuilder {
}
}
+case class SecondsOfTimeWithFraction(child: Expression)
+ extends RuntimeReplaceable
+ with ExpectsInputTypes {
+
+ override def replacement: Expression = {
+
+ StaticInvoke(
+ classOf[DateTimeUtils.type],
+ DecimalType(8, 6),
+ "getSecondsOfTimeWithFraction",
+ Seq(child, Literal(precision)),
+ Seq(child.dataType, IntegerType))
+ }
+ private val precision: Int = child.dataType.asInstanceOf[TimeType].precision
+
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(TimeType(precision))
+
+ override def children: Seq[Expression] = Seq(child)
+
+ override protected def withNewChildrenInternal(
+ newChildren: IndexedSeq[Expression]): Expression = {
+ copy(child = newChildren.head)
+ }
+}
+
case class SecondsOfTime(child: Expression)
extends RuntimeReplaceable
with ExpectsInputTypes {
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 1b8902319d9f..d7cbb9886ba1 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
@@ -148,6 +148,23 @@ object DateTimeUtils extends SparkDateTimeUtils {
Decimal(getMicroseconds(micros, zoneId), 8, 6)
}
+
+ /**
+ * Returns the second value with fraction from a given TIME (TimeType) value.
+ * @param micros
+ * The number of microseconds since the epoch.
+ * @param precision
+ * The time fractional seconds precision, which indicates the number of decimal digits
+ * maintained.
+ */
+ def getSecondsOfTimeWithFraction(micros: Long, precision: Int): Decimal = {
+ val seconds = (micros / MICROS_PER_SECOND) % SECONDS_PER_MINUTE
+ val scaleFactor = math.pow(10, precision).toLong
+ val scaledFraction = (micros % MICROS_PER_SECOND) * scaleFactor / MICROS_PER_SECOND
+ val fraction = scaledFraction.toDouble / scaleFactor
+ Decimal(seconds + fraction, 8, 6)
+ }
+
/**
* Returns local seconds, including fractional parts, multiplied by 1000000.
*
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala
index 55da0a3562b0..06ea49f0f71d 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/TimeExpressionsSuite.scala
@@ -333,4 +333,33 @@ class TimeExpressionsSuite extends SparkFunSuite with ExpressionEvalHelper {
}
assert(failure.getMessage.contains("cannot be cast to class java.lang.Number"))
}
+
+ test("Second with fraction from TIME type") {
+ val time = "13:11:15.987654321"
+ assert(
+ SecondsOfTimeWithFraction(
+ Cast(Literal(time), TimeType(TimeType.MICROS_PRECISION))).resolved)
+ assert(
+ SecondsOfTimeWithFraction(
+ Cast(Literal.create(time), TimeType(TimeType.MIN_PRECISION + 3))).resolved)
+ Seq(
+ 0 -> 15.0,
+ 1 -> 15.9,
+ 2 -> 15.98,
+ 3 -> 15.987,
+ 4 -> 15.9876,
+ 5 -> 15.98765,
+ 6 -> 15.987654).foreach { case (precision, expected) =>
+ checkEvaluation(
+ SecondsOfTimeWithFraction(Literal(localTime(13, 11, 15, 987654), TimeType(precision))),
+ BigDecimal(expected))
+ }
+ // Verify NULL handling
+ checkEvaluation(
+ SecondsOfTimeWithFraction(Literal.create(null, TimeType(TimeType.MICROS_PRECISION))),
+ null)
+ checkConsistencyBetweenInterpretedAndCodegen(
+ (child: Expression) => SecondsOfTimeWithFraction(child).replacement,
+ TimeType())
+ }
}
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
index 457a259a27ae..8ce87d992e75 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/time.sql.out
@@ -189,3 +189,213 @@ select make_time(1, 18, 4294967297.999999)
-- !query analysis
Project [make_time(1, 18, 4294967297.999999) AS make_time(1, 18, 4294967297.999999)#x]
+- OneRowRelation
+
+
+-- !query
+select second(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'))
+-- !query analysis
+Project [second(to_time(23-59-58.987654, Some(HH-mm-ss.SSSSSS))) AS second(to_time(23-59-58.987654, HH-mm-ss.SSSSSS))#x]
++- OneRowRelation
+
+
+-- !query
+select minute(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'))
+-- !query analysis
+Project [minute(to_time(23-59-58.987654, Some(HH-mm-ss.SSSSSS))) AS minute(to_time(23-59-58.987654, HH-mm-ss.SSSSSS))#x]
++- OneRowRelation
+
+
+-- !query
+select hour(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'))
+-- !query analysis
+Project [hour(to_time(23-59-58.987654, Some(HH-mm-ss.SSSSSS))) AS hour(to_time(23-59-58.987654, HH-mm-ss.SSSSSS))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(HOUR from time'00:59:00.987654')
+-- !query analysis
+Project [extract(HOUR, 00:59:00.987654) AS extract(HOUR FROM TIME '00:59:00.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(H from time'23:59:00.987654')
+-- !query analysis
+Project [extract(H, 23:59:00.987654) AS extract(H FROM TIME '23:59:00.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(HOURS from time'00:00:00')
+-- !query analysis
+Project [extract(HOURS, 00:00:00) AS extract(HOURS FROM TIME '00:00:00')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(HR from time'23:59:59.999999')
+-- !query analysis
+Project [extract(HR, 23:59:59.999999) AS extract(HR FROM TIME '23:59:59.999999')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(HRS from time'23:59:58.987654')
+-- !query analysis
+Project [extract(HRS, 23:59:58.987654) AS extract(HRS FROM TIME '23:59:58.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(MINUTE from time'00:59:00.987654')
+-- !query analysis
+Project [extract(MINUTE, 00:59:00.987654) AS extract(MINUTE FROM TIME '00:59:00.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(M from time'23:59:00.987654')
+-- !query analysis
+Project [extract(M, 23:59:00.987654) AS extract(M FROM TIME '23:59:00.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(M from time'23:00:59.987654')
+-- !query analysis
+Project [extract(M, 23:00:59.987654) AS extract(M FROM TIME '23:00:59.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(MIN from time'00:00:00')
+-- !query analysis
+Project [extract(MIN, 00:00:00) AS extract(MIN FROM TIME '00:00:00')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(MINS from time'23:59:59.999999')
+-- !query analysis
+Project [extract(MINS, 23:59:59.999999) AS extract(MINS FROM TIME '23:59:59.999999')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(MINUTES from time'23:59:58.987654')
+-- !query analysis
+Project [extract(MINUTES, 23:59:58.987654) AS extract(MINUTES FROM TIME '23:59:58.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND from time'00:59:00.987654')
+-- !query analysis
+Project [extract(SECOND, 00:59:00.987654) AS extract(SECOND FROM TIME '00:59:00.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND from time'00:0:00.000001')
+-- !query analysis
+Project [extract(SECOND, 00:00:00.000001) AS extract(SECOND FROM TIME '00:00:00.000001')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND from time'00:0:00.100000')
+-- !query analysis
+Project [extract(SECOND, 00:00:00.1) AS extract(SECOND FROM TIME '00:00:00.1')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND from time'00:0:00.100001')
+-- !query analysis
+Project [extract(SECOND, 00:00:00.100001) AS extract(SECOND FROM TIME '00:00:00.100001')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(S from time'23:59:00.987654')
+-- !query analysis
+Project [extract(S, 23:59:00.987654) AS extract(S FROM TIME '23:59:00.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SEC from time'00:00:00')
+-- !query analysis
+Project [extract(SEC, 00:00:00) AS extract(SEC FROM TIME '00:00:00')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SEC from time'00:00:00.000000')
+-- !query analysis
+Project [extract(SEC, 00:00:00) AS extract(SEC FROM TIME '00:00:00')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECONDS from time'23:59:59.999999')
+-- !query analysis
+Project [extract(SECONDS, 23:59:59.999999) AS extract(SECONDS FROM TIME '23:59:59.999999')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECS from time'23:59:58.987654')
+-- !query analysis
+Project [extract(SECS, 23:59:58.987654) AS extract(SECS FROM TIME '23:59:58.987654')#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(0)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(0))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(0)))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(1)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(1))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(1)))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(2)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(2))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(2)))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(3)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(3))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(3)))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(4)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(4))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(4)))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(5)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(5))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(5)))#x]
++- OneRowRelation
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(6)))
+-- !query analysis
+Project [extract(SECOND, cast(09:08:01.987654 as time(6))) AS extract(SECOND FROM CAST(09:08:01.987654 AS TIME(6)))#x]
++- OneRowRelation
diff --git a/sql/core/src/test/resources/sql-tests/inputs/time.sql b/sql/core/src/test/resources/sql-tests/inputs/time.sql
index 6930c5d24c14..3d0ea48a4e05 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/time.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/time.sql
@@ -30,4 +30,40 @@ select make_time(1, 18, 60.0);
select make_time(1, 18, 9999999999.999999);
select make_time(1, 18, -999999999.999999);
-- Full seconds overflows to a valid seconds integer when converted from long to int
-select make_time(1, 18, 4294967297.999999);
\ No newline at end of file
+select make_time(1, 18, 4294967297.999999);
+
+select second(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'));
+select minute(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'));
+select hour(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'));
+
+select extract(HOUR from time'00:59:00.987654');
+select extract(H from time'23:59:00.987654');
+select extract(HOURS from time'00:00:00');
+select extract(HR from time'23:59:59.999999');
+select extract(HRS from time'23:59:58.987654');
+
+select extract(MINUTE from time'00:59:00.987654');
+select extract(M from time'23:59:00.987654');
+select extract(M from time'23:00:59.987654');
+select extract(MIN from time'00:00:00');
+select extract(MINS from time'23:59:59.999999');
+select extract(MINUTES from time'23:59:58.987654');
+
+select extract(SECOND from time'00:59:00.987654');
+select extract(SECOND from time'00:0:00.000001');
+select extract(SECOND from time'00:0:00.100000');
+select extract(SECOND from time'00:0:00.100001');
+select extract(S from time'23:59:00.987654');
+select extract(SEC from time'00:00:00');
+select extract(SEC from time'00:00:00.000000');
+select extract(SECONDS from time'23:59:59.999999');
+select extract(SECS from time'23:59:58.987654');
+
+-- test with precisions
+select extract(SECOND FROM cast('09:08:01.987654' as time(0)));
+select extract(SECOND FROM cast('09:08:01.987654' as time(1)));
+select extract(SECOND FROM cast('09:08:01.987654' as time(2)));
+select extract(SECOND FROM cast('09:08:01.987654' as time(3)));
+select extract(SECOND FROM cast('09:08:01.987654' as time(4)));
+select extract(SECOND FROM cast('09:08:01.987654' as time(5)));
+select extract(SECOND FROM cast('09:08:01.987654' as time(6)));
diff --git a/sql/core/src/test/resources/sql-tests/results/time.sql.out b/sql/core/src/test/resources/sql-tests/results/time.sql.out
index b401d2dd4561..efe53559a7e0 100644
--- a/sql/core/src/test/resources/sql-tests/results/time.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/time.sql.out
@@ -258,3 +258,243 @@ org.apache.spark.SparkDateTimeException
"rangeMessage" : "Invalid value for SecondOfMinute (valid values 0 - 59): 4294967297"
}
}
+
+
+-- !query
+select second(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'))
+-- !query schema
+struct<second(to_time(23-59-58.987654, HH-mm-ss.SSSSSS)):int>
+-- !query output
+58
+
+
+-- !query
+select minute(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'))
+-- !query schema
+struct<minute(to_time(23-59-58.987654, HH-mm-ss.SSSSSS)):int>
+-- !query output
+59
+
+
+-- !query
+select hour(to_time('23-59-58.987654', 'HH-mm-ss.SSSSSS'))
+-- !query schema
+struct<hour(to_time(23-59-58.987654, HH-mm-ss.SSSSSS)):int>
+-- !query output
+23
+
+
+-- !query
+select extract(HOUR from time'00:59:00.987654')
+-- !query schema
+struct<extract(HOUR FROM TIME '00:59:00.987654'):int>
+-- !query output
+0
+
+
+-- !query
+select extract(H from time'23:59:00.987654')
+-- !query schema
+struct<extract(H FROM TIME '23:59:00.987654'):int>
+-- !query output
+23
+
+
+-- !query
+select extract(HOURS from time'00:00:00')
+-- !query schema
+struct<extract(HOURS FROM TIME '00:00:00'):int>
+-- !query output
+0
+
+
+-- !query
+select extract(HR from time'23:59:59.999999')
+-- !query schema
+struct<extract(HR FROM TIME '23:59:59.999999'):int>
+-- !query output
+23
+
+
+-- !query
+select extract(HRS from time'23:59:58.987654')
+-- !query schema
+struct<extract(HRS FROM TIME '23:59:58.987654'):int>
+-- !query output
+23
+
+
+-- !query
+select extract(MINUTE from time'00:59:00.987654')
+-- !query schema
+struct<extract(MINUTE FROM TIME '00:59:00.987654'):int>
+-- !query output
+59
+
+
+-- !query
+select extract(M from time'23:59:00.987654')
+-- !query schema
+struct<extract(M FROM TIME '23:59:00.987654'):int>
+-- !query output
+59
+
+
+-- !query
+select extract(M from time'23:00:59.987654')
+-- !query schema
+struct<extract(M FROM TIME '23:00:59.987654'):int>
+-- !query output
+0
+
+
+-- !query
+select extract(MIN from time'00:00:00')
+-- !query schema
+struct<extract(MIN FROM TIME '00:00:00'):int>
+-- !query output
+0
+
+
+-- !query
+select extract(MINS from time'23:59:59.999999')
+-- !query schema
+struct<extract(MINS FROM TIME '23:59:59.999999'):int>
+-- !query output
+59
+
+
+-- !query
+select extract(MINUTES from time'23:59:58.987654')
+-- !query schema
+struct<extract(MINUTES FROM TIME '23:59:58.987654'):int>
+-- !query output
+59
+
+
+-- !query
+select extract(SECOND from time'00:59:00.987654')
+-- !query schema
+struct<extract(SECOND FROM TIME '00:59:00.987654'):decimal(8,6)>
+-- !query output
+0.987654
+
+
+-- !query
+select extract(SECOND from time'00:0:00.000001')
+-- !query schema
+struct<extract(SECOND FROM TIME '00:00:00.000001'):decimal(8,6)>
+-- !query output
+0.000001
+
+
+-- !query
+select extract(SECOND from time'00:0:00.100000')
+-- !query schema
+struct<extract(SECOND FROM TIME '00:00:00.1'):decimal(8,6)>
+-- !query output
+0.100000
+
+
+-- !query
+select extract(SECOND from time'00:0:00.100001')
+-- !query schema
+struct<extract(SECOND FROM TIME '00:00:00.100001'):decimal(8,6)>
+-- !query output
+0.100001
+
+
+-- !query
+select extract(S from time'23:59:00.987654')
+-- !query schema
+struct<extract(S FROM TIME '23:59:00.987654'):decimal(8,6)>
+-- !query output
+0.987654
+
+
+-- !query
+select extract(SEC from time'00:00:00')
+-- !query schema
+struct<extract(SEC FROM TIME '00:00:00'):decimal(8,6)>
+-- !query output
+0.000000
+
+
+-- !query
+select extract(SEC from time'00:00:00.000000')
+-- !query schema
+struct<extract(SEC FROM TIME '00:00:00'):decimal(8,6)>
+-- !query output
+0.000000
+
+
+-- !query
+select extract(SECONDS from time'23:59:59.999999')
+-- !query schema
+struct<extract(SECONDS FROM TIME '23:59:59.999999'):decimal(8,6)>
+-- !query output
+59.999999
+
+
+-- !query
+select extract(SECS from time'23:59:58.987654')
+-- !query schema
+struct<extract(SECS FROM TIME '23:59:58.987654'):decimal(8,6)>
+-- !query output
+58.987654
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(0)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(0))):decimal(8,6)>
+-- !query output
+1.000000
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(1)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(1))):decimal(8,6)>
+-- !query output
+1.900000
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(2)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(2))):decimal(8,6)>
+-- !query output
+1.980000
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(3)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(3))):decimal(8,6)>
+-- !query output
+1.987000
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(4)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(4))):decimal(8,6)>
+-- !query output
+1.987600
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(5)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(5))):decimal(8,6)>
+-- !query output
+1.987650
+
+
+-- !query
+select extract(SECOND FROM cast('09:08:01.987654' as time(6)))
+-- !query schema
+struct<extract(SECOND FROM CAST(09:08:01.987654 AS TIME(6))):decimal(8,6)>
+-- !query output
+1.987654
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org