linrrzqqq opened a new pull request, #63161:
URL: https://github.com/apache/doris/pull/63161
Problem Summary:
Fix TIMESTAMPTZ handling for elapsed-time semantics.
TIMESTAMPTZ represents an absolute instant. For calculations that depend on
the elapsed interval between two timestamps, such as time diff functions and
time-window matching logic, Doris should use the stored UTC time values
directly. This follows PostgreSQL-style semantics and avoids treating
TIMESTAMPTZ as local DATETIME before calculation.
### Before
The previous behavior could fall back to TIMESTAMPTZ-to-DATETIME conversion
before evaluating elapsed-time logic. That conversion depends on the session
time zone and produces local wall-clock time.
This is unstable around daylight saving time transitions. During DST
fall-back or spring-forward, local wall-clock time can repeat or skip, so two
TIMESTAMPTZ values with a fixed UTC interval may produce different or
unexpected elapsed-time results after conversion to DATETIME.
```sql
Doris> SET time_zone = '+00:00';
Doris> SELECT milliseconds_diff(
-> CAST('2024-11-03 01:05:00 -05:00' AS TIMESTAMPTZ(6)),
-> CAST('2024-11-03 01:55:00 -04:00' AS TIMESTAMPTZ(6))) AS ms_utc;
+--------+
| ms_utc |
+--------+
| 600000 |
+--------+
Doris> SET time_zone = 'America/New_York';
-- 内部计算 cast 成 datetime(local_time), 在夏令时/冬令时转换节点结果不稳定)
Doris> SELECT milliseconds_diff(
-> CAST('2024-11-03 01:05:00 -05:00' AS TIMESTAMPTZ(6)),
-> CAST('2024-11-03 01:55:00 -04:00' AS TIMESTAMPTZ(6))) AS ms_ny;
+----------+
| ms_ny |
+----------+
| -3000000 |
+----------+
```
### Now
Handle TIMESTAMPTZ directly in affected elapsed-time paths, including scalar
time interval calculations and time-window matching logic, so they operate on
UTC values instead of local DATETIME values.
Add regression coverage for DST transition cases to verify that results are
based on absolute UTC elapsed time.
```sql
Doris> SET time_zone = 'America/New_York';
Doris> SELECT milliseconds_diff(
-> CAST('2024-11-03 01:05:00 -05:00' AS TIMESTAMPTZ(6)),
-> CAST('2024-11-03 01:55:00 -04:00' AS TIMESTAMPTZ(6))) AS ms_ny;
+--------+
| ms_ny |
+--------+
| 600000 |
+--------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
