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 <max.g...@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 <max.g...@gmail.com> Signed-off-by: Max Gekk <max.g...@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-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org