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 b9fc5c03ed6 [SPARK-44778][SQL] Add the alias `TIMEDIFF` for
`TIMESTAMPDIFF`
b9fc5c03ed6 is described below
commit b9fc5c03ed69e91d9c4cbe7ff5a1522c7b849568
Author: Max Gekk <[email protected]>
AuthorDate: Sat Aug 12 11:08:39 2023 +0500
[SPARK-44778][SQL] Add the alias `TIMEDIFF` for `TIMESTAMPDIFF`
### What changes were proposed in this pull request?
In the PR, I propose to extend the rules of `primaryExpression` in
`SqlBaseParser.g4`, and one more function `TIMEDIFF` which accepts 3-args in
the same way as the existing expressions `TIMESTAMPDIFF`.
### Why are the changes needed?
To achieve feature parity w/ other system and make the migration to Spark
SQL from such systems easier:
1. Snowflake: https://docs.snowflake.com/en/sql-reference/functions/timediff
2. MySQL/MariaDB:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timediff
### Does this PR introduce _any_ user-facing change?
No.
### How was this patch tested?
By running the existing test suites:
```
$ PYSPARK_PYTHON=python3 build/sbt "sql/testOnly
org.apache.spark.sql.SQLQueryTestSuite"
```
Closes #42435 from MaxGekk/timediff.
Authored-by: Max Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
---
docs/sql-ref-ansi-compliance.md | 1 +
.../spark/sql/catalyst/parser/SqlBaseLexer.g4 | 1 +
.../spark/sql/catalyst/parser/SqlBaseParser.g4 | 4 +-
.../analyzer-results/ansi/timestamp.sql.out | 68 ++++++++++++++++++
.../analyzer-results/datetime-legacy.sql.out | 68 ++++++++++++++++++
.../sql-tests/analyzer-results/timestamp.sql.out | 68 ++++++++++++++++++
.../timestampNTZ/timestamp-ansi.sql.out | 70 +++++++++++++++++++
.../timestampNTZ/timestamp.sql.out | 70 +++++++++++++++++++
.../test/resources/sql-tests/inputs/timestamp.sql | 8 +++
.../sql-tests/results/ansi/keywords.sql.out | 1 +
.../sql-tests/results/ansi/timestamp.sql.out | 80 ++++++++++++++++++++++
.../sql-tests/results/datetime-legacy.sql.out | 80 ++++++++++++++++++++++
.../resources/sql-tests/results/keywords.sql.out | 1 +
.../resources/sql-tests/results/timestamp.sql.out | 80 ++++++++++++++++++++++
.../results/timestampNTZ/timestamp-ansi.sql.out | 80 ++++++++++++++++++++++
.../results/timestampNTZ/timestamp.sql.out | 80 ++++++++++++++++++++++
.../ThriftServerWithSparkContextSuite.scala | 2 +-
17 files changed, 760 insertions(+), 2 deletions(-)
diff --git a/docs/sql-ref-ansi-compliance.md b/docs/sql-ref-ansi-compliance.md
index f3a0e8f9afb..09c38a00995 100644
--- a/docs/sql-ref-ansi-compliance.md
+++ b/docs/sql-ref-ansi-compliance.md
@@ -636,6 +636,7 @@ Below is a list of all the keywords in Spark SQL.
|TERMINATED|non-reserved|non-reserved|non-reserved|
|THEN|reserved|non-reserved|reserved|
|TIME|reserved|non-reserved|reserved|
+|TIMEDIFF|non-reserved|non-reserved|non-reserved|
|TIMESTAMP|non-reserved|non-reserved|non-reserved|
|TIMESTAMP_LTZ|non-reserved|non-reserved|non-reserved|
|TIMESTAMP_NTZ|non-reserved|non-reserved|non-reserved|
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
index bf6370575a1..d9128de0f5d 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseLexer.g4
@@ -373,6 +373,7 @@ TEMPORARY: 'TEMPORARY' | 'TEMP';
TERMINATED: 'TERMINATED';
THEN: 'THEN';
TIME: 'TIME';
+TIMEDIFF: 'TIMEDIFF';
TIMESTAMP: 'TIMESTAMP';
TIMESTAMP_LTZ: 'TIMESTAMP_LTZ';
TIMESTAMP_NTZ: 'TIMESTAMP_NTZ';
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
index a45ebee3106..7a69b10dadb 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
@@ -953,7 +953,7 @@ datetimeUnit
primaryExpression
: name=(CURRENT_DATE | CURRENT_TIMESTAMP | CURRENT_USER | USER)
#currentLike
| name=(TIMESTAMPADD | DATEADD | DATE_ADD) LEFT_PAREN (unit=datetimeUnit |
invalidUnit=stringLit) COMMA unitsAmount=valueExpression COMMA
timestamp=valueExpression RIGHT_PAREN #timestampadd
- | name=(TIMESTAMPDIFF | DATEDIFF | DATE_DIFF) LEFT_PAREN
(unit=datetimeUnit | invalidUnit=stringLit) COMMA
startTimestamp=valueExpression COMMA endTimestamp=valueExpression RIGHT_PAREN
#timestampdiff
+ | name=(TIMESTAMPDIFF | DATEDIFF | DATE_DIFF | TIMEDIFF) LEFT_PAREN
(unit=datetimeUnit | invalidUnit=stringLit) COMMA
startTimestamp=valueExpression COMMA endTimestamp=valueExpression RIGHT_PAREN
#timestampdiff
| CASE whenClause+ (ELSE elseExpression=expression)? END
#searchedCase
| CASE value=expression whenClause+ (ELSE elseExpression=expression)? END
#simpleCase
| name=(CAST | TRY_CAST) LEFT_PAREN expression AS dataType RIGHT_PAREN
#cast
@@ -1511,6 +1511,7 @@ ansiNonReserved
| TBLPROPERTIES
| TEMPORARY
| TERMINATED
+ | TIMEDIFF
| TIMESTAMP
| TIMESTAMP_LTZ
| TIMESTAMP_NTZ
@@ -1853,6 +1854,7 @@ nonReserved
| TERMINATED
| THEN
| TIME
+ | TIMEDIFF
| TIMESTAMP
| TIMESTAMP_LTZ
| TIMESTAMP_NTZ
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/ansi/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/ansi/timestamp.sql.out
index d06813dc675..49e160a6cd4 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/ansi/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/ansi/timestamp.sql.out
@@ -875,3 +875,71 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/datetime-legacy.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/datetime-legacy.sql.out
index 8ae2893a0a8..1243a03fcf0 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/datetime-legacy.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/datetime-legacy.sql.out
@@ -1978,3 +1978,71 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp.sql.out
index 12fa38ac3c3..0661cd045e4 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp.sql.out
@@ -947,3 +947,71 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp-ansi.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp-ansi.sql.out
index bbdbd903d82..827bc8ddeea 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp-ansi.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp-ansi.sql.out
@@ -894,3 +894,73 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query analysis
+Project [timestampdiff(QUARTER, cast(2023-08-10 01:02:03 as timestamp),
cast(2022-01-14 01:02:03 as timestamp), Some(America/Los_Angeles)) AS
timestampdiff(QUARTER, TIMESTAMP_NTZ '2023-08-10 01:02:03', TIMESTAMP_NTZ
'2022-01-14 01:02:03')#xL]
++- OneRowRelation
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query analysis
+Project [timestampdiff(HOUR, cast(2022-02-14 01:02:03 as timestamp),
cast(2022-02-14 12:00:03 as timestamp), Some(America/Los_Angeles)) AS
timestampdiff(HOUR, TIMESTAMP_NTZ '2022-02-14 01:02:03', TIMESTAMP_NTZ
'2022-02-14 12:00:03')#xL]
++- OneRowRelation
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
index 720ab45acd7..a6b61b3957c 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestampNTZ/timestamp.sql.out
@@ -954,3 +954,73 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query analysis
+Project [timestampdiff(QUARTER, cast(2023-08-10 01:02:03 as timestamp),
cast(2022-01-14 01:02:03 as timestamp), Some(America/Los_Angeles)) AS
timestampdiff(QUARTER, TIMESTAMP_NTZ '2023-08-10 01:02:03', TIMESTAMP_NTZ
'2022-01-14 01:02:03')#xL]
++- OneRowRelation
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query analysis
+Project [timestampdiff(HOUR, cast(2022-02-14 01:02:03 as timestamp),
cast(2022-02-14 12:00:03 as timestamp), Some(America/Los_Angeles)) AS
timestampdiff(HOUR, TIMESTAMP_NTZ '2022-02-14 01:02:03', TIMESTAMP_NTZ
'2022-02-14 12:00:03')#xL]
++- OneRowRelation
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query analysis
+[Analyzer test output redacted due to nondeterminism]
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
index 163b734164e..72ea88a0320 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
@@ -161,3 +161,11 @@ select timestampdiff(SECOND, date'2022-02-15',
timestamp'2022-02-14 23:59:59');
select timestampdiff('MINUTE', timestamp'2022-02-14 01:02:03',
timestamp'2022-02-14 02:00:03');
select timestampdiff('YEAR', date'2022-02-15', date'2023-02-15');
+
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03');
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03');
+select timediff(DAY, date'2022-02-15', date'2023-02-15');
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59');
+
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03');
+select timediff('YEAR', date'2020-02-15', date'2023-02-15');
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/keywords.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/keywords.sql.out
index 2abb5cee119..f88dcbd4658 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/keywords.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/keywords.sql.out
@@ -285,6 +285,7 @@ TBLPROPERTIES false
TERMINATED false
THEN true
TIME true
+TIMEDIFF false
TIMESTAMP false
TIMESTAMPADD false
TIMESTAMPDIFF false
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
index 51314079a8d..d7a58e321b0 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
@@ -1137,3 +1137,83 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query schema
+struct<timestampdiff(QUARTER, TIMESTAMP '2023-08-10 01:02:03', TIMESTAMP
'2022-01-14 01:02:03'):bigint>
+-- !query output
+-6
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query schema
+struct<timestampdiff(HOUR, TIMESTAMP '2022-02-14 01:02:03', TIMESTAMP
'2022-02-14 12:00:03'):bigint>
+-- !query output
+10
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query schema
+struct<timestampdiff(DAY, DATE '2022-02-15', DATE '2023-02-15'):bigint>
+-- !query output
+365
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query schema
+struct<timestampdiff(SECOND, DATE '2022-02-15', TIMESTAMP '2022-02-14
23:59:59'):bigint>
+-- !query output
+-1
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
b/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
index 1642e10ea41..8b8a09ae3f1 100644
--- a/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
@@ -2276,3 +2276,83 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query schema
+struct<timestampdiff(QUARTER, TIMESTAMP '2023-08-10 01:02:03', TIMESTAMP
'2022-01-14 01:02:03'):bigint>
+-- !query output
+-6
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query schema
+struct<timestampdiff(HOUR, TIMESTAMP '2022-02-14 01:02:03', TIMESTAMP
'2022-02-14 12:00:03'):bigint>
+-- !query output
+10
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query schema
+struct<timestampdiff(DAY, DATE '2022-02-15', DATE '2023-02-15'):bigint>
+-- !query output
+365
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query schema
+struct<timestampdiff(SECOND, DATE '2022-02-15', TIMESTAMP '2022-02-14
23:59:59'):bigint>
+-- !query output
+-1
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git a/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
b/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
index 716e2a32e7f..b618299ea61 100644
--- a/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/keywords.sql.out
@@ -285,6 +285,7 @@ TBLPROPERTIES false
TERMINATED false
THEN false
TIME false
+TIMEDIFF false
TIMESTAMP false
TIMESTAMPADD false
TIMESTAMPDIFF false
diff --git a/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
index c28fda32c9c..3128c5dd359 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
@@ -1133,3 +1133,83 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query schema
+struct<timestampdiff(QUARTER, TIMESTAMP '2023-08-10 01:02:03', TIMESTAMP
'2022-01-14 01:02:03'):bigint>
+-- !query output
+-6
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query schema
+struct<timestampdiff(HOUR, TIMESTAMP '2022-02-14 01:02:03', TIMESTAMP
'2022-02-14 12:00:03'):bigint>
+-- !query output
+10
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query schema
+struct<timestampdiff(DAY, DATE '2022-02-15', DATE '2023-02-15'):bigint>
+-- !query output
+365
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query schema
+struct<timestampdiff(SECOND, DATE '2022-02-15', TIMESTAMP '2022-02-14
23:59:59'):bigint>
+-- !query output
+-1
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
index 06b6818e00e..25aaadfc8e7 100644
---
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
@@ -1128,3 +1128,83 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query schema
+struct<timestampdiff(QUARTER, TIMESTAMP_NTZ '2023-08-10 01:02:03',
TIMESTAMP_NTZ '2022-01-14 01:02:03'):bigint>
+-- !query output
+-6
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query schema
+struct<timestampdiff(HOUR, TIMESTAMP_NTZ '2022-02-14 01:02:03', TIMESTAMP_NTZ
'2022-02-14 12:00:03'):bigint>
+-- !query output
+10
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query schema
+struct<timestampdiff(DAY, DATE '2022-02-15', DATE '2023-02-15'):bigint>
+-- !query output
+365
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query schema
+struct<timestampdiff(SECOND, DATE '2022-02-15', TIMESTAMP_NTZ '2022-02-14
23:59:59'):bigint>
+-- !query output
+-1
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
index ff13693e65b..24edf1a3577 100644
---
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
@@ -1109,3 +1109,83 @@ org.apache.spark.sql.catalyst.parser.ParseException
"fragment" : "timestampdiff('YEAR', date'2022-02-15', date'2023-02-15')"
} ]
}
+
+
+-- !query
+select timediff(QUARTER, timestamp'2023-08-10 01:02:03', timestamp'2022-01-14
01:02:03')
+-- !query schema
+struct<timestampdiff(QUARTER, TIMESTAMP_NTZ '2023-08-10 01:02:03',
TIMESTAMP_NTZ '2022-01-14 01:02:03'):bigint>
+-- !query output
+-6
+
+
+-- !query
+select timediff(HOUR, timestamp'2022-02-14 01:02:03', timestamp'2022-02-14
12:00:03')
+-- !query schema
+struct<timestampdiff(HOUR, TIMESTAMP_NTZ '2022-02-14 01:02:03', TIMESTAMP_NTZ
'2022-02-14 12:00:03'):bigint>
+-- !query output
+10
+
+
+-- !query
+select timediff(DAY, date'2022-02-15', date'2023-02-15')
+-- !query schema
+struct<timestampdiff(DAY, DATE '2022-02-15', DATE '2023-02-15'):bigint>
+-- !query output
+365
+
+
+-- !query
+select timediff(SECOND, date'2022-02-15', timestamp'2022-02-14 23:59:59')
+-- !query schema
+struct<timestampdiff(SECOND, DATE '2022-02-15', TIMESTAMP_NTZ '2022-02-14
23:59:59'):bigint>
+-- !query output
+-1
+
+
+-- !query
+select timediff('MINUTE', timestamp'2023-02-14 01:02:03', timestamp'2023-02-14
02:00:03')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'MINUTE'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 89,
+ "fragment" : "timediff('MINUTE', timestamp'2023-02-14 01:02:03',
timestamp'2023-02-14 02:00:03')"
+ } ]
+}
+
+
+-- !query
+select timediff('YEAR', date'2020-02-15', date'2023-02-15')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "INVALID_PARAMETER_VALUE.DATETIME_UNIT",
+ "sqlState" : "22023",
+ "messageParameters" : {
+ "functionName" : "`timediff`",
+ "invalidValue" : "'YEAR'",
+ "parameter" : "`unit`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 59,
+ "fragment" : "timediff('YEAR', date'2020-02-15', date'2023-02-15')"
+ } ]
+}
diff --git
a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
index fd4c68e8ac2..8355a03125a 100644
---
a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
+++
b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerWithSparkContextSuite.scala
@@ -213,7 +213,7 @@ trait ThriftServerWithSparkContextSuite extends
SharedThriftServer {
val sessionHandle = client.openSession(user, "")
val infoValue = client.getInfo(sessionHandle,
GetInfoType.CLI_ODBC_KEYWORDS)
// scalastyle:off line.size.limit
- assert(infoValue.getStringValue ==
"ADD,AFTER,ALL,ALTER,ALWAYS,ANALYZE,AND,ANTI,ANY,ANY_VALUE,ARCHIVE,ARRAY,AS,ASC,AT,AUTHORIZATION,BETWEEN,BIGINT,BINARY,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,BYTE,CACHE,CASCADE,CASE,CAST,CATALOG,CATALOGS,CHANGE,CHAR,CHARACTER,CHECK,CLEAR,CLUSTER,CLUSTERED,CODEGEN,COLLATE,COLLECTION,COLUMN,COLUMNS,COMMENT,COMMIT,COMPACT,COMPACTIONS,COMPUTE,CONCATENATE,CONSTRAINT,COST,CREATE,CROSS,CUBE,CURRENT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,DATA,DA
[...]
+ assert(infoValue.getStringValue ==
"ADD,AFTER,ALL,ALTER,ALWAYS,ANALYZE,AND,ANTI,ANY,ANY_VALUE,ARCHIVE,ARRAY,AS,ASC,AT,AUTHORIZATION,BETWEEN,BIGINT,BINARY,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,BYTE,CACHE,CASCADE,CASE,CAST,CATALOG,CATALOGS,CHANGE,CHAR,CHARACTER,CHECK,CLEAR,CLUSTER,CLUSTERED,CODEGEN,COLLATE,COLLECTION,COLUMN,COLUMNS,COMMENT,COMMIT,COMPACT,COMPACTIONS,COMPUTE,CONCATENATE,CONSTRAINT,COST,CREATE,CROSS,CUBE,CURRENT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,DATA,DA
[...]
// scalastyle:on line.size.limit
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
