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 2022/03/24 14:41:12 UTC
[spark] branch branch-3.3 updated: [SPARK-37568][SQL][3.3] Support 2-arguments by the convert_timezone() function
This is an automated email from the ASF dual-hosted git repository.
maxgekk pushed a commit to branch branch-3.3
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.3 by this push:
new 2238b05 [SPARK-37568][SQL][3.3] Support 2-arguments by the convert_timezone() function
2238b05 is described below
commit 2238b05cdac131f286222cf35769d4257cfc5a67
Author: Max Gekk <ma...@gmail.com>
AuthorDate: Thu Mar 24 17:39:08 2022 +0300
[SPARK-37568][SQL][3.3] Support 2-arguments by the convert_timezone() function
### What changes were proposed in this pull request?
Add new constructor to the `ConvertTimezone` expression (see https://github.com/apache/spark/pull/34817) which accepts only 2 arguments:
1. `<targetTz>` - the time zone to which the input timestamp should be converted.
2. `<sourceTs>` - the timestamp to convert.
and sets `<sourceTz>` to the current session time zone (see the SQL config `spark.sql.session.timeZone`).
### Why are the changes needed?
To help users in migrations from other systems to Spark SQL. Other systems support optional first parameter:
- https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html
- https://docs.snowflake.com/en/sql-reference/functions/convert_timezone.html
### Does this PR introduce _any_ user-facing change?
No, it extends the existing signature, and the function hasn't been released yet.
### How was this patch tested?
By running new tests:
```
$ build/sbt "sql/test:testOnly org.apache.spark.sql.expressions.ExpressionInfoSuite"
$ build/sbt "sql/testOnly org.apache.spark.sql.SQLQueryTestSuite -- -z timestamp-ltz.sql"
$ build/sbt "sql/testOnly org.apache.spark.sql.SQLQueryTestSuite -- -z timestamp-ntz.sql"
```
Closes #35957 from MaxGekk/convert_timezone-2-params-3.3.
Authored-by: Max Gekk <ma...@gmail.com>
Signed-off-by: Max Gekk <ma...@gmail.com>
---
.../spark/sql/catalyst/expressions/datetimeExpressions.scala | 10 ++++++++--
sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz.sql | 2 ++
sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql | 1 +
.../src/test/resources/sql-tests/results/timestamp-ltz.sql.out | 10 +++++++++-
.../src/test/resources/sql-tests/results/timestamp-ntz.sql.out | 10 +++++++++-
5 files changed, 29 insertions(+), 4 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 fbc670f..fc701d4 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
@@ -2999,10 +2999,11 @@ object SubtractDates {
// scalastyle:off line.size.limit
@ExpressionDescription(
- usage = "_FUNC_(sourceTz, targetTz, sourceTs) - Converts the timestamp without time zone `sourceTs` from the `sourceTz` time zone to `targetTz`. ",
+ usage = "_FUNC_([sourceTz, ]targetTz, sourceTs) - Converts the timestamp without time zone `sourceTs` from the `sourceTz` time zone to `targetTz`. ",
arguments = """
Arguments:
- * sourceTz - the time zone for the input timestamp
+ * sourceTz - the time zone for the input timestamp.
+ If it is missed, the current session time zone is used as the source time zone.
* targetTz - the time zone to which the input timestamp should be converted
* sourceTs - a timestamp without time zone
""",
@@ -3010,6 +3011,8 @@ object SubtractDates {
Examples:
> SELECT _FUNC_('Europe/Amsterdam', 'America/Los_Angeles', timestamp_ntz'2021-12-06 00:00:00');
2021-12-05 15:00:00
+ > SELECT _FUNC_('Europe/Amsterdam', timestamp_ntz'2021-12-05 15:00:00');
+ 2021-12-06 00:00:00
""",
group = "datetime_funcs",
since = "3.3.0")
@@ -3020,6 +3023,9 @@ case class ConvertTimezone(
sourceTs: Expression)
extends TernaryExpression with ImplicitCastInputTypes with NullIntolerant {
+ def this(targetTz: Expression, sourceTs: Expression) =
+ this(CurrentTimeZone(), targetTz, sourceTs)
+
override def first: Expression = sourceTz
override def second: Expression = targetTz
override def third: Expression = sourceTs
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 3c2883e..88ce0ba 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
@@ -14,3 +14,5 @@ select to_timestamp_ltz(to_timestamp_ntz(null)), to_timestamp_ltz(to_timestamp_n
SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 45.678);
SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 45.678, 'CET');
SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 60.007);
+
+SELECT convert_timezone('Europe/Amsterdam', timestamp_ltz'2022-03-23 00:00:00 America/Los_Angeles');
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
index b7dc287..bec31d3 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
@@ -17,6 +17,7 @@ SELECT make_timestamp_ntz(2021, 07, 11, 6, 30, 45.678, 'CET');
SELECT make_timestamp_ntz(2021, 07, 11, 6, 30, 60.007);
SELECT convert_timezone('Europe/Moscow', 'America/Los_Angeles', timestamp_ntz'2022-01-01 00:00:00');
+SELECT convert_timezone('Europe/Amsterdam', timestamp_ntz'2022-03-23 00:00:00');
-- Get the difference between timestamps w/o time zone in the specified units
select timestampdiff(QUARTER, timestamp_ntz'2022-01-01 01:02:03', timestamp_ntz'2022-05-02 05:06:07');
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 057cdf1..c2ede2f 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
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 7
+-- Number of queries: 8
-- !query
@@ -56,3 +56,11 @@ SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 60.007)
struct<make_timestamp_ltz(2021, 7, 11, 6, 30, 60.007):timestamp>
-- !query output
NULL
+
+
+-- !query
+SELECT convert_timezone('Europe/Amsterdam', timestamp_ltz'2022-03-23 00:00:00 America/Los_Angeles')
+-- !query schema
+struct<convert_timezone(current_timezone(), Europe/Amsterdam, TIMESTAMP '2022-03-23 00:00:00'):timestamp_ntz>
+-- !query output
+2022-03-23 08:00:00
diff --git a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
index f36ffff..146c403 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 12
+-- Number of queries: 13
-- !query
@@ -68,6 +68,14 @@ struct<convert_timezone(Europe/Moscow, America/Los_Angeles, TIMESTAMP_NTZ '2022-
-- !query
+SELECT convert_timezone('Europe/Amsterdam', timestamp_ntz'2022-03-23 00:00:00')
+-- !query schema
+struct<convert_timezone(current_timezone(), Europe/Amsterdam, TIMESTAMP_NTZ '2022-03-23 00:00:00'):timestamp_ntz>
+-- !query output
+2022-03-23 08:00:00
+
+
+-- !query
select timestampdiff(QUARTER, timestamp_ntz'2022-01-01 01:02:03', timestamp_ntz'2022-05-02 05:06:07')
-- !query schema
struct<timestampdiff(QUARTER, TIMESTAMP_NTZ '2022-01-01 01:02:03', TIMESTAMP_NTZ '2022-05-02 05:06:07'):bigint>
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org