This is an automated email from the ASF dual-hosted git repository.
corgy pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/seatunnel.git
The following commit(s) were added to refs/heads/dev by this push:
new bdd9c1d3b9 [Improve][Transform-V2] Add whitelist-based validation for
datetime format patterns in PARSEDATETIME/TO_DATE (#10360)
bdd9c1d3b9 is described below
commit bdd9c1d3b96c242e262f4aeecdb51a3ea48b83da
Author: aksmf1442 <[email protected]>
AuthorDate: Fri Feb 27 23:37:47 2026 +0900
[Improve][Transform-V2] Add whitelist-based validation for datetime format
patterns in PARSEDATETIME/TO_DATE (#10360)
---
.../introduction/concepts/incompatible-changes.md | 8 +
docs/en/transforms/sql-functions.md | 91 +++++++-
.../introduction/concepts/incompatible-changes.md | 9 +
docs/zh/transforms/sql-functions.md | 94 +++++++-
.../transform/sql/zeta/ZetaDateTimeFormat.java | 75 ++++++
.../seatunnel/transform/sql/zeta/ZetaSQLType.java | 40 ++--
.../sql/zeta/functions/DateTimeFunction.java | 80 +++++--
.../transform/sql/zeta/ZetaDateTimeFormatTest.java | 260 +++++++++++++++++++++
.../transform/sql/zeta/ZetaSQLTypeTest.java | 4 +-
.../sql/zeta/functions/DateTimeFunctionsTest.java | 166 ++++++++++++-
10 files changed, 779 insertions(+), 48 deletions(-)
diff --git a/docs/en/introduction/concepts/incompatible-changes.md
b/docs/en/introduction/concepts/incompatible-changes.md
index 940db9350c..7cdb776dde 100644
--- a/docs/en/introduction/concepts/incompatible-changes.md
+++ b/docs/en/introduction/concepts/incompatible-changes.md
@@ -37,6 +37,14 @@ You need to check this document before you upgrade to
related version.
### Transform Changes
+- **[BREAKING]** SQL Transform `PARSEDATETIME`, `TO_DATE`, and `IS_DATE`
functions now only accept whitelisted datetime format patterns. Custom format
patterns that were previously accepted will now fail at runtime. The supported
patterns are:
+ - DateTime: `yyyy-MM-dd HH:mm:ss`, `yyyy-MM-dd HH:mm:ss.SSS`,
`yyyy-MM-dd'T'HH:mm:ss`, `yyyy-MM-dd'T'HH:mm:ss.SSS`, `yyyy/MM/dd HH:mm:ss`,
`yyyy/MM/dd HH:mm:ss.SSS`, `yyyyMMddHHmmss`
+ - Date: `yyyy-MM-dd`, `yyyy/MM/dd`, `yyyyMMdd`
+ - Time: `HH:mm:ss`, `HH:mm:ss.SSS`, `HHmmss`
+
+ **Exception Type Change**: Invalid datetime format patterns now throw
`SeaTunnelRuntimeException` instead of `TransformException`. If you have error
handling or monitoring systems that catch `TransformException` for datetime
parsing errors, you will need to update them to handle
`SeaTunnelRuntimeException`.
+
+ **Migration Guide**: If you are using custom datetime format patterns in
`PARSEDATETIME`, `TO_DATE`, or `IS_DATE` functions, you must update your
queries to use one of the supported patterns above. If your data uses a
different format, you may need to preprocess the input data to match a
supported format, or use string manipulation functions to transform the format
before parsing.
- DataValidator transform: In `row_error_handle_way = ROUTE_TO_TABLE` mode,
the routed error row `table_id` now includes the upstream database/schema
prefix (for example, `db1.ffp` / `db1.schema1.ffp` instead of `ffp`).
- Adjusted SQL Transform date & time functions:
- `DATEDIFF(<start>, <end>, 'MONTH')` now returns the total number of months
between the two dates across years (for example, from `2023-01-01` to
`2024-03-01` returns `14` instead of `15`).
diff --git a/docs/en/transforms/sql-functions.md
b/docs/en/transforms/sql-functions.md
index 22d0c6b4e9..2c6bd6bc17 100644
--- a/docs/en/transforms/sql-functions.md
+++ b/docs/en/transforms/sql-functions.md
@@ -889,22 +889,97 @@ MONTHNAME(CREATED)
### IS_DATE
```IS_DATE(string, formatString) -> BOOLEAN```
-Parses a string. The most important format characters are: y year, M month, d
day, H hour, m minute, s second. For details of the format, see
java.time.format.DateTimeFormatter.
+Validates whether a string can be parsed as a date/time value using the
specified format pattern.
+
+**Supported Format Patterns:**
+
+DateTime Formats:
+- `yyyy-MM-dd HH:mm:ss` - Standard datetime format
+- `yyyy-MM-dd HH:mm:ss.SSS` - Datetime with milliseconds
+- `yyyy-MM-dd'T'HH:mm:ss` - ISO 8601 datetime format
+- `yyyy-MM-dd'T'HH:mm:ss.SSS` - ISO 8601 datetime with milliseconds
+- `yyyy/MM/dd HH:mm:ss` - Datetime with slash separator
+- `yyyy/MM/dd HH:mm:ss.SSS` - Datetime with slash separator and milliseconds
+- `yyyyMMddHHmmss` - Compact datetime format
+
+Date Formats:
+- `yyyy-MM-dd` - ISO 8601 date format
+- `yyyy/MM/dd` - Date with slash separator
+- `yyyyMMdd` - Compact date format
+
+Time Formats:
+- `HH:mm:ss` - Standard time format
+- `HH:mm:ss.SSS` - Time with milliseconds
+- `HHmmss` - Compact time format
Example:
-CALL IS_DATE('2021-04-08 13:34:45','yyyy-MM-dd HH:mm:ss')
+```sql
+CALL IS_DATE('2021-04-08 13:34:45', 'yyyy-MM-dd HH:mm:ss')
+-- Returns true
+
+CALL IS_DATE('2021/04/08', 'yyyy/MM/dd')
+-- Returns true
+
+CALL IS_DATE('20210408', 'yyyyMMdd')
+-- Returns true
+
+-- Consistent with TO_DATE
+SELECT CASE
+ WHEN IS_DATE(date_string, 'yyyy-MM-dd HH:mm:ss')
+ THEN TO_DATE(date_string, 'yyyy-MM-dd HH:mm:ss')
+ ELSE NULL
+END as parsed_date
+```
### PARSEDATETIME / TO_DATE
-```PARSEDATETIME | TO_DATE(string, formatString) -> TIMESTAMP```
-Parses a string. The most important format characters are: y year, M month, d
day, H hour, m minute, s second. For details of the format, see
java.time.format.DateTimeFormatter.
+```PARSEDATETIME | TO_DATE(string, formatString) -> TIMESTAMP | DATE | TIME```
+Parses a string into a date/time value using the specified format pattern.
-Example:
+**Supported Format Patterns:**
+
+DateTime Formats (returns TIMESTAMP):
+- `yyyy-MM-dd HH:mm:ss` - Standard datetime format
+- `yyyy-MM-dd HH:mm:ss.SSS` - Datetime with milliseconds
+- `yyyy-MM-dd'T'HH:mm:ss` - ISO 8601 datetime format
+- `yyyy-MM-dd'T'HH:mm:ss.SSS` - ISO 8601 datetime with milliseconds
+- `yyyy/MM/dd HH:mm:ss` - Datetime with slash separator
+- `yyyy/MM/dd HH:mm:ss.SSS` - Datetime with slash separator and milliseconds
+- `yyyyMMddHHmmss` - Compact datetime format
+
+Date Formats (returns DATE):
+- `yyyy-MM-dd` - ISO 8601 date format
+- `yyyy/MM/dd` - Date with slash separator
+- `yyyyMMdd` - Compact date format
+
+Time Formats (returns TIME):
+- `HH:mm:ss` - Standard time format
+- `HH:mm:ss.SSS` - Time with milliseconds
+- `HHmmss` - Compact time format
+
+**Note:** When using single quotes (`'`) in format patterns (e.g., for ISO
8601 'T' separator), they must be escaped as `''` in SQL.
-CALL PARSEDATETIME('2021-04-08 13:34:45','yyyy-MM-dd HH:mm:ss')
-CALL TO_DATE('2021-04-08'T'13:34:45','yyyy-MM-dd''T''HH:mm:ss')
-Note that when filling in `'` in SQL functions, it needs to be escaped to `''`.
+Examples:
+
+```sql
+-- DateTime examples
+CALL PARSEDATETIME('2021-04-08 13:34:45', 'yyyy-MM-dd HH:mm:ss')
+CALL TO_DATE('2021-04-08T13:34:45', 'yyyy-MM-dd''T''HH:mm:ss')
+CALL PARSEDATETIME('2024-06-15 14:30:45.123', 'yyyy-MM-dd HH:mm:ss.SSS')
+CALL PARSEDATETIME('2021/04/08 13:34:45', 'yyyy/MM/dd HH:mm:ss')
+CALL PARSEDATETIME('20210408133445', 'yyyyMMddHHmmss')
+
+-- Date examples
+CALL TO_DATE('2021-04-08', 'yyyy-MM-dd')
+CALL TO_DATE('2021/04/08', 'yyyy/MM/dd')
+CALL TO_DATE('20210408', 'yyyyMMdd')
+
+-- Time examples
+CALL PARSEDATETIME('14:30:45', 'HH:mm:ss')
+CALL PARSEDATETIME('14:30:45.123', 'HH:mm:ss.SSS')
+CALL PARSEDATETIME('143045', 'HHmmss')
+```
### QUARTER
diff --git a/docs/zh/introduction/concepts/incompatible-changes.md
b/docs/zh/introduction/concepts/incompatible-changes.md
index 13fe82f5ce..784729cec6 100644
--- a/docs/zh/introduction/concepts/incompatible-changes.md
+++ b/docs/zh/introduction/concepts/incompatible-changes.md
@@ -36,6 +36,15 @@
### 转换变更
+- **[BREAKING]** SQL Transform 的 `PARSEDATETIME`、`TO_DATE` 和 `IS_DATE`
函数现在只接受白名单中的日期时间格式模式。以前接受的自定义格式模式现在将在运行时失败。支持的模式有：
+ - DateTime: `yyyy-MM-dd HH:mm:ss`, `yyyy-MM-dd HH:mm:ss.SSS`,
`yyyy-MM-dd'T'HH:mm:ss`, `yyyy-MM-dd'T'HH:mm:ss.SSS`, `yyyy/MM/dd HH:mm:ss`,
`yyyy/MM/dd HH:mm:ss.SSS`, `yyyyMMddHHmmss`
+ - Date: `yyyy-MM-dd`, `yyyy/MM/dd`, `yyyyMMdd`
+ - Time: `HH:mm:ss`, `HH:mm:ss.SSS`, `HHmmss`
+
+ **异常类型变更**: 无效的日期时间格式模式现在会抛出 `SeaTunnelRuntimeException` 而不是
`TransformException`。如果您的错误处理或监控系统捕获 `TransformException`
来处理日期时间解析错误，您需要更新它们以处理 `SeaTunnelRuntimeException`。
+
+ **迁移指南**: 如果您在 `PARSEDATETIME`、`TO_DATE` 或 `IS_DATE`
函数中使用自定义日期时间格式模式，您必须更新查询以使用上述支持的模式之一。如果您的数据使用不同的格式，您可能需要预处理输入数据以匹配支持的格式，或使用字符串操作函数在解析之前转换格式。
+
- DataValidator 转换：当 `row_error_handle_way = ROUTE_TO_TABLE` 时，路由到错误表的行
`table_id` 现在会携带上游的 database/schema 前缀（例如从 `ffp` 变为 `db1.ffp` /
`db1.schema1.ffp`）。
### 引擎行为变更
diff --git a/docs/zh/transforms/sql-functions.md
b/docs/zh/transforms/sql-functions.md
index c5fe31e5d9..5a2536fa42 100644
--- a/docs/zh/transforms/sql-functions.md
+++ b/docs/zh/transforms/sql-functions.md
@@ -890,17 +890,101 @@ MONTH(CREATED)
MONTHNAME(CREATED)
+### IS_DATE
+
+```IS_DATE(string, formatString) -> BOOLEAN```
+验证字符串是否可以使用指定的格式模式解析为日期/时间值。
+
+**支持的格式模式:**
+
+日期时间格式:
+- `yyyy-MM-dd HH:mm:ss` - 标准日期时间格式
+- `yyyy-MM-dd HH:mm:ss.SSS` - 带毫秒的日期时间
+- `yyyy-MM-dd'T'HH:mm:ss` - ISO 8601 日期时间格式
+- `yyyy-MM-dd'T'HH:mm:ss.SSS` - 带毫秒的 ISO 8601 日期时间
+- `yyyy/MM/dd HH:mm:ss` - 带斜杠分隔符的日期时间
+- `yyyy/MM/dd HH:mm:ss.SSS` - 带斜杠分隔符和毫秒的日期时间
+- `yyyyMMddHHmmss` - 紧凑日期时间格式
+
+日期格式:
+- `yyyy-MM-dd` - ISO 8601 日期格式
+- `yyyy/MM/dd` - 带斜杠分隔符的日期
+- `yyyyMMdd` - 紧凑日期格式
+
+时间格式:
+- `HH:mm:ss` - 标准时间格式
+- `HH:mm:ss.SSS` - 带毫秒的时间
+- `HHmmss` - 紧凑时间格式
+
+示例:
+
+```sql
+CALL IS_DATE('2021-04-08 13:34:45', 'yyyy-MM-dd HH:mm:ss')
+-- 返回 true
+
+CALL IS_DATE('2021/04/08', 'yyyy/MM/dd')
+-- 返回 true
+
+CALL IS_DATE('20210408', 'yyyyMMdd')
+-- 返回 true
+
+-- 与 TO_DATE 保持一致
+SELECT CASE
+ WHEN IS_DATE(date_string, 'yyyy-MM-dd HH:mm:ss')
+ THEN TO_DATE(date_string, 'yyyy-MM-dd HH:mm:ss')
+ ELSE NULL
+END as parsed_date
+```
+
### PARSEDATETIME / TO_DATE
-```PARSEDATETIME | TO_DATE(string, formatString) -> TIMESTAMP```
+```PARSEDATETIME | TO_DATE(string, formatString) -> TIMESTAMP | DATE | TIME```
+
+使用指定的格式模式将字符串解析为日期/时间值
+
+**支持的格式模式:**
-解析一个字符串并返回一个 TIMESTAMP WITH TIME ZONE
值。最重要的格式字符包括：y（年）、M（月）、d（日）、H（时）、m（分）、s（秒）。有关格式的详细信息，请参阅
java.time.format.DateTimeFormatter。
+日期时间格式 (返回 TIMESTAMP):
+- `yyyy-MM-dd HH:mm:ss` - 标准日期时间格式
+- `yyyy-MM-dd HH:mm:ss.SSS` - 带毫秒的日期时间
+- `yyyy-MM-dd'T'HH:mm:ss` - ISO 8601 日期时间格式
+- `yyyy-MM-dd'T'HH:mm:ss.SSS` - 带毫秒的 ISO 8601 日期时间
+- `yyyy/MM/dd HH:mm:ss` - 带斜杠分隔符的日期时间
+- `yyyy/MM/dd HH:mm:ss.SSS` - 带斜杠分隔符和毫秒的日期时间
+- `yyyyMMddHHmmss` - 紧凑日期时间格式
+
+日期格式 (返回 DATE):
+- `yyyy-MM-dd` - ISO 8601 日期格式
+- `yyyy/MM/dd` - 带斜杠分隔符的日期
+- `yyyyMMdd` - 紧凑日期格式
+
+时间格式 (返回 TIME):
+- `HH:mm:ss` - 标准时间格式
+- `HH:mm:ss.SSS` - 带毫秒的时间
+- `HHmmss` - 紧凑时间格式
+
+**注意:** 在格式模式中使用单引号 (`'`) 时(例如 ISO 8601 的 'T' 分隔符)，必须在 SQL 中转义为 `''`。
示例:
-CALL PARSEDATETIME('2021-04-08 13:34:45','yyyy-MM-dd HH:mm:ss')
-CALL TO_DATE('2021-04-08T13:34:45','yyyy-MM-dd''T''HH:mm:ss')
-注意SQL函数中的`'`填写时需要转义为`''`。
+```sql
+-- 日期时间示例
+CALL PARSEDATETIME('2021-04-08 13:34:45', 'yyyy-MM-dd HH:mm:ss')
+CALL TO_DATE('2021-04-08T13:34:45', 'yyyy-MM-dd''T''HH:mm:ss')
+CALL PARSEDATETIME('2024-06-15 14:30:45.123', 'yyyy-MM-dd HH:mm:ss.SSS')
+CALL PARSEDATETIME('2021/04/08 13:34:45', 'yyyy/MM/dd HH:mm:ss')
+CALL PARSEDATETIME('20210408133445', 'yyyyMMddHHmmss')
+
+-- 日期示例
+CALL TO_DATE('2021-04-08', 'yyyy-MM-dd')
+CALL TO_DATE('2021/04/08', 'yyyy/MM/dd')
+CALL TO_DATE('20210408', 'yyyyMMdd')
+
+-- 时间示例
+CALL PARSEDATETIME('14:30:45', 'HH:mm:ss')
+CALL PARSEDATETIME('14:30:45.123', 'HH:mm:ss.SSS')
+CALL PARSEDATETIME('143045', 'HHmmss')
+```
### QUARTER
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormat.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormat.java
new file mode 100644
index 0000000000..84412f4a8f
--- /dev/null
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormat.java
@@ -0,0 +1,75 @@
+/*
+ * 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.
+ */
+
+package org.apache.seatunnel.transform.sql.zeta;
+
+import java.time.format.DateTimeFormatter;
+import java.util.Arrays;
+import java.util.Optional;
+
+public enum ZetaDateTimeFormat {
+ // DateTime formats
+ DATETIME_STANDARD("yyyy-MM-dd HH:mm:ss", FormatType.DATETIME),
+ DATETIME_WITH_MILLIS("yyyy-MM-dd HH:mm:ss.SSS", FormatType.DATETIME),
+ DATETIME_ISO8601("yyyy-MM-dd'T'HH:mm:ss", FormatType.DATETIME),
+ DATETIME_ISO8601_WITH_MILLIS("yyyy-MM-dd'T'HH:mm:ss.SSS",
FormatType.DATETIME),
+ DATETIME_SLASH("yyyy/MM/dd HH:mm:ss", FormatType.DATETIME),
+ DATETIME_SLASH_WITH_MILLIS("yyyy/MM/dd HH:mm:ss.SSS", FormatType.DATETIME),
+ DATETIME_COMPACT("yyyyMMddHHmmss", FormatType.DATETIME),
+
+ // Date formats
+ DATE_ISO8601("yyyy-MM-dd", FormatType.DATE),
+ DATE_SLASH("yyyy/MM/dd", FormatType.DATE),
+ DATE_COMPACT("yyyyMMdd", FormatType.DATE),
+
+ // Time formats
+ TIME_STANDARD("HH:mm:ss", FormatType.TIME),
+ TIME_WITH_MILLIS("HH:mm:ss.SSS", FormatType.TIME),
+ TIME_COMPACT("HHmmss", FormatType.TIME);
+
+ private final String pattern;
+ private final FormatType type;
+ private final DateTimeFormatter formatter;
+
+ ZetaDateTimeFormat(String pattern, FormatType type) {
+ this.pattern = pattern;
+ this.type = type;
+ this.formatter = DateTimeFormatter.ofPattern(pattern);
+ }
+
+ public String getPattern() {
+ return pattern;
+ }
+
+ public FormatType getType() {
+ return type;
+ }
+
+ public DateTimeFormatter getFormatter() {
+ return formatter;
+ }
+
+ public static Optional<ZetaDateTimeFormat> fromPattern(String pattern) {
+ return Arrays.stream(values()).filter(format ->
format.pattern.equals(pattern)).findFirst();
+ }
+
+ public enum FormatType {
+ DATETIME,
+ DATE,
+ TIME
+ }
+}
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java
index 73c92a17fe..5988f0869d 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLType.java
@@ -26,6 +26,7 @@ import org.apache.seatunnel.api.table.type.SeaTunnelDataType;
import org.apache.seatunnel.api.table.type.SeaTunnelRowType;
import org.apache.seatunnel.api.table.type.SqlType;
import org.apache.seatunnel.api.table.type.VectorType;
+import org.apache.seatunnel.common.exception.CommonError;
import org.apache.seatunnel.common.exception.CommonErrorCodeDeprecated;
import org.apache.seatunnel.transform.exception.TransformException;
import org.apache.seatunnel.transform.sql.zeta.functions.ArrayFunction;
@@ -430,21 +431,34 @@ public class ZetaSQLType {
case ZetaSQLFunction.PARSEDATETIME:
case ZetaSQLFunction.TO_DATE:
{
- String format =
function.getParameters().getExpressions().get(1).toString();
- if (format.contains("yy") && format.contains("mm")) {
- return LocalTimeType.LOCAL_DATE_TIME_TYPE;
+ Expression formatExpr =
function.getParameters().getExpressions().get(1);
+ String format;
+ if (formatExpr instanceof StringValue) {
+ format = ((StringValue)
formatExpr).getNotExcapedValue();
+ } else {
+ throw CommonError.unsupportedOperation(
+ function.getName(), "non-literal format
parameter");
}
- if (format.contains("yy")) {
- return LocalTimeType.LOCAL_DATE_TYPE;
- }
- if (format.contains("mm")) {
- return LocalTimeType.LOCAL_TIME_TYPE;
+
+ ZetaDateTimeFormat dateTimeFormat =
+ ZetaDateTimeFormat.fromPattern(format)
+ .orElseThrow(
+ () ->
+
CommonError.illegalArgument(
+ format,
"unsupported datetime format"));
+
+ switch (dateTimeFormat.getType()) {
+ case DATETIME:
+ return LocalTimeType.LOCAL_DATE_TIME_TYPE;
+ case DATE:
+ return LocalTimeType.LOCAL_DATE_TYPE;
+ case TIME:
+ return LocalTimeType.LOCAL_TIME_TYPE;
+ default:
+ throw CommonError.illegalArgument(
+ dateTimeFormat.getType().toString(),
+ "unsupported datetime format type");
}
- throw new TransformException(
- CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
- String.format(
- "Unknown pattern letter %s for function:
%s",
- format, function.getName()));
}
case ZetaSQLFunction.ABS:
case ZetaSQLFunction.DATEADD:
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
index bfb00f9dfa..4539acd093 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunction.java
@@ -17,8 +17,10 @@
package org.apache.seatunnel.transform.sql.zeta.functions;
+import org.apache.seatunnel.common.exception.CommonError;
import org.apache.seatunnel.common.exception.CommonErrorCodeDeprecated;
import org.apache.seatunnel.transform.exception.TransformException;
+import org.apache.seatunnel.transform.sql.zeta.ZetaDateTimeFormat;
import org.apache.seatunnel.transform.sql.zeta.ZetaSQLFunction;
import java.text.DateFormatSymbols;
@@ -32,6 +34,7 @@ import java.time.Period;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
+import java.time.format.DateTimeParseException;
import java.time.temporal.Temporal;
import java.time.temporal.TemporalAccessor;
import java.time.temporal.WeekFields;
@@ -619,10 +622,38 @@ public class DateTimeFunction {
}
public static boolean isDate(List<Object> args) {
+ String str = (String) args.get(0);
+ if (str == null || str.isEmpty()) {
+ return false;
+ }
+
+ String format = (String) args.get(1);
+ if (format == null) {
+ return false;
+ }
+
+ ZetaDateTimeFormat dateTimeFormat =
ZetaDateTimeFormat.fromPattern(format).orElse(null);
+ if (dateTimeFormat == null) {
+ return false;
+ }
+
try {
- parsedatetime(args);
- return true;
- } catch (Throwable e) {
+ DateTimeFormatter formatter = dateTimeFormat.getFormatter();
+
+ switch (dateTimeFormat.getType()) {
+ case DATETIME:
+ LocalDateTime.parse(str, formatter);
+ return true;
+ case DATE:
+ LocalDate.parse(str, formatter);
+ return true;
+ case TIME:
+ LocalTime.parse(str, formatter);
+ return true;
+ default:
+ return false;
+ }
+ } catch (DateTimeParseException e) {
return false;
}
}
@@ -633,23 +664,32 @@ public class DateTimeFunction {
return null;
}
String format = (String) args.get(1);
- if (format.contains("yy") && format.contains("mm")) {
- DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
- return LocalDateTime.parse(str, df);
- }
- if (format.contains("yy")) {
- DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
- return LocalDate.parse(str, df);
- }
- if (format.contains("mm")) {
- DateTimeFormatter df = DateTimeFormatter.ofPattern(format);
- return LocalTime.parse(str, df);
- }
- throw new TransformException(
- CommonErrorCodeDeprecated.UNSUPPORTED_OPERATION,
- String.format(
- "Unknown pattern letter %s for function: %s",
- format, ZetaSQLFunction.PARSEDATETIME));
+
+ ZetaDateTimeFormat dateTimeFormat =
+ ZetaDateTimeFormat.fromPattern(format)
+ .orElseThrow(
+ () ->
+ CommonError.illegalArgument(
+ format, "unsupported datetime
format"));
+
+ try {
+ DateTimeFormatter formatter = dateTimeFormat.getFormatter();
+
+ switch (dateTimeFormat.getType()) {
+ case DATETIME:
+ return LocalDateTime.parse(str, formatter);
+ case DATE:
+ return LocalDate.parse(str, formatter);
+ case TIME:
+ return LocalTime.parse(str, formatter);
+ default:
+ throw CommonError.illegalArgument(
+ dateTimeFormat.getType().toString(),
+ "unsupported datetime format type");
+ }
+ } catch (DateTimeParseException e) {
+ throw CommonError.illegalArgument(str, "parsing datetime with
format: " + format);
+ }
}
public static Integer quarter(List<Object> args) {
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormatTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormatTest.java
new file mode 100644
index 0000000000..af68ca9986
--- /dev/null
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaDateTimeFormatTest.java
@@ -0,0 +1,260 @@
+/*
+ * 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.
+ */
+
+package org.apache.seatunnel.transform.sql.zeta;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.Optional;
+
+public class ZetaDateTimeFormatTest {
+
+ @Test
+ public void testFromPatternWithAllDateTimeFormats() {
+ // DATETIME_STANDARD
+ Optional<ZetaDateTimeFormat> format1 =
+ ZetaDateTimeFormat.fromPattern("yyyy-MM-dd HH:mm:ss");
+ Assertions.assertTrue(format1.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_STANDARD,
format1.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format1.get().getType());
+
+ // DATETIME_WITH_MILLIS
+ Optional<ZetaDateTimeFormat> format2 =
+ ZetaDateTimeFormat.fromPattern("yyyy-MM-dd HH:mm:ss.SSS");
+ Assertions.assertTrue(format2.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_WITH_MILLIS,
format2.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format2.get().getType());
+
+ // DATETIME_ISO8601
+ Optional<ZetaDateTimeFormat> format3 =
+ ZetaDateTimeFormat.fromPattern("yyyy-MM-dd'T'HH:mm:ss");
+ Assertions.assertTrue(format3.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_ISO8601,
format3.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format3.get().getType());
+
+ // DATETIME_ISO8601_WITH_MILLIS
+ Optional<ZetaDateTimeFormat> format4 =
+ ZetaDateTimeFormat.fromPattern("yyyy-MM-dd'T'HH:mm:ss.SSS");
+ Assertions.assertTrue(format4.isPresent());
+
Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS,
format4.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format4.get().getType());
+
+ // DATETIME_SLASH
+ Optional<ZetaDateTimeFormat> format5 =
+ ZetaDateTimeFormat.fromPattern("yyyy/MM/dd HH:mm:ss");
+ Assertions.assertTrue(format5.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_SLASH,
format5.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format5.get().getType());
+
+ // DATETIME_SLASH_WITH_MILLIS
+ Optional<ZetaDateTimeFormat> format6 =
+ ZetaDateTimeFormat.fromPattern("yyyy/MM/dd HH:mm:ss.SSS");
+ Assertions.assertTrue(format6.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_SLASH_WITH_MILLIS,
format6.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format6.get().getType());
+
+ // DATETIME_COMPACT
+ Optional<ZetaDateTimeFormat> format7 =
ZetaDateTimeFormat.fromPattern("yyyyMMddHHmmss");
+ Assertions.assertTrue(format7.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATETIME_COMPACT,
format7.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATETIME,
format7.get().getType());
+ }
+
+ @Test
+ public void testFromPatternWithAllDateFormats() {
+ // DATE_ISO8601
+ Optional<ZetaDateTimeFormat> format1 =
ZetaDateTimeFormat.fromPattern("yyyy-MM-dd");
+ Assertions.assertTrue(format1.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATE_ISO8601,
format1.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATE,
format1.get().getType());
+
+ // DATE_SLASH
+ Optional<ZetaDateTimeFormat> format2 =
ZetaDateTimeFormat.fromPattern("yyyy/MM/dd");
+ Assertions.assertTrue(format2.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATE_SLASH, format2.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATE,
format2.get().getType());
+
+ // DATE_COMPACT
+ Optional<ZetaDateTimeFormat> format3 =
ZetaDateTimeFormat.fromPattern("yyyyMMdd");
+ Assertions.assertTrue(format3.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.DATE_COMPACT,
format3.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.DATE,
format3.get().getType());
+ }
+
+ @Test
+ public void testFromPatternWithAllTimeFormats() {
+ // TIME_STANDARD
+ Optional<ZetaDateTimeFormat> format1 =
ZetaDateTimeFormat.fromPattern("HH:mm:ss");
+ Assertions.assertTrue(format1.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.TIME_STANDARD,
format1.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.TIME,
format1.get().getType());
+
+ // TIME_WITH_MILLIS
+ Optional<ZetaDateTimeFormat> format2 =
ZetaDateTimeFormat.fromPattern("HH:mm:ss.SSS");
+ Assertions.assertTrue(format2.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.TIME_WITH_MILLIS,
format2.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.TIME,
format2.get().getType());
+
+ // TIME_COMPACT
+ Optional<ZetaDateTimeFormat> format3 =
ZetaDateTimeFormat.fromPattern("HHmmss");
+ Assertions.assertTrue(format3.isPresent());
+ Assertions.assertEquals(ZetaDateTimeFormat.TIME_COMPACT,
format3.get());
+ Assertions.assertEquals(ZetaDateTimeFormat.FormatType.TIME,
format3.get().getType());
+ }
+
+ @Test
+ public void testFromPatternWithInvalidFormat() {
+ Optional<ZetaDateTimeFormat> format =
ZetaDateTimeFormat.fromPattern("invalid_pattern");
+
+ Assertions.assertFalse(format.isPresent());
+ }
+
+ @Test
+ public void testFromPatternWithNullFormat() {
+ Optional<ZetaDateTimeFormat> format =
ZetaDateTimeFormat.fromPattern(null);
+
+ Assertions.assertFalse(format.isPresent());
+ }
+
+ @Test
+ public void testAllDateTimeFormatsHaveCorrectType() {
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_STANDARD.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_WITH_MILLIS.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_ISO8601.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_SLASH.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_SLASH_WITH_MILLIS.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATETIME,
+ ZetaDateTimeFormat.DATETIME_COMPACT.getType());
+ }
+
+ @Test
+ public void testAllDateFormatsHaveCorrectType() {
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATE,
ZetaDateTimeFormat.DATE_ISO8601.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATE,
ZetaDateTimeFormat.DATE_SLASH.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.DATE,
ZetaDateTimeFormat.DATE_COMPACT.getType());
+ }
+
+ @Test
+ public void testAllTimeFormatsHaveCorrectType() {
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.TIME,
ZetaDateTimeFormat.TIME_STANDARD.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.TIME,
ZetaDateTimeFormat.TIME_WITH_MILLIS.getType());
+ Assertions.assertEquals(
+ ZetaDateTimeFormat.FormatType.TIME,
ZetaDateTimeFormat.TIME_COMPACT.getType());
+ }
+
+ @Test
+ public void testGetPatternForAllFormats() {
+ Assertions.assertEquals(
+ "yyyy-MM-dd HH:mm:ss",
ZetaDateTimeFormat.DATETIME_STANDARD.getPattern());
+ Assertions.assertEquals(
+ "yyyy-MM-dd HH:mm:ss.SSS",
ZetaDateTimeFormat.DATETIME_WITH_MILLIS.getPattern());
+ Assertions.assertEquals(
+ "yyyy-MM-dd'T'HH:mm:ss",
ZetaDateTimeFormat.DATETIME_ISO8601.getPattern());
+ Assertions.assertEquals(
+ "yyyy-MM-dd'T'HH:mm:ss.SSS",
+ ZetaDateTimeFormat.DATETIME_ISO8601_WITH_MILLIS.getPattern());
+ Assertions.assertEquals(
+ "yyyy/MM/dd HH:mm:ss",
ZetaDateTimeFormat.DATETIME_SLASH.getPattern());
+ Assertions.assertEquals(
+ "yyyy/MM/dd HH:mm:ss.SSS",
+ ZetaDateTimeFormat.DATETIME_SLASH_WITH_MILLIS.getPattern());
+ Assertions.assertEquals("yyyyMMddHHmmss",
ZetaDateTimeFormat.DATETIME_COMPACT.getPattern());
+
+ Assertions.assertEquals("yyyy-MM-dd",
ZetaDateTimeFormat.DATE_ISO8601.getPattern());
+ Assertions.assertEquals("yyyy/MM/dd",
ZetaDateTimeFormat.DATE_SLASH.getPattern());
+ Assertions.assertEquals("yyyyMMdd",
ZetaDateTimeFormat.DATE_COMPACT.getPattern());
+
+ Assertions.assertEquals("HH:mm:ss",
ZetaDateTimeFormat.TIME_STANDARD.getPattern());
+ Assertions.assertEquals("HH:mm:ss.SSS",
ZetaDateTimeFormat.TIME_WITH_MILLIS.getPattern());
+ Assertions.assertEquals("HHmmss",
ZetaDateTimeFormat.TIME_COMPACT.getPattern());
+ }
+
+ @Test
+ public void testFromPatternIsCaseSensitive() {
+ Optional<ZetaDateTimeFormat> format =
ZetaDateTimeFormat.fromPattern("YYYY-MM-DD HH:MM:SS");
+
+ Assertions.assertFalse(format.isPresent());
+ }
+
+ @Test
+ public void testAllEnumValuesAreUnique() {
+ ZetaDateTimeFormat[] formats = ZetaDateTimeFormat.values();
+
+ for (int i = 0; i < formats.length; i++) {
+ for (int j = i + 1; j < formats.length; j++) {
+ Assertions.assertNotEquals(
+ formats[i].getPattern(),
+ formats[j].getPattern(),
+ "Duplicate pattern found: " + formats[i].getPattern());
+ }
+ }
+ }
+
+ @Test
+ public void testFormatterIsCached() {
+ ZetaDateTimeFormat format = ZetaDateTimeFormat.DATETIME_STANDARD;
+ Assertions.assertNotNull(format.getFormatter());
+ Assertions.assertSame(
+ format.getFormatter(),
+ format.getFormatter(),
+ "Formatter should be cached and return the same instance");
+ }
+
+ @Test
+ public void testAllFormatsHaveValidFormatter() {
+ for (ZetaDateTimeFormat format : ZetaDateTimeFormat.values()) {
+ Assertions.assertNotNull(
+ format.getFormatter(),
+ "Format " + format.name() + " should have a valid
formatter");
+ }
+ }
+
+ @Test
+ public void testFormatterCanParseValidInput() {
+ ZetaDateTimeFormat format = ZetaDateTimeFormat.DATE_ISO8601;
+ Assertions.assertDoesNotThrow(
+ () -> java.time.LocalDate.parse("2024-06-15",
format.getFormatter()));
+
+ ZetaDateTimeFormat compactFormat = ZetaDateTimeFormat.DATE_COMPACT;
+ Assertions.assertDoesNotThrow(
+ () -> java.time.LocalDate.parse("20240615",
compactFormat.getFormatter()));
+
+ ZetaDateTimeFormat slashFormat = ZetaDateTimeFormat.DATE_SLASH;
+ Assertions.assertDoesNotThrow(
+ () -> java.time.LocalDate.parse("2024/06/15",
slashFormat.getFormatter()));
+ }
+}
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java
index 0499b701ba..c56e4f6ed4 100644
---
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/ZetaSQLTypeTest.java
@@ -26,6 +26,7 @@ import org.apache.seatunnel.api.table.type.SeaTunnelDataType;
import org.apache.seatunnel.api.table.type.SeaTunnelRowType;
import org.apache.seatunnel.api.table.type.SqlType;
import org.apache.seatunnel.api.table.type.VectorType;
+import org.apache.seatunnel.common.exception.SeaTunnelRuntimeException;
import org.apache.seatunnel.transform.exception.TransformException;
import org.apache.seatunnel.transform.sql.zeta.functions.udf.DesEncrypt;
@@ -271,7 +272,8 @@ public class ZetaSQLTypeTest {
badPattern.setParameters(
new ExpressionList<>(
Arrays.asList(new StringValue("data"), new
StringValue("invalid"))));
- Assertions.assertThrows(TransformException.class, () ->
type.getExpressionType(badPattern));
+ Assertions.assertThrows(
+ SeaTunnelRuntimeException.class, () ->
type.getExpressionType(badPattern));
Assertions.assertEquals(
LocalTimeType.LOCAL_DATE_TYPE,
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java
index 7cd773d1cc..3b67ebe871 100644
---
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/DateTimeFunctionsTest.java
@@ -24,6 +24,7 @@ import org.apache.seatunnel.api.table.type.LocalTimeType;
import org.apache.seatunnel.api.table.type.SeaTunnelDataType;
import org.apache.seatunnel.api.table.type.SeaTunnelRow;
import org.apache.seatunnel.api.table.type.SeaTunnelRowType;
+import org.apache.seatunnel.common.exception.SeaTunnelRuntimeException;
import org.apache.seatunnel.transform.exception.TransformException;
import org.apache.seatunnel.transform.sql.SQLTransform;
@@ -303,7 +304,7 @@ public class DateTimeFunctionsTest {
new SeaTunnelDataType[]
{LocalTimeType.LOCAL_DATE_TIME_TYPE});
Assertions.assertThrows(
- TransformException.class,
+ SeaTunnelRuntimeException.class,
() ->
runSql(
"select PARSEDATETIME('2021-04-08',
'invalid_pattern') as parsed from dual",
@@ -326,4 +327,167 @@ public class DateTimeFunctionsTest {
rowType,
LocalDate.of(2024, 6, 15)));
}
+
+ @Test
+ public void testParseDateTimeWithAllDateTimeFormats() {
+ SeaTunnelRowType rowType =
+ new SeaTunnelRowType(
+ new String[] {"dummy"},
+ new SeaTunnelDataType[]
{LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+ // DATETIME_STANDARD: yyyy-MM-dd HH:mm:ss
+ SeaTunnelRow row1 =
+ runSql(
+ "select PARSEDATETIME('2024-06-15 14:30:45',
'yyyy-MM-dd HH:mm:ss') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45),
row1.getField(0));
+
+ // DATETIME_WITH_MILLIS: yyyy-MM-dd HH:mm:ss.SSS
+ SeaTunnelRow row2 =
+ runSql(
+ "select PARSEDATETIME('2024-06-15 14:30:45.123',
'yyyy-MM-dd HH:mm:ss.SSS') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(
+ LocalDateTime.of(2024, 6, 15, 14, 30, 45, 123000000),
row2.getField(0));
+
+ // DATETIME_ISO8601: yyyy-MM-dd'T'HH:mm:ss
+ SeaTunnelRow row3 =
+ runSql(
+ "select PARSEDATETIME('2024-06-15T14:30:45',
'yyyy-MM-dd''T''HH:mm:ss') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45),
row3.getField(0));
+
+ // DATETIME_ISO8601_WITH_MILLIS: yyyy-MM-dd'T'HH:mm:ss.SSS
+ SeaTunnelRow row4 =
+ runSql(
+ "select PARSEDATETIME('2024-06-15T14:30:45.987',
'yyyy-MM-dd''T''HH:mm:ss.SSS') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(
+ LocalDateTime.of(2024, 6, 15, 14, 30, 45, 987000000),
row4.getField(0));
+
+ // DATETIME_SLASH: yyyy/MM/dd HH:mm:ss
+ SeaTunnelRow row5 =
+ runSql(
+ "select PARSEDATETIME('2024/06/15 14:30:45',
'yyyy/MM/dd HH:mm:ss') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45),
row5.getField(0));
+
+ // DATETIME_SLASH_WITH_MILLIS: yyyy/MM/dd HH:mm:ss.SSS
+ SeaTunnelRow row6 =
+ runSql(
+ "select PARSEDATETIME('2024/06/15 14:30:45.123',
'yyyy/MM/dd HH:mm:ss.SSS') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(
+ LocalDateTime.of(2024, 6, 15, 14, 30, 45, 123000000),
row6.getField(0));
+
+ // DATETIME_COMPACT: yyyyMMddHHmmss
+ SeaTunnelRow row7 =
+ runSql(
+ "select PARSEDATETIME('20240615143045',
'yyyyMMddHHmmss') as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDateTime.of(2024, 6, 15, 14, 30, 45),
row7.getField(0));
+ }
+
+ @Test
+ public void testParseDateTimeWithAllTimeFormats() {
+ SeaTunnelRowType rowType =
+ new SeaTunnelRowType(
+ new String[] {"dummy"},
+ new SeaTunnelDataType[]
{LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+ // TIME_STANDARD: HH:mm:ss
+ SeaTunnelRow row1 =
+ runSql(
+ "select PARSEDATETIME('14:30:45', 'HH:mm:ss') as dt
from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45),
row1.getField(0));
+
+ // TIME_WITH_MILLIS: HH:mm:ss.SSS
+ SeaTunnelRow row2 =
+ runSql(
+ "select PARSEDATETIME('14:30:45.123', 'HH:mm:ss.SSS')
as dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45, 123000000),
row2.getField(0));
+
+ // TIME_COMPACT: HHmmss
+ SeaTunnelRow row3 =
+ runSql(
+ "select PARSEDATETIME('143045', 'HHmmss') as dt from
dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(java.time.LocalTime.of(14, 30, 45),
row3.getField(0));
+ }
+
+ @Test
+ public void testParseDateTimeWithUnsupportedFormat() {
+ SeaTunnelRowType rowType =
+ new SeaTunnelRowType(
+ new String[] {"dummy"},
+ new SeaTunnelDataType[]
{LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+ Assertions.assertThrows(
+ SeaTunnelRuntimeException.class,
+ () ->
+ runSql(
+ "select PARSEDATETIME('2024-06-15',
'dd/MM/yyyy') as dt from dual",
+ rowType,
+ LocalDateTime.now()));
+ }
+
+ @Test
+ public void testParseDateTimeWithMalformedInput() {
+ SeaTunnelRowType rowType =
+ new SeaTunnelRowType(
+ new String[] {"dummy"},
+ new SeaTunnelDataType[]
{LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+ Assertions.assertThrows(
+ SeaTunnelRuntimeException.class,
+ () ->
+ runSql(
+ "select PARSEDATETIME('not-a-date',
'yyyy-MM-dd') as dt from dual",
+ rowType,
+ LocalDateTime.now()));
+ }
+
+ @Test
+ public void testParseDateTimeWithAllDateFormats() {
+ SeaTunnelRowType rowType =
+ new SeaTunnelRowType(
+ new String[] {"dummy"},
+ new SeaTunnelDataType[]
{LocalTimeType.LOCAL_DATE_TIME_TYPE});
+
+ // DATE_ISO8601: yyyy-MM-dd
+ SeaTunnelRow row1 =
+ runSql(
+ "select TO_DATE('2024-06-15', 'yyyy-MM-dd') as dt from
dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDate.of(2024, 6, 15), row1.getField(0));
+
+ // DATE_SLASH: yyyy/MM/dd
+ SeaTunnelRow row2 =
+ runSql(
+ "select PARSEDATETIME('2024/06/15', 'yyyy/MM/dd') as
dt from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDate.of(2024, 6, 15), row2.getField(0));
+
+ // DATE_COMPACT: yyyyMMdd
+ SeaTunnelRow row3 =
+ runSql(
+ "select PARSEDATETIME('20240615', 'yyyyMMdd') as dt
from dual",
+ rowType,
+ LocalDateTime.now());
+ Assertions.assertEquals(LocalDate.of(2024, 6, 15), row3.getField(0));
+ }
}
