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