This is an automated email from the ASF dual-hosted git repository.
jimin pushed a commit to branch 2.x
in repository https://gitbox.apache.org/repos/asf/incubator-seata.git
The following commit(s) were added to refs/heads/2.x by this push:
new 1e9e7590a6 bugfix: fix the datetime format time query error in the
global lock query (#7613)
1e9e7590a6 is described below
commit 1e9e7590a679261aba7ba5f3698ffba8191841d5
Author: 徐博 洪 <[email protected]>
AuthorDate: Mon Sep 1 19:17:36 2025 +0800
bugfix: fix the datetime format time query error in the global lock query
(#7613)
---
changes/en-us/2.x.md | 1 +
changes/zh-cn/2.x.md | 1 +
.../org/apache/seata/common/util/PageUtil.java | 50 ++++++++++++++++++++++
.../console/impl/db/GlobalLockDBServiceImpl.java | 4 +-
4 files changed, 54 insertions(+), 2 deletions(-)
diff --git a/changes/en-us/2.x.md b/changes/en-us/2.x.md
index 941bf2302e..b521925c37 100644
--- a/changes/en-us/2.x.md
+++ b/changes/en-us/2.x.md
@@ -34,6 +34,7 @@ Add changes here for all PR submitted to the 2.x branch.
- [[#7563](https://github.com/apache/incubator-seata/pull/7563)] Fix NPE when
server-side filter is disabled and filter chain is null.
- [[#7568](https://github.com/apache/incubator-seata/pull/7568)] Fix order()
behavior in GlobalTransactionalInterceptorHandler to ensure correct sorting of
invocation handlers
- [[#7596](https://github.com/apache/incubator-seata/pull/7596)] Fixed the
issue where deserialization failed when the xss filter obtained the default
keyword
+- [[#7613](https://github.com/apache/incubator-seata/pull/7613)] Fixed the SQL
error in the datetime format time query in the global lock query
diff --git a/changes/zh-cn/2.x.md b/changes/zh-cn/2.x.md
index 7102d8b9ec..afd92bb909 100644
--- a/changes/zh-cn/2.x.md
+++ b/changes/zh-cn/2.x.md
@@ -35,6 +35,7 @@
- [[#7563](https://github.com/apache/incubator-seata/pull/7563)]
修复在未开启服务端过滤器的状态下导致的过滤器链空指针异常
- [[#7568](https://github.com/apache/incubator-seata/pull/7568)] 修复
GlobalTransactionalInterceptorHandler 中的 order() 方法行为，确保拦截器的正确排序
- [[#7596](https://github.com/apache/incubator-seata/pull/7596)]
修复xss过滤器获取默认关键字时反序列化失败的问题
+- [[#7613](https://github.com/apache/incubator-seata/pull/7613)]
修复全局锁查询中的datetime时间格式时间查询sql错误
### optimize:
diff --git a/common/src/main/java/org/apache/seata/common/util/PageUtil.java
b/common/src/main/java/org/apache/seata/common/util/PageUtil.java
index 6a61430e7d..8961a2147f 100644
--- a/common/src/main/java/org/apache/seata/common/util/PageUtil.java
+++ b/common/src/main/java/org/apache/seata/common/util/PageUtil.java
@@ -220,4 +220,54 @@ public class PageUtil {
throw new IllegalArgumentException("The DB type :" + dbType +
" is not supported yet");
}
}
+
+ /**
+ * get sql for time start (The database fields is of type datetime)
+ * @param dbType
+ * @param timeColumnName
+ * @return java.lang.String
+ */
+ public static String getDateTimeStartSql(String dbType, String
timeColumnName) {
+ switch (dbType.toLowerCase()) {
+ case "mysql":
+ return " and UNIX_TIMESTAMP(" + timeColumnName + ") >= ? ";
+ case "postgresql":
+ return " and " + timeColumnName + " >= TO_TIMESTAMP(?) ";
+ case "oracle":
+ return " and " + timeColumnName + " >=
TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL(?, 'SECOND') ";
+ case "sqlserver":
+ return " and " + timeColumnName + " >= DATEADD(SECOND, ?,
'1970-01-01 00:00:00') ";
+ case "dm":
+ case "oscar":
+ // Compatible with Oracle syntax
+ return " and " + timeColumnName + " >=
TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL(?, 'SECOND') ";
+ default:
+ throw new IllegalArgumentException("Unsupported DB type: " +
dbType);
+ }
+ }
+
+ /**
+ * get sql for time end (The database fields is of type datetime)
+ * @param dbType
+ * @param timeColumnName
+ * @return java.lang.String
+ */
+ public static String getDateTimeEndSql(String dbType, String
timeColumnName) {
+ switch (dbType.toLowerCase()) {
+ case "mysql":
+ return " and UNIX_TIMESTAMP(" + timeColumnName + ") <= ? ";
+ case "postgresql":
+ return " and " + timeColumnName + " <= TO_TIMESTAMP(?) ";
+ case "oracle":
+ return " and " + timeColumnName + " <=
TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL(?, 'SECOND') ";
+ case "sqlserver":
+ return " and " + timeColumnName + " <= DATEADD(SECOND, ?,
'1970-01-01 00:00:00') ";
+ case "dm":
+ case "oscar":
+ // Compatible with Oracle syntax
+ return " and " + timeColumnName + " <=
TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL(?, 'SECOND') ";
+ default:
+ throw new IllegalArgumentException("Unsupported DB type: " +
dbType);
+ }
+ }
}
diff --git
a/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
b/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
index 4e5cd95cdc..66666d6e37 100644
---
a/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
+++
b/server/src/main/java/org/apache/seata/server/console/impl/db/GlobalLockDBServiceImpl.java
@@ -171,11 +171,11 @@ public class GlobalLockDBServiceImpl extends
AbstractLockService implements Glob
sqlParamList.add(param.getBranchId());
}
if (param.getTimeStart() != null) {
- whereConditionBuilder.append(PageUtil.getTimeStartSql(this.dbType,
"gmt_create"));
+
whereConditionBuilder.append(PageUtil.getDateTimeStartSql(this.dbType,
"gmt_create"));
sqlParamList.add(param.getTimeStart() / 1000);
}
if (param.getTimeEnd() != null) {
- whereConditionBuilder.append(PageUtil.getTimeEndSql(this.dbType,
"gmt_create"));
+
whereConditionBuilder.append(PageUtil.getDateTimeEndSql(this.dbType,
"gmt_create"));
sqlParamList.add(param.getTimeEnd() / 1000);
}
String whereCondition = whereConditionBuilder.toString();
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
