You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "the-sakthi (via GitHub)" <gi...@apache.org> on 2025/04/16 11:00:16 UTC
[PR] [WIP][SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
the-sakthi opened a new pull request, #50607:
URL: https://github.com/apache/spark/pull/50607
### What changes were proposed in this pull request?
- Added a new built-in function `time_trunc(unit, expr)` that returns a `TIME` value truncated to the specified unit.
- Allowed input for expr to be either a `TIME` type or a string that can be cast to TIME.
- Supported truncation units are `HOUR`, `MINUTE`, `SECOND`, `MILLISECOND`, and `MICROSECOND`.
- Handles both foldable and non-foldable inputs
### Why are the changes needed?
- Spark currently lacks a built-in function for truncating TIME values in a similar way as `truncTimestamp`.
### Does this PR introduce _any_ user-facing change?
Yes. A new built-in function `time_trunc` is added. Users can call the function to truncate TIME values to one of the above mentioned supported units.
### How was this patch tested?
By running new tests: WIP. Adding new UTs.
By manual tests:
```bash
# Happy test cases
scala> spark.sql("SELECT time_trunc('HOUR', '09:32:05.123456');").show()
+---------------------------------+
|time_trunc(HOUR, 09:32:05.123456)|
+---------------------------------+
| 09:00:00|
+---------------------------------+
scala> spark.sql("SELECT time_trunc('MINUTE', TIME'09:32:05.123456');").show()
+------------------------------------------+
|time_trunc(MINUTE, TIME '09:32:05.123456')|
+------------------------------------------+
| 09:32:00|
+------------------------------------------+
scala> spark.sql("SELECT time_trunc('second', '09:32:05.123456');").show()
+-----------------------------------+
|time_trunc(second, 09:32:05.123456)|
+-----------------------------------+
| 09:32:05|
+-----------------------------------+
scala> spark.sql("SELECT time_trunc('MILLISECOND', '09:32:05.123456');").show()
+----------------------------------------+
|time_trunc(MILLISECOND, 09:32:05.123456)|
+----------------------------------------+
| 09:32:05.123|
+----------------------------------------+
scala> spark.sql("SELECT time_trunc('MICROSECOND', '09:32:05.123456');").show()
+----------------------------------------+
|time_trunc(MICROSECOND, 09:32:05.123456)|
+----------------------------------------+
| 09:32:05.123456|
+----------------------------------------+
scala> spark.sql("SELECT time_trunc('MICROSECOND', '09:32:05.1234');").show()
+--------------------------------------+
|time_trunc(MICROSECOND, 09:32:05.1234)|
+--------------------------------------+
| 09:32:05.1234|
+--------------------------------------+
# Invalid inputs
scala> spark.sql("SELECT time_trunc('MS', '09:32:05.123456');").show()
+-------------------------------+
|time_trunc(MS, 09:32:05.123456)|
+-------------------------------+
| NULL|
+-------------------------------+
scala> spark.sql("SELECT time_trunc('MICROSECOND', '29:32:05.123456');").show()
org.apache.spark.SparkDateTimeException: [CAST_INVALID_INPUT] The value '29:32:05.123456' of the type "STRING" cannot be cast to "TIME(6)" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. SQLSTATE: 22018
== SQL (line 1, position 8) ==
SELECT time_trunc('MICROSECOND', '29:32:05.123456');
# unfoldable inputs
scala> val df = Seq(
| ("HOUR", "09:32:05.123456"),
| ("MINUTE", "10:20:15.123456"),
| ("second", "11:59:59.999999"),
| ("MILLISECOND","00:00:00.123000"),
| ("MICROSECOND","00:00:00.123000"),
| ("MICROSECOND","00:00:00.123456")
| ).toDF("unitcol", "timecol")
val df: org.apache.spark.sql.DataFrame = [unitcol: string, timecol: string]
scala> val timeDf = df.selectExpr("unitcol", "CAST(timecol AS TIME(6)) as timeval")
val timeDf: org.apache.spark.sql.DataFrame = [unitcol: string, timeval: time(6)]
scala> timeDf.createOrReplaceTempView("tmp")
scala> spark.sql("""
| SELECT
| unitcol,
| timeval,
| time_trunc(unitcol, timeval) as truncated
| FROM tmp
| """).show(false)
+-----------+---------------+---------------+
|unitcol |timeval |truncated |
+-----------+---------------+---------------+
|HOUR |09:32:05.123456|09:00:00 |
|MINUTE |10:20:15.123456|10:20:00 |
|second |11:59:59.999999|11:59:59 |
|MILLISECOND|00:00:00.123 |00:00:00.123 |
|MICROSECOND|00:00:00.123 |00:00:00.123 |
|MICROSECOND|00:00:00.123456|00:00:00.123456|
+-----------+---------------+---------------+
```
### Was this patch authored or co-authored using generative AI tooling?
No.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #50607: [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype
URL: https://github.com/apache/spark/pull/50607
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #50607:
URL: https://github.com/apache/spark/pull/50607#issuecomment-3470810263
We're closing this PR because it hasn't been updated in a while. This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable.
If you'd like to revive this PR, please reopen it and ask a committer to remove the Stale tag!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "MaxGekk (via GitHub)" <gi...@apache.org>.
MaxGekk commented on code in PR #50607:
URL: https://github.com/apache/spark/pull/50607#discussion_r2138399816
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala:
##########
@@ -351,7 +351,7 @@ case class SecondsOfTime(child: Expression)
)
override def inputTypes: Seq[AbstractDataType] =
- Seq(TypeCollection(TimeType.MIN_PRECISION to TimeType.MAX_PRECISION map TimeType.apply: _*))
+ Seq(TypeCollection(TimeType.MIN_PRECISION to TimeType.MAX_PRECISION map TimeType: _*))
Review Comment:
Please, revert it back otherwise it causes a compilation warning, see https://github.com/apache/spark/pull/50692
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala:
##########
@@ -548,3 +548,77 @@ case class MakeTime(
override protected def withNewChildrenInternal(newChildren: IndexedSeq[Expression]): MakeTime =
copy(hours = newChildren(0), minutes = newChildren(1), secsAndMicros = newChildren(2))
}
+
+// scalastyle:off line.size.limit
+@ExpressionDescription(
+ usage = """
+ _FUNC_(unit, expr) - Returns a TIME value representing `expr` truncated to the specified time unit.
+ """,
+ arguments = """
+ Arguments:
+ * unit - A STRING literal (case-insensitive) indicating the unit to truncate to.
+ Valid values are 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', and 'MICROSECOND'.
+ If the unit is not recognized, the function returns NULL.
+ * expr - A TIME value, or a STRING that can be cast to TIME.
+ """,
+ examples = """
+ Examples:
+ > SELECT _FUNC_('HOUR', '09:32:05.359');
+ 09:00:00
+ > SELECT _FUNC_('MILLISECOND', '09:32:05.123456');
+ 09:32:05.123
+ > SELECT _FUNC_('MS', '09:32:05.123456');
+ NULL
+ """,
+ since = "4.1.0",
+ group = "datetime_funcs")
+// scalastyle:on line.size.limit
+case class TruncateTime(unit: Expression, timeExpr: Expression)
+ extends BinaryExpression with ImplicitCastInputTypes with RuntimeReplaceable {
+
+ override def nullable: Boolean = true
+ override def left: Expression = unit
+ override def right: Expression = timeExpr
+
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(
+ StringType,
+ TypeCollection(TimeType.MIN_PRECISION to TimeType.MAX_PRECISION map TimeType: _*)
Review Comment:
Could you double check that it won't produce warnings.
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala:
##########
@@ -532,6 +534,61 @@ object DateTimeUtils extends SparkDateTimeUtils {
}
}
+ /**
+ * Truncate a TIME-of-day value given as microseconds from midnight.
+ *
+ * @param unitUtf8 the requested unit ('HOUR', 'MINUTE', ...) or null
+ * @param micros microseconds since 00:00:00
+ * @return truncated value as microseconds-of-day, or -1L if the unit is invalid
+ */
+ def truncateTime(unitUtf8: UTF8String, micros: Long): Long = {
+ val level = parseTruncLevelForTime(unitUtf8)
+ if (level < MIN_LEVEL_OF_TIME_TRUNC) { // unknown unit
+ -1L
+ } else {
+ val out = truncTime(micros, level) // may still return -1L on error
Review Comment:
What's the reason of splitting the function? Is `truncTime` used somewhere else?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on PR #50607:
URL: https://github.com/apache/spark/pull/50607#issuecomment-3104144644
@MaxGekk can I get a review here please?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on code in PR #50607:
URL: https://github.com/apache/spark/pull/50607#discussion_r2139443598
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala:
##########
@@ -351,7 +351,7 @@ case class SecondsOfTime(child: Expression)
)
override def inputTypes: Seq[AbstractDataType] =
- Seq(TypeCollection(TimeType.MIN_PRECISION to TimeType.MAX_PRECISION map TimeType.apply: _*))
+ Seq(TypeCollection(TimeType.MIN_PRECISION to TimeType.MAX_PRECISION map TimeType: _*))
Review Comment:
Sure will do!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on PR #50607:
URL: https://github.com/apache/spark/pull/50607#issuecomment-2947031955
Hey @MaxGekk , could I get a review here, please? Looking forward to the comments.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [WIP][SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on PR #50607:
URL: https://github.com/apache/spark/pull/50607#issuecomment-2809226278
@MaxGekk While I add the UTs for this, would appreciate any feedbacks from you on this, meanwhile!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on PR #50607:
URL: https://github.com/apache/spark/pull/50607#issuecomment-2838213673
@MaxGekk Please do let me know your thoughts on this one! Have updated the revision.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on PR #50607:
URL: https://github.com/apache/spark/pull/50607#issuecomment-2854621496
@MaxGekk Any chance I can get a review here, please?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-51554][SQL] Add the time_trunc() function for TIME datatype [spark]
Posted by "the-sakthi (via GitHub)" <gi...@apache.org>.
the-sakthi commented on code in PR #50607:
URL: https://github.com/apache/spark/pull/50607#discussion_r2139445372
##########
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/timeExpressions.scala:
##########
@@ -548,3 +548,77 @@ case class MakeTime(
override protected def withNewChildrenInternal(newChildren: IndexedSeq[Expression]): MakeTime =
copy(hours = newChildren(0), minutes = newChildren(1), secsAndMicros = newChildren(2))
}
+
+// scalastyle:off line.size.limit
+@ExpressionDescription(
+ usage = """
+ _FUNC_(unit, expr) - Returns a TIME value representing `expr` truncated to the specified time unit.
+ """,
+ arguments = """
+ Arguments:
+ * unit - A STRING literal (case-insensitive) indicating the unit to truncate to.
+ Valid values are 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND', and 'MICROSECOND'.
+ If the unit is not recognized, the function returns NULL.
+ * expr - A TIME value, or a STRING that can be cast to TIME.
+ """,
+ examples = """
+ Examples:
+ > SELECT _FUNC_('HOUR', '09:32:05.359');
+ 09:00:00
+ > SELECT _FUNC_('MILLISECOND', '09:32:05.123456');
+ 09:32:05.123
+ > SELECT _FUNC_('MS', '09:32:05.123456');
+ NULL
+ """,
+ since = "4.1.0",
+ group = "datetime_funcs")
+// scalastyle:on line.size.limit
+case class TruncateTime(unit: Expression, timeExpr: Expression)
+ extends BinaryExpression with ImplicitCastInputTypes with RuntimeReplaceable {
+
+ override def nullable: Boolean = true
+ override def left: Expression = unit
+ override def right: Expression = timeExpr
+
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(
+ StringType,
+ TypeCollection(TimeType.MIN_PRECISION to TimeType.MAX_PRECISION map TimeType: _*)
Review Comment:
Will check!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org