This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new e0bc26c344 [CALCITE-7522] Allow EXTRACT(interval FROM tz) to operate
on TIMESTAMP WITH TIME ZONE
e0bc26c344 is described below
commit e0bc26c344e7537a8b6d0bd875473988833d8eda
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue May 12 21:45:54 2026 -0700
[CALCITE-7522] Allow EXTRACT(interval FROM tz) to operate on TIMESTAMP WITH
TIME ZONE
Signed-off-by: Mihai Budiu <[email protected]>
---
.../java/org/apache/calcite/test/BabelQuidemTest.java | 3 +++
babel/src/test/resources/sql/big-query.iq | 15 ++++++++-------
.../org/apache/calcite/sql/fun/SqlExtractFunction.java | 6 ++++++
3 files changed, 17 insertions(+), 7 deletions(-)
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
index 65a4f6c64d..f826d01e45 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
@@ -34,6 +34,7 @@
import org.junit.jupiter.api.BeforeEach;
import java.sql.Connection;
+import java.time.ZoneId;
import java.util.Collection;
import java.util.List;
import java.util.Locale;
@@ -118,6 +119,8 @@ public static void main(String[] args) throws Exception {
ConnectionFactories.addType("TIMESTAMP", typeFactory ->
typeFactory.createSqlType(
SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)))
+ // Set the time zone for deterministic results
+ .with(CalciteConnectionProperty.TIME_ZONE,
ZoneId.of("UTC").toString())
.connect();
case "scott-postgresql":
return CalciteAssert.that()
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index a988eac1d7..8d9334d224 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -27,6 +27,8 @@
# The DATETIME() and TIMESTAMP() functions are also substituted so that they
# produce values that BigQuery would call DATETIME and TIMESTAMP.
#
+# Note: scott-big-query sets the TIME ZONE to UTC for deterministic test
results
+# for tests that involve TIME ZONE
!use scott-big-query
!set outputformat mysql
@@ -591,19 +593,18 @@ FROM t;
!ok
!}
-!if (false) {
-WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
+# Test case for [CALCITE-7522] Allow EXTRACT(interval FROM tz) to operate on
TIMESTAMP WITH TIME ZONE
SELECT
- EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
- EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS
the_day_california
-FROM Input;
+ EXTRACT(DAY FROM TIMESTAMP WITH TIME ZONE '2008-12-25 20:30:00 UTC') AS
the_day_utc,
+ EXTRACT(DAY FROM TIMESTAMP WITH TIME ZONE '2008-12-25 20:30:00
America/Los_Angeles') AS the_day_california;
+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
-| 25 | 24 |
+| 25 | 26 |
+-------------+--------------------+
+(1 row)
+
!ok
-!}
# Display of results may differ, depending upon the environment and
# time zone where this query was executed.
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
index 9c8d4b36e2..378c414dd5 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
@@ -88,6 +88,7 @@ public SqlExtractFunction(String name, boolean allowString) {
.add(SqlTypeName.DATE)
.add(SqlTypeName.TIMESTAMP)
.add(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
+ .add(SqlTypeName.TIMESTAMP_TZ)
.addAll(SqlTypeName.YEAR_INTERVAL_TYPES)
.build();
@@ -96,6 +97,7 @@ public SqlExtractFunction(String name, boolean allowString) {
new ImmutableSet.Builder<SqlTypeName>()
.add(SqlTypeName.DATE)
.add(SqlTypeName.TIMESTAMP)
+ .add(SqlTypeName.TIMESTAMP_TZ)
.add(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
.build();
@@ -104,6 +106,7 @@ public SqlExtractFunction(String name, boolean allowString)
{
new ImmutableSet.Builder<SqlTypeName>()
.add(SqlTypeName.DATE)
.add(SqlTypeName.TIMESTAMP)
+ .add(SqlTypeName.TIMESTAMP_TZ)
.add(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
.addAll(SqlTypeName.YEAR_INTERVAL_TYPES)
.addAll(SqlTypeName.DAY_INTERVAL_TYPES)
@@ -114,6 +117,7 @@ public SqlExtractFunction(String name, boolean allowString)
{
new ImmutableSet.Builder<SqlTypeName>()
.add(SqlTypeName.DATE)
.add(SqlTypeName.TIMESTAMP)
+ .add(SqlTypeName.TIMESTAMP_TZ)
.add(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
.add(SqlTypeName.INTERVAL_DAY)
.add(SqlTypeName.INTERVAL_DAY_HOUR)
@@ -128,8 +132,10 @@ public SqlExtractFunction(String name, boolean
allowString) {
new ImmutableSet.Builder<SqlTypeName>()
.add(SqlTypeName.DATE)
.add(SqlTypeName.TIMESTAMP)
+ .add(SqlTypeName.TIMESTAMP_TZ)
.add(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
.add(SqlTypeName.TIME)
+ .add(SqlTypeName.TIME_TZ)
.add(SqlTypeName.TIME_WITH_LOCAL_TIME_ZONE)
.addAll(SqlTypeName.YEAR_INTERVAL_TYPES)
.addAll(SqlTypeName.DAY_INTERVAL_TYPES)
