This is an automated email from the ASF dual-hosted git repository.
zclllyybb pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new a9ca36f68ee [fix](timestamptz) Preserve correct DST fold branch to go
cross the transition point (#63034)
a9ca36f68ee is described below
commit a9ca36f68eec67dd0363e211b55319562178315e
Author: zclllyybb <[email protected]>
AuthorDate: Fri May 8 20:46:59 2026 +0800
[fix](timestamptz) Preserve correct DST fold branch to go cross the
transition point (#63034)
Problem Summary: TIMESTAMPTZ values in a DST fold hour could be
converted through an ambiguous local time, making explicit post-fold
instants use the earlier branch during FE literal handling and BE
floor/ceil/date_trunc execution.
The old code would treat DST repeated hours like `2024-11-03 01:xx` as
local civil time without offset when parsing TIMESTAMPTZ with explicit
offset in FE, and when BE converts local time to UTC using
date_trunc/floor/ceil, it would lose the `-04/-05` offset branch
information and default to the earlier branch. After the fix, FE
directly constructs TimestampTzLiteral from timezone-aware strings, and
BE uses the original UTC instant's offset to select pre/post branches
when converting repeated hours back to UTC, thus preserving the correct
fold branch.
when `time_zone = 'America/New_York'`:
```sql
SELECT
CAST(CAST('2024-11-03 01:05:00 -05:00' AS TIMESTAMPTZ(6)) AS VARCHAR(64))
AS ts,
CAST(date_trunc(CAST('2024-11-03 01:05:30 -05:00' AS TIMESTAMPTZ(6)),
'hour') AS VARCHAR(64)) AS hour_trunc;
```
before:
```
ts hour_trunc
2024-11-03 01:05:00.000000-04:00 2024-11-03 01:00:00.000000-04:00
```
now:
```
ts hour_trunc
2024-11-03 01:05:00.000000-05:00 2024-11-03 01:00:00.000000-05:00
```
---
be/src/core/value/timestamptz_value.cpp | 32 +++++++
be/src/core/value/timestamptz_value.h | 7 +-
.../function/function_datetime_floor_ceil.cpp | 19 ++--
.../function/function_other_types_to_date.cpp | 8 +-
.../expressions/literal/StringLikeLiteral.java | 12 ++-
.../doris/nereids/util/TypeCoercionUtils.java | 29 ++++--
.../doris/nereids/util/TypeCoercionUtilsTest.java | 18 ++++
.../timestamptz/test_timestamptz_dst_fold.out | 17 ++++
.../test_timestamptz_storage_agg_key.out | 9 ++
.../sql-functions/doc_date_functions_test.out | 2 +-
.../datetime_functions/test_date_function_v2.out | 6 +-
.../timestamptz/test_timestamptz_dst_fold.groovy | 103 +++++++++++++++++++++
12 files changed, 235 insertions(+), 27 deletions(-)
diff --git a/be/src/core/value/timestamptz_value.cpp
b/be/src/core/value/timestamptz_value.cpp
index 40ca96bdf7f..ffa9bf530e2 100644
--- a/be/src/core/value/timestamptz_value.cpp
+++ b/be/src/core/value/timestamptz_value.cpp
@@ -135,4 +135,36 @@ void TimestampTzValue::convert_local_to_utc(const
cctz::time_zone& local_time_zo
dt.microsecond());
}
+int TimestampTzValue::utc_offset(const cctz::time_zone& local_time_zone) const
{
+ cctz::civil_second utc_cs(_utc_dt.year(), _utc_dt.month(), _utc_dt.day(),
_utc_dt.hour(),
+ _utc_dt.minute(), _utc_dt.second());
+ cctz::time_point<cctz::seconds> utc_tp = cctz::convert(utc_cs,
cctz::utc_time_zone());
+ return local_time_zone.lookup(utc_tp).offset;
+}
+
+void TimestampTzValue::convert_local_to_utc(const cctz::time_zone&
local_time_zone,
+ const
DateV2Value<DateTimeV2ValueType>& dt,
+ int preferred_offset) {
+ cctz::civil_second local_cs(dt.year(), dt.month(), dt.day(), dt.hour(),
dt.minute(),
+ dt.second());
+ const auto lookup = local_time_zone.lookup(local_cs);
+ cctz::time_point<cctz::seconds> local_tp = cctz::convert(local_cs,
local_time_zone);
+
+ if (lookup.kind == cctz::time_zone::civil_lookup::REPEATED) {
+ const auto pre_offset = local_time_zone.lookup(lookup.pre).offset;
+ const auto post_offset = local_time_zone.lookup(lookup.post).offset;
+ if (preferred_offset == pre_offset) {
+ local_tp = lookup.pre;
+ } else if (preferred_offset == post_offset) {
+ local_tp = lookup.post;
+ }
+ }
+
+ auto utc_cs = cctz::convert(local_tp, cctz::utc_time_zone());
+ _utc_dt.unchecked_set_time((uint16_t)utc_cs.year(),
(uint8_t)utc_cs.month(),
+ (uint8_t)utc_cs.day(), (uint8_t)utc_cs.hour(),
+ (uint8_t)utc_cs.minute(),
(uint8_t)utc_cs.second(),
+ dt.microsecond());
+}
+
} // namespace doris
diff --git a/be/src/core/value/timestamptz_value.h
b/be/src/core/value/timestamptz_value.h
index 3ccf0426fde..1a0fa21f004 100644
--- a/be/src/core/value/timestamptz_value.h
+++ b/be/src/core/value/timestamptz_value.h
@@ -151,6 +151,11 @@ public:
void convert_local_to_utc(const cctz::time_zone& local_time_zone,
const DateV2Value<DateTimeV2ValueType>& dt);
+ int utc_offset(const cctz::time_zone& local_time_zone) const;
+
+ void convert_local_to_utc(const cctz::time_zone& local_time_zone,
+ const DateV2Value<DateTimeV2ValueType>& dt, int
preferred_offset);
+
TimestampTzValue& operator++() {
++_utc_dt;
return *this;
@@ -197,4 +202,4 @@ struct std::hash<doris::TimestampTzValue> {
auto int_val = v.to_date_int_val();
return doris::HashUtil::hash(&int_val, sizeof(int_val), 0);
}
-};
\ No newline at end of file
+};
diff --git a/be/src/exprs/function/function_datetime_floor_ceil.cpp
b/be/src/exprs/function/function_datetime_floor_ceil.cpp
index 94e073f9e12..8863ea42576 100644
--- a/be/src/exprs/function/function_datetime_floor_ceil.cpp
+++ b/be/src/exprs/function/function_datetime_floor_ceil.cpp
@@ -771,18 +771,13 @@ struct DateTimeFloorCeilCore {
// For TimestampTzValue on date-based units, convert result from local
time back to UTC
if constexpr (need_tz_conversion) {
if (result) {
- cctz::civil_second local_result_cs(ts_res.year(),
ts_res.month(), ts_res.day(),
- ts_res.hour(),
ts_res.minute(), ts_res.second());
- cctz::time_point<cctz::sys_seconds> local_tp =
cctz::convert(local_result_cs, tz);
- auto utc_result_cs = cctz::convert(local_tp,
cctz::utc_time_zone());
-
-
ts_origin.unchecked_set_time(static_cast<uint16_t>(utc_result_cs.year()),
-
static_cast<uint8_t>(utc_result_cs.month()),
-
static_cast<uint8_t>(utc_result_cs.day()),
-
static_cast<uint8_t>(utc_result_cs.hour()),
-
static_cast<uint8_t>(utc_result_cs.minute()),
-
static_cast<uint8_t>(utc_result_cs.second()),
- ts_res.microsecond());
+ DateV2Value<DateTimeV2ValueType>
local_result(ts_res.to_date_int_val());
+ if constexpr (Flag::Unit == HOUR || Flag::Unit == MINUTE) {
+ const int preferred_offset = ts_arg.utc_offset(tz);
+ ts_origin.convert_local_to_utc(tz, local_result,
preferred_offset);
+ } else {
+ ts_origin.convert_local_to_utc(tz, local_result);
+ }
}
}
diff --git a/be/src/exprs/function/function_other_types_to_date.cpp
b/be/src/exprs/function/function_other_types_to_date.cpp
index 0ccfb8f7e53..002112993fe 100644
--- a/be/src/exprs/function/function_other_types_to_date.cpp
+++ b/be/src/exprs/function/function_other_types_to_date.cpp
@@ -551,7 +551,13 @@ private:
DateV2Value<DateTimeV2ValueType> local_dt;
dt.convert_utc_to_local(timezone, local_dt);
local_dt.template datetime_trunc<Unit>();
- dt.convert_local_to_utc(timezone, local_dt);
+ if constexpr (Unit == TimeUnit::SECOND || Unit ==
TimeUnit::MINUTE ||
+ Unit == TimeUnit::HOUR) {
+ const int preferred_offset = dt.utc_offset(timezone);
+ dt.convert_local_to_utc(timezone, local_dt,
preferred_offset);
+ } else {
+ dt.convert_local_to_utc(timezone, local_dt);
+ }
} else {
dt.template datetime_trunc<Unit>();
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/StringLikeLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/StringLikeLiteral.java
index 4e9d395415f..621b8d6fa2b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/StringLikeLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/StringLikeLiteral.java
@@ -22,6 +22,7 @@ import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.exceptions.CastException;
import org.apache.doris.nereids.trees.expressions.Expression;
import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeExtractAndTransform;
+import
org.apache.doris.nereids.trees.expressions.literal.format.DateTimeChecker;
import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.DateTimeType;
import org.apache.doris.nereids.types.DateTimeV2Type;
@@ -147,10 +148,19 @@ public abstract class StringLikeLiteral extends Literal
implements ComparableLit
return new DateTimeLiteral((DateTimeType) targetType,
datetime.year, datetime.month, datetime.day,
datetime.hour, datetime.minute, datetime.second,
datetime.microSecond);
} else if (targetType.isTimeStampTzType()) {
+ // Explicit offsets must not round-trip through session local
time; that loses the selected
+ // branch in DST fold hours. Wildcard targets still need a
concrete scale before parsing.
+ TimeStampTzType timeStampTzType = (TimeStampTzType) targetType;
+ if (timeStampTzType.getScale() < 0) {
+ timeStampTzType = TimeStampTzType.forTypeFromString(value);
+ }
+ if (DateTimeChecker.hasTimeZone(value)) {
+ return new TimestampTzLiteral(timeStampTzType, value);
+ }
DateTimeV2Literal expression = castToDateTime(DateTimeV2Type.MAX,
strictCast, true);
expression = (DateTimeV2Literal)
(DateTimeExtractAndTransform.convertTz(expression,
new
StringLiteral(ConnectContext.get().getSessionVariable().timeZone), new
StringLiteral("UTC")));
- return new TimestampTzLiteral((TimeStampTzType) targetType,
expression.year, expression.month,
+ return new TimestampTzLiteral(timeStampTzType, expression.year,
expression.month,
expression.day, expression.hour, expression.minute,
expression.second, expression.microSecond);
} else if (targetType.isDateTimeV2Type()) {
return castToDateTime(targetType, strictCast, true);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
index ca7b04d7ed6..2970e9bbe46 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
@@ -638,15 +638,26 @@ public class TypeCoercionUtils {
&& DateTimeChecker.isValidDateTime(value)) {
ret = DateTimeLiteral.parseDateTimeLiteral(value,
true).orElse(null);
} else if (dataType.isTimeStampTzType() &&
DateTimeChecker.isValidDateTime(value)) {
- DateTimeV2Literal dtV2Lit = (DateTimeV2Literal) DateTimeLiteral
- .parseDateTimeLiteral(value,
true).orElse(null);
- if (dtV2Lit != null) {
- dtV2Lit = (DateTimeV2Literal)
(DateTimeExtractAndTransform.convertTz(
- dtV2Lit,
- new
StringLiteral(ConnectContext.get().getSessionVariable().timeZone),
- new StringLiteral("UTC")));
- ret = new TimestampTzLiteral(dtV2Lit.getYear(),
dtV2Lit.getMonth(), dtV2Lit.getDay(),
- dtV2Lit.getHour(), dtV2Lit.getMinute(),
dtV2Lit.getSecond(), dtV2Lit.getMicroSecond());
+ if (DateTimeChecker.hasTimeZone(value)) {
+ // Signature search can pass TIMESTAMPTZ(*) here.
TimestampTzLiteral rounds by scale,
+ // so derive a concrete scale from the literal before
preserving its explicit offset.
+ TimeStampTzType timeStampTzType = (TimeStampTzType)
dataType;
+ if (timeStampTzType.getScale() < 0) {
+ timeStampTzType =
TimeStampTzType.forTypeFromString(value);
+ }
+ ret = new TimestampTzLiteral(timeStampTzType, value);
+ } else {
+ DateTimeV2Literal dtV2Lit = (DateTimeV2Literal)
DateTimeLiteral
+ .parseDateTimeLiteral(value,
true).orElse(null);
+ if (dtV2Lit != null) {
+ dtV2Lit = (DateTimeV2Literal)
(DateTimeExtractAndTransform.convertTz(
+ dtV2Lit,
+ new
StringLiteral(ConnectContext.get().getSessionVariable().timeZone),
+ new StringLiteral("UTC")));
+ ret = new TimestampTzLiteral(dtV2Lit.getYear(),
dtV2Lit.getMonth(), dtV2Lit.getDay(),
+ dtV2Lit.getHour(), dtV2Lit.getMinute(),
dtV2Lit.getSecond(),
+ dtV2Lit.getMicroSecond());
+ }
}
} else if ((dataType.isDateV2Type() || dataType.isDateType()) &&
DateTimeChecker.isValidDateTime(value)) {
Result<DateLiteral, AnalysisException> parseResult =
DateV2Literal.parseDateLiteral(value, true);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
index 7aa41181374..d2ead4e326d 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/TypeCoercionUtilsTest.java
@@ -63,10 +63,12 @@ import org.apache.doris.nereids.types.QuantileStateType;
import org.apache.doris.nereids.types.SmallIntType;
import org.apache.doris.nereids.types.StringType;
import org.apache.doris.nereids.types.StructType;
+import org.apache.doris.nereids.types.TimeStampTzType;
import org.apache.doris.nereids.types.TimeV2Type;
import org.apache.doris.nereids.types.TinyIntType;
import org.apache.doris.nereids.types.VarcharType;
import org.apache.doris.nereids.types.coercion.IntegralType;
+import org.apache.doris.qe.ConnectContext;
import com.google.common.collect.ImmutableList;
import org.junit.jupiter.api.Assertions;
@@ -286,6 +288,22 @@ public class TypeCoercionUtilsTest {
// datetime
Assertions.assertEquals(DateTimeV2Type.SYSTEM_DEFAULT,
TypeCoercionUtils.characterLiteralTypeCoercion("2020-02-02",
DateTimeType.INSTANCE).get().getDataType());
+ // timestamptz wildcard
+ Assertions.assertEquals(TimeStampTzType.SYSTEM_DEFAULT,
+
TypeCoercionUtils.characterLiteralTypeCoercion("2023-08-17T01:41:18Z",
TimeStampTzType.WILDCARD)
+ .get().getDataType());
+ // No-zone TIMESTAMPTZ coercion uses the session timezone to define
the local civil time.
+ ConnectContext connectContext = new ConnectContext();
+ connectContext.getSessionVariable().setTimeZone("Asia/Shanghai");
+ connectContext.setThreadLocalInfo();
+ try {
+ // timestamptz without explicit timezone keeps the literal scale
during signature search
+ Assertions.assertEquals(TimeStampTzType.SYSTEM_DEFAULT,
+
TypeCoercionUtils.characterLiteralTypeCoercion("2004-12-31",
TimeStampTzType.MAX)
+ .get().getDataType());
+ } finally {
+ ConnectContext.remove();
+ }
}
@Test
diff --git
a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_dst_fold.out
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_dst_fold.out
new file mode 100644
index 00000000000..a2dba24a802
--- /dev/null
+++ b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_dst_fold.out
@@ -0,0 +1,17 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+1 pre_fold_utc 2024-11-03 01:05:00.000000-04:00
+2 post_fold_utc 2024-11-03 01:05:00.000000-05:00
+3 pre_explicit 2024-11-03 01:05:00.000000-04:00
+4 post_explicit 2024-11-03 01:05:00.000000-05:00
+
+-- !sql --
+2 post_fold_utc
+4 post_explicit
+
+-- !sql --
+4 2024-11-03 01:00:00.000000-05:00 2024-11-03
01:10:00.000000-05:00 2024-11-03 01:00:00.000000-05:00 2024-11-03
01:05:00.000000-05:00 2024-11-03 01:05:00.000000-05:00 2024-11-03
01:00:00.000000-05:00
+
+-- !sql --
+2024-11-03 06:05:00.000000+00:00 2024-11-03 06:05:00.000000+00:00
2024-11-03 06:00:00.000000+00:00
+
diff --git
a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_storage_agg_key.out
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_storage_agg_key.out
index 4b519c03308..d57bd6e3deb 100644
---
a/regression-test/data/datatype_p0/timestamptz/test_timestamptz_storage_agg_key.out
+++
b/regression-test/data/datatype_p0/timestamptz/test_timestamptz_storage_agg_key.out
@@ -458,6 +458,15 @@
2025-12-12 12:12:12.123456+08:00 3023-09-09 16:09:09.000000+08:00
3023-09-09 16:09:09.000000+08:00 1023-09-09 16:09:09.000000+08:00
3023-09-09 16:09:09.000000+08:00
-- !scale_not_in --
+0000-01-01 08:00:00.123456+08:00 0000-01-01 08:00:00.000000+08:00
0000-01-01 08:00:00.000000+08:00 0000-01-01 08:00:00.000000+08:00
0000-01-01 08:00:00.000000+08:00
+0000-01-01 08:00:00.999999+08:00 0000-01-01 08:00:00.000000+08:00
0000-01-01 08:00:00.000000+08:00 0000-01-01 08:00:00.000000+08:00
0000-01-01 08:00:00.000000+08:00
+2025-12-12 12:12:12.000000+08:00 3023-09-09 16:09:09.000000+08:00
2023-09-09 16:09:09.000000+08:00 2023-09-09 16:09:09.000000+08:00
2023-09-09 16:09:09.000000+08:00
+2025-12-12 12:12:12.000001+08:00 3023-09-09 16:09:09.000000+08:00
3023-09-09 16:09:09.000000+08:00 1023-09-09 16:09:09.000000+08:00
3023-09-09 16:09:09.000000+08:00
+2025-12-12 12:12:12.999999+08:00 3023-09-09 16:09:09.000000+08:00
3023-09-09 16:09:09.000000+08:00 1023-09-09 16:09:09.000000+08:00
3023-09-09 16:09:09.000000+08:00
+9999-12-31 23:59:59.000000+08:00 0000-01-01 08:00:00.000000+08:00
0000-01-01 08:00:00.000000+08:00 0000-01-01 08:00:00.000000+08:00
2023-04-05 07:59:59.000000+08:00
+9999-12-31 23:59:59.000001+08:00 0000-01-01 08:00:00.000000+08:00
9999-12-31 23:59:59.999999+08:00 0000-01-01 08:00:00.000000+08:00
9999-12-31 23:59:59.999999+08:00
+9999-12-31 23:59:59.123456+08:00 0000-01-01 08:00:00.000000+08:00
9999-12-31 23:59:59.999999+08:00 0000-01-01 08:00:00.000000+08:00
9999-12-31 23:59:59.999999+08:00
+9999-12-31 23:59:59.999999+08:00 0000-01-01 08:00:00.000000+08:00
9999-12-31 23:59:59.999999+08:00 0000-01-01 08:00:00.000000+08:00
9999-12-31 23:59:59.999999+08:00
-- !scale_is_null --
\N 2025-12-12 12:12:12.000000+08:00 2025-12-12
12:12:12.000000+08:00 0000-01-01 08:00:00.000000+08:00 2025-12-12
12:12:12.000000+08:00
diff --git
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
index 06b53aebae1..63cdecd6d68 100644
---
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
+++
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
@@ -2097,7 +2097,7 @@ da fanadur
1196389819
-- !unix_timestamp_4 --
-1196386219.000000
+1196386219
-- !unix_timestamp_5 --
1196389819.000000
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function_v2.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function_v2.out
index 3a9a3534780..25e5ac66919 100644
---
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function_v2.out
+++
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function_v2.out
@@ -121,6 +121,7 @@
-- !get_format_time_4 --
\N
+
-- !sql_addtime1 --
2023-10-14T22:35:22
@@ -154,10 +155,10 @@
2025-06-30T17:15:30.999999
-- !sql_addtime9 --
-2025-10-10T17:24:36+08:00
+2025-10-10 17:24:36+08:00
-- !sql_addtime10 --
-2025-10-10T17:24:36.123457+08:00
+2025-10-10 17:24:36.123457+08:00
-- !sql_addtime11 --
44:22:32
@@ -220,3 +221,4 @@
-- !sql_subtime14 --
0001-01-01 07:59:59.999999+08:00
+
diff --git
a/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_dst_fold.groovy
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_dst_fold.groovy
new file mode 100644
index 00000000000..a090b6f0957
--- /dev/null
+++
b/regression-test/suites/datatype_p0/timestamptz/test_timestamptz_dst_fold.groovy
@@ -0,0 +1,103 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_timestamptz_dst_fold") {
+ sql "SET enable_nereids_planner = true;"
+ sql "SET enable_fallback_to_original_planner = false;"
+
+ sql "DROP TABLE IF EXISTS tz_dst_fold_events;"
+ sql "DROP TABLE IF EXISTS tz_dst_fold_trunc_out;"
+ sql """
+ CREATE TABLE tz_dst_fold_events (
+ id INT,
+ label VARCHAR(64),
+ ts TIMESTAMPTZ(6)
+ )
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES('replication_num' = '1');
+ """
+
+ sql "SET time_zone = 'America/New_York';"
+ sql """
+ INSERT INTO tz_dst_fold_events VALUES
+ (1, 'pre_fold_utc', CAST('2024-11-03 05:05:00 +00:00' AS
TIMESTAMPTZ(6))),
+ (2, 'post_fold_utc', CAST('2024-11-03 06:05:00 +00:00' AS
TIMESTAMPTZ(6))),
+ (3, 'pre_explicit', CAST('2024-11-03 01:05:00 -04:00' AS
TIMESTAMPTZ(6))),
+ (4, 'post_explicit', CAST('2024-11-03 01:05:00 -05:00' AS
TIMESTAMPTZ(6)));
+ """
+
+ sql "SET debug_skip_fold_constant = true;"
+ qt_sql """
+ SELECT id, label, CAST(ts AS VARCHAR(64)) AS rendered
+ FROM tz_dst_fold_events
+ ORDER BY id;
+ """
+
+ sql "SET debug_skip_fold_constant = false;"
+ qt_sql """
+ SELECT id, label
+ FROM tz_dst_fold_events
+ WHERE ts = CAST('2024-11-03 01:05:00 -05:00' AS TIMESTAMPTZ(6))
+ ORDER BY id;
+ """
+
+ sql "SET debug_skip_fold_constant = true;"
+ qt_sql """
+ SELECT id,
+ CAST(minute_floor(ts, 10) AS VARCHAR(64)) AS
minute_floor_rendered,
+ CAST(minute_ceil(ts, 10) AS VARCHAR(64)) AS
minute_ceil_rendered,
+ CAST(hour_floor(ts, 1) AS VARCHAR(64)) AS hour_floor_rendered,
+ CAST(date_trunc(ts, 'second') AS VARCHAR(64)) AS
second_trunc_rendered,
+ CAST(date_trunc(ts, 'minute') AS VARCHAR(64)) AS
minute_trunc_rendered,
+ CAST(date_trunc(ts, 'hour') AS VARCHAR(64)) AS
hour_trunc_rendered
+ FROM tz_dst_fold_events
+ WHERE id = 4
+ ORDER BY id;
+ """
+
+ sql """
+ CREATE TABLE tz_dst_fold_trunc_out (
+ second_trunc TIMESTAMPTZ(6),
+ minute_trunc TIMESTAMPTZ(6),
+ hour_trunc TIMESTAMPTZ(6)
+ )
+ DUPLICATE KEY(second_trunc)
+ DISTRIBUTED BY HASH(second_trunc) BUCKETS 1
+ PROPERTIES('replication_num' = '1');
+ """
+ sql """
+ INSERT INTO tz_dst_fold_trunc_out
+ SELECT date_trunc(ts, 'second'),
+ date_trunc(ts, 'minute'),
+ date_trunc(ts, 'hour')
+ FROM tz_dst_fold_events
+ WHERE id = 4;
+ """
+
+ sql "SET time_zone = '+00:00';"
+ qt_sql """
+ SELECT CAST(second_trunc AS VARCHAR(64)) AS stored_second_utc,
+ CAST(minute_trunc AS VARCHAR(64)) AS stored_minute_utc,
+ CAST(hour_trunc AS VARCHAR(64)) AS stored_hour_utc
+ FROM tz_dst_fold_trunc_out
+ ORDER BY 1, 2, 3;
+ """
+
+ sql "SET time_zone = default;"
+ sql "SET debug_skip_fold_constant = false;"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
