This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new 6eb4a8093c8 [fix](function) Fix wrong calculation of timestampdiff of
datetimev1 (#56893)
6eb4a8093c8 is described below
commit 6eb4a8093c8fd91043efddc9e195fdc0b3a3c899
Author: zclllyybb <[email protected]>
AuthorDate: Wed Oct 15 10:04:30 2025 +0800
[fix](function) Fix wrong calculation of timestampdiff of datetimev1
(#56893)
### What problem does this PR solve?
Related PR: #49429
Problem Summary:
only fix in 3.1
before:
mysql> select hours_diff(now(), date_format(event_date, '%Y-%m-%d
%H:00:00')) from my_doris_table;
+-----------------------------------------------------------------+
| hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) |
+-----------------------------------------------------------------+
| 103079215078 |
+-----------------------------------------------------------------+
now:
mysql> select hours_diff(now(), date_format(event_date, '%Y-%m-%d
%H:00:00')) from my_doris_table;
+-----------------------------------------------------------------+
| hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) |
+-----------------------------------------------------------------+
| -24 |
+-----------------------------------------------------------------+
---
be/src/vec/runtime/vdatetime_value.h | 3 ++-
.../datetime_functions/test_date_function.out | 3 ---
.../datetime_functions/test_date_function.out | 3 ---
.../datetime_functions/test_date_function.groovy | 1 -
.../test_date_function_v2.groovy | 23 ++++++++++++++++++++++
.../datetime_functions/test_date_function.groovy | 1 -
6 files changed, 25 insertions(+), 9 deletions(-)
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index 0e815dbf3e0..1c9bd9d126a 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -658,7 +658,8 @@ public:
template <typename T>
int64_t datetime_diff_in_seconds(const T& rhs) const {
- return (daynr() - rhs.daynr()) * SECOND_PER_HOUR * HOUR_PER_DAY +
time_part_diff(rhs);
+ return ((int64_t)daynr() - rhs.daynr()) * SECOND_PER_HOUR *
HOUR_PER_DAY +
+ time_part_diff(rhs);
}
void set_type(int type);
diff --git
a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
index cbfc68fc27d..7c305a3900c 100644
---
a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
+++
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
@@ -291,9 +291,6 @@ February
-- !sql --
2020-04-09T00:00
--- !sql --
-08:00:00
-
-- !sql --
00:00:09
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
index 6ea735d5471..d568ebdff36 100644
---
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
+++
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
@@ -308,9 +308,6 @@ February
-- !sql --
2020-04-09T00:00
--- !sql --
-08:00:00
-
-- !sql --
00:00:09
diff --git
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
index 5cf16d07eee..c0bf8269136 100644
---
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -347,7 +347,6 @@ suite("test_date_function") {
qt_sql """ SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3,
CAST('1970-01-09 00:00:00' AS DATETIME)) """
// TIMEDIFF
- qt_sql """ SELECT TIMEDIFF(now(),utc_timestamp()) """
qt_sql """ SELECT TIMEDIFF('2019-07-11 16:59:30','2019-07-11 16:59:21') """
qt_sql """ SELECT TIMEDIFF('2019-01-01 00:00:00', NULL) """
diff --git
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
index 25ead06d66f..e72e0aae881 100644
---
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
@@ -50,4 +50,27 @@ suite("test_date_function_v2") {
testFoldConst("select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
qt_sql_diff14 "select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');"
testFoldConst("select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');")
+
+ sql " drop table if exists my_doris_table;"
+ sql """
+ CREATE TABLE my_doris_table (
+ `id` INT COMMENT '唯一ID',
+ `event_date` DATETIME COMMENT '事件时间，精确到秒'
+ )
+ UNIQUE KEY(`id`)
+ DISTRIBUTED BY HASH(`id`) BUCKETS 10
+ PROPERTIES (
+ "replication_num" = "1"
+ );
+ """
+
+ sql """
+ INSERT INTO my_doris_table VALUES
+ (1, '2099-08-20 10:00:01');
+ """
+
+ def res = sql """ select hours_diff('2000-08-19 08:09:13', event_date)
from my_doris_table; """
+ assertTrue(res[0][0] < 0)
+ res = sql """ select hours_diff(now(), event_date) from my_doris_table;"""
+ assertTrue(res[0][0] < 0)
}
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
index db891c0f015..46a52a8d814 100644
---
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -412,7 +412,6 @@ suite("test_date_function") {
qt_sql """ SELECT MONTH_CEIL(CAST('2020-02-02 13:09:20' AS DATETIME), 3,
CAST('1970-01-09 00:00:00' AS DATETIME)) """
// TIMEDIFF
- qt_sql """ SELECT TIMEDIFF(now(),utc_timestamp()) """
qt_sql """ SELECT TIMEDIFF('2019-07-11 16:59:30','2019-07-11 16:59:21') """
qt_sql """ SELECT TIMEDIFF('2019-01-01 00:00:00', NULL) """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
