This is an automated email from the ASF dual-hosted git repository.
wenchen 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 4a62f7509c50 [SPARK-53319][SQL] Support the time type by
try_make_timestamp_ltz()
4a62f7509c50 is described below
commit 4a62f7509c50018bd5928b0af9c5cb8ef739f29e
Author: Uros Bojanic <[email protected]>
AuthorDate: Thu Oct 23 01:01:12 2025 +0800
[SPARK-53319][SQL] Support the time type by try_make_timestamp_ltz()
### What changes were proposed in this pull request?
In the PR, I propose to extend the `try_make_timestamp_ltz` function, and
accept a date and time fields.
**Syntax**
```
try_make_timestamp_ltz(date[, time])
```
**Arguments**
- `date`: A date expression.
- `time`: A time expression.
**Returns**
A `TIMESTAMP_LTZ`.
Examples
```
> SELECT try_make_timestamp_ltz(DATE'2014-12-28', TIME'6:30:45.887');
2014-12-28 06:30:45.887
```
### Why are the changes needed?
Users will be able to create a timestamp with local time zone by combining
a time and a date.
### Does this PR introduce _any_ user-facing change?
Yes, this extends `try_make_timestamp_ltz` to accept additional kinds of
inputs.
### How was this patch tested?
Added new e2e SQL tests in corresponding golden files.
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #52063 from uros-db/try_make_timestamp_ltz.
Lead-authored-by: Uros Bojanic <[email protected]>
Co-authored-by: Wenchen Fan <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../catalyst/expressions/datetimeExpressions.scala | 21 ++++-
.../analyzer-results/timestamp-ltz.sql.out | 81 +++++++++++++++++++
.../resources/sql-tests/inputs/timestamp-ltz.sql | 12 +++
.../sql-tests/results/timestamp-ltz.sql.out | 92 ++++++++++++++++++++++
4 files changed, 204 insertions(+), 2 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 3948f8bd0dd6..226e098165b8 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
@@ -2806,7 +2806,11 @@ object MakeTimestampLTZExpressionBuilder extends
ExpressionBuilder {
// scalastyle:off line.size.limit
@ExpressionDescription(
- usage = "_FUNC_(year, month, day, hour, min, sec[, timezone]) - Try to
create the current timestamp with local time zone from year, month, day, hour,
min, sec and timezone fields. The function returns NULL on invalid inputs.",
+ usage = """
+ _FUNC_(year, month, day, hour, min, sec[, timezone]) - Try to create the
current timestamp with local time zone from year, month, day, hour, min, sec
and (optional) timezone fields. The function returns NULL on invalid inputs.
+
+ _FUNC_(date, time[, timezone]) - Try to create the current timestamp with
local time zone from date, time and (optional) timezone fields.
+ """,
arguments = """
Arguments:
* year - the year to represent, from 1 to 9999
@@ -2818,6 +2822,8 @@ object MakeTimestampLTZExpressionBuilder extends
ExpressionBuilder {
0 to 60. If the sec argument equals to 60, the seconds field is
set
to 0 and 1 minute is added to the final timestamp.
* timezone - the time zone identifier. For example, CET, UTC and etc.
+ * date - a date to represent, from 0001-01-01 to 9999-12-31
+ * time - a local time to represent, from 00:00:00 to 23:59:59.999999
""",
examples = """
Examples:
@@ -2831,6 +2837,10 @@ object MakeTimestampLTZExpressionBuilder extends
ExpressionBuilder {
NULL
> SELECT _FUNC_(2024, 13, 22, 15, 30, 0);
NULL
+ > SELECT _FUNC_(DATE'2014-12-28', TIME'6:30:45.887');
+ 2014-12-28 06:30:45.887
+ > SELECT _FUNC_(DATE'2014-12-28', TIME'6:30:45.887', 'CET');
+ 2014-12-27 21:30:45.887
""",
group = "datetime_funcs",
since = "4.0.0")
@@ -2838,7 +2848,14 @@ object MakeTimestampLTZExpressionBuilder extends
ExpressionBuilder {
object TryMakeTimestampLTZExpressionBuilder extends ExpressionBuilder {
override def build(funcName: String, expressions: Seq[Expression]):
Expression = {
val numArgs = expressions.length
- if (numArgs == 6 || numArgs == 7) {
+ if (numArgs == 2 || numArgs == 3) {
+ // Overload for: date, time[, timezone].
+ MakeTimestampFromDateTime(
+ expressions(0),
+ Some(expressions(1)),
+ expressions.drop(2).lastOption
+ )
+ } else if (numArgs == 6 || numArgs == 7) {
MakeTimestamp(
expressions(0),
expressions(1),
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz.sql.out
index 73fbade579c5..cc6ff0da5f79 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz.sql.out
@@ -132,3 +132,84 @@ Project [make_timestamp(make_date(2021, 7, 11, true),
Some(make_time(6, 30, cast
SELECT convert_timezone('Europe/Brussels', timestamp_ltz'2022-03-23 00:00:00
America/Los_Angeles')
-- !query analysis
[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678))
+-- !query analysis
+Project [make_timestamp(make_date(2021, 7, 11, true), Some(make_time(6, 30,
cast(45.678 as decimal(16,6)))), None, Some(America/Los_Angeles)) AS
make_timestamp(make_date(2021, 7, 11), make_time(6, 30, 45.678))#x]
++- OneRowRelation
+
+
+-- !query
+SELECT try_make_timestamp_ltz(NULL, TIME'00:00:00')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT try_make_timestamp_ltz(DATE'1970-01-01', NULL)
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+SELECT try_make_timestamp_ltz(timestamp_ntz'2018-11-17 13:33:33', TIME'0:0:0')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"TIMESTAMP_NTZ '2018-11-17 13:33:33'\"",
+ "inputType" : "\"TIMESTAMP_NTZ\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"DATE\"",
+ "sqlExpr" : "\"make_timestamp(TIMESTAMP_NTZ '2018-11-17 13:33:33', TIME
'00:00:00')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 78,
+ "fragment" : "try_make_timestamp_ltz(timestamp_ntz'2018-11-17 13:33:33',
TIME'0:0:0')"
+ } ]
+}
+
+
+-- !query
+SELECT try_make_timestamp_ltz(DATE'2025-06-20', timestamp_ntz'2018-11-17
13:33:33')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"TIMESTAMP_NTZ '2018-11-17 13:33:33'\"",
+ "inputType" : "\"TIMESTAMP_NTZ\"",
+ "paramIndex" : "second",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"make_timestamp(DATE '2025-06-20', TIMESTAMP_NTZ '2018-11-17
13:33:33')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 83,
+ "fragment" : "try_make_timestamp_ltz(DATE'2025-06-20',
timestamp_ntz'2018-11-17 13:33:33')"
+ } ]
+}
+
+
+-- !query
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678), 'PST')
+-- !query analysis
+Project [make_timestamp(make_date(2021, 7, 11, true), Some(make_time(6, 30,
cast(45.678 as decimal(16,6)))), Some(PST), Some(America/Los_Angeles)) AS
make_timestamp(make_date(2021, 7, 11), make_time(6, 30, 45.678), PST)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678), 'CET')
+-- !query analysis
+Project [make_timestamp(make_date(2021, 7, 11, true), Some(make_time(6, 30,
cast(45.678 as decimal(16,6)))), Some(CET), Some(America/Los_Angeles)) AS
make_timestamp(make_date(2021, 7, 11), make_time(6, 30, 45.678), CET)#x]
++- OneRowRelation
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz.sql
index c068e35dbd92..6484383eabc0 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz.sql
@@ -28,3 +28,15 @@ SELECT make_timestamp_ltz(make_date(2021, 07, 11),
make_time(6, 30, 45.678), 'PS
SELECT make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30, 45.678),
'CET');
SELECT convert_timezone('Europe/Brussels', timestamp_ltz'2022-03-23 00:00:00
America/Los_Angeles');
+
+-- Try TimestampLTZ date/time fields constructor
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678));
+-- Handling NULL input.
+SELECT try_make_timestamp_ltz(NULL, TIME'00:00:00');
+SELECT try_make_timestamp_ltz(DATE'1970-01-01', NULL);
+-- Handling invalid input.
+SELECT try_make_timestamp_ltz(timestamp_ntz'2018-11-17 13:33:33', TIME'0:0:0');
+SELECT try_make_timestamp_ltz(DATE'2025-06-20', timestamp_ntz'2018-11-17
13:33:33');
+-- Optional timezone is ignored.
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678), 'PST');
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678), 'CET');
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz.sql.out
index 69f9213cfc23..6ec411a790eb 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz.sql.out
@@ -160,3 +160,95 @@ SELECT convert_timezone('Europe/Brussels',
timestamp_ltz'2022-03-23 00:00:00 Ame
struct<convert_timezone(current_timezone(), Europe/Brussels, TIMESTAMP
'2022-03-23 00:00:00'):timestamp_ntz>
-- !query output
2022-03-23 08:00:00
+
+
+-- !query
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678))
+-- !query schema
+struct<make_timestamp(make_date(2021, 7, 11), make_time(6, 30,
45.678)):timestamp>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT try_make_timestamp_ltz(NULL, TIME'00:00:00')
+-- !query schema
+struct<make_timestamp(NULL, TIME '00:00:00'):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+SELECT try_make_timestamp_ltz(DATE'1970-01-01', NULL)
+-- !query schema
+struct<make_timestamp(DATE '1970-01-01', NULL):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+SELECT try_make_timestamp_ltz(timestamp_ntz'2018-11-17 13:33:33', TIME'0:0:0')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"TIMESTAMP_NTZ '2018-11-17 13:33:33'\"",
+ "inputType" : "\"TIMESTAMP_NTZ\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"DATE\"",
+ "sqlExpr" : "\"make_timestamp(TIMESTAMP_NTZ '2018-11-17 13:33:33', TIME
'00:00:00')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 78,
+ "fragment" : "try_make_timestamp_ltz(timestamp_ntz'2018-11-17 13:33:33',
TIME'0:0:0')"
+ } ]
+}
+
+
+-- !query
+SELECT try_make_timestamp_ltz(DATE'2025-06-20', timestamp_ntz'2018-11-17
13:33:33')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"TIMESTAMP_NTZ '2018-11-17 13:33:33'\"",
+ "inputType" : "\"TIMESTAMP_NTZ\"",
+ "paramIndex" : "second",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"make_timestamp(DATE '2025-06-20', TIMESTAMP_NTZ '2018-11-17
13:33:33')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 83,
+ "fragment" : "try_make_timestamp_ltz(DATE'2025-06-20',
timestamp_ntz'2018-11-17 13:33:33')"
+ } ]
+}
+
+
+-- !query
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678), 'PST')
+-- !query schema
+struct<make_timestamp(make_date(2021, 7, 11), make_time(6, 30, 45.678),
PST):timestamp>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT try_make_timestamp_ltz(make_date(2021, 07, 11), make_time(6, 30,
45.678), 'CET')
+-- !query schema
+struct<make_timestamp(make_date(2021, 7, 11), make_time(6, 30, 45.678),
CET):timestamp>
+-- !query output
+2021-07-10 21:30:45.678
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
