This is an automated email from the ASF dual-hosted git repository.
shenghang 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 41abb7d036 [improve] the substring and substr functions support
Date,LocalDate,L… (#9969)
41abb7d036 is described below
commit 41abb7d036b5dd9a557263d8948b7b7a8e800bd7
Author: misi <[email protected]>
AuthorDate: Sat Nov 1 14:51:36 2025 +0800
[improve] the substring and substr functions support Date,LocalDate,L…
(#9969)
Co-authored-by: misi <[email protected]>
---
.../seatunnel/common/utils/DateTimeUtils.java | 16 +++
.../apache/seatunnel/common/utils/DateUtils.java | 5 +
.../sql/zeta/functions/StringFunction.java | 74 +++++++++++-
.../sql/zeta/functions/StringFunctionTest.java | 124 +++++++++++++++++++++
4 files changed, 217 insertions(+), 2 deletions(-)
diff --git
a/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateTimeUtils.java
b/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateTimeUtils.java
index b1e0856e1f..7c78e7e674 100644
---
a/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateTimeUtils.java
+++
b/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateTimeUtils.java
@@ -21,10 +21,12 @@ import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
+import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.SignStyle;
+import java.time.temporal.Temporal;
import java.time.temporal.TemporalAccessor;
import java.time.temporal.TemporalQueries;
import java.util.HashMap;
@@ -314,6 +316,20 @@ public class DateTimeUtils {
return dateTime.format(FORMATTER_MAP.get(formatter));
}
+ public static String toString(OffsetDateTime offsetDateTime, Formatter
formatter) {
+ return toString(offsetDateTime.toLocalDateTime(), formatter);
+ }
+
+ public static String toString(Temporal temporal, Formatter formatter) {
+ if (temporal instanceof OffsetDateTime) {
+ return toString(((OffsetDateTime) temporal).toLocalDateTime(),
formatter);
+ } else if (temporal instanceof java.time.ZonedDateTime) {
+ return toString(((java.time.ZonedDateTime)
temporal).toLocalDateTime(), formatter);
+ } else {
+ return FORMATTER_MAP.get(formatter).format(temporal);
+ }
+ }
+
public static String toString(long timestamp, Formatter formatter) {
Instant instant = Instant.ofEpochMilli(timestamp);
return toString(LocalDateTime.ofInstant(instant,
ZoneId.systemDefault()), formatter);
diff --git
a/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateUtils.java
b/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateUtils.java
index 04105cfdf8..81385cb1ad 100644
---
a/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateUtils.java
+++
b/seatunnel-common/src/main/java/org/apache/seatunnel/common/utils/DateUtils.java
@@ -21,6 +21,7 @@ import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.SignStyle;
+import java.time.temporal.Temporal;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;
@@ -189,6 +190,10 @@ public class DateUtils {
return date.format(FORMATTER_MAP.get(formatter));
}
+ public static String toString(Temporal temporal, Formatter formatter) {
+ return FORMATTER_MAP.get(formatter).format(temporal);
+ }
+
public enum Formatter {
YYYY_MM_DD("yyyy-MM-dd"),
YYYY_M_D("yyyy/M/d"),
diff --git
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
index f2a6762cfd..dec3948f1b 100644
---
a/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
+++
b/seatunnel-transforms-v2/src/main/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunction.java
@@ -20,6 +20,8 @@ package org.apache.seatunnel.transform.sql.zeta.functions;
import org.apache.seatunnel.shade.com.google.common.hash.Hashing;
import org.apache.seatunnel.common.exception.CommonErrorCodeDeprecated;
+import org.apache.seatunnel.common.utils.DateTimeUtils;
+import org.apache.seatunnel.common.utils.DateUtils;
import org.apache.seatunnel.transform.exception.TransformException;
import org.apache.seatunnel.transform.sql.zeta.ZetaSQLFunction;
@@ -27,9 +29,14 @@ import org.apache.groovy.parser.antlr4.util.StringUtils;
import java.lang.reflect.Array;
import java.nio.charset.StandardCharsets;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.OffsetDateTime;
+import java.time.ZoneOffset;
import java.time.temporal.Temporal;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.regex.Matcher;
@@ -625,11 +632,74 @@ public class StringFunction {
return new String(chars, StandardCharsets.ISO_8859_1);
}
+ /**
+ * Convert date/time objects to standardized string format
+ *
+ * @param obj the object to convert
+ * @return standardized string representation of the date/time object
+ */
+ private static String convertDateToString(Object obj) {
+ if (obj == null) {
+ return null;
+ }
+
+ // Handle java.util.Date and subclasses (java.sql.Date,
java.sql.Timestamp)
+ if (obj instanceof Date) {
+ Date date = (Date) obj;
+ LocalDateTime localDateTime =
LocalDateTime.ofInstant(date.toInstant(), ZoneOffset.UTC);
+ return DateTimeUtils.toString(
+ localDateTime,
DateTimeUtils.Formatter.YYYY_MM_DD_HH_MM_SS);
+ }
+
+ // Handle java.time types
+ if (obj instanceof LocalDate) {
+ LocalDate localDate = (LocalDate) obj;
+ return DateUtils.toString(localDate,
DateUtils.Formatter.YYYY_MM_DD);
+ }
+
+ if (obj instanceof LocalDateTime) {
+ LocalDateTime localDateTime = (LocalDateTime) obj;
+ return DateTimeUtils.toString(
+ localDateTime,
DateTimeUtils.Formatter.YYYY_MM_DD_HH_MM_SS);
+ }
+
+ if (obj instanceof OffsetDateTime) {
+ OffsetDateTime offsetDateTime = (OffsetDateTime) obj;
+ return DateTimeUtils.toString(
+ offsetDateTime,
DateTimeUtils.Formatter.YYYY_MM_DD_HH_MM_SS);
+ }
+
+ // For Temporal objects that are not specifically handled above
+ if (obj instanceof Temporal) {
+ Temporal temporal = (Temporal) obj;
+ try {
+ // Try to format as timestamp first
+ return DateTimeUtils.toString(
+ temporal, DateTimeUtils.Formatter.YYYY_MM_DD_HH_MM_SS);
+ } catch (Exception e) {
+ try {
+ // Fallback to date-only format
+ return DateUtils.toString(temporal,
DateUtils.Formatter.YYYY_MM_DD);
+ } catch (Exception ex) {
+ // If all else fails, use toString
+ return obj.toString();
+ }
+ }
+ }
+
+ // For non-date objects, convert to string directly
+ return obj.toString();
+ }
+
public static String substring(List<Object> args) {
- String s = (String) args.get(0);
- if (s == null) {
+ Object input = args.get(0);
+ if (input == null) {
return null;
}
+
+ // Convert date types to standardized string format
+ String s = convertDateToString(input);
+
int sl = s.length();
int start = ((Number) args.get(1)).intValue();
Object v3 = null;
diff --git
a/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
new file mode 100644
index 0000000000..038e56042a
--- /dev/null
+++
b/seatunnel-transforms-v2/src/test/java/org/apache/seatunnel/transform/sql/zeta/functions/StringFunctionTest.java
@@ -0,0 +1,124 @@
+/*
+ * 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.functions;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.time.OffsetDateTime;
+import java.time.ZoneOffset;
+import java.time.temporal.Temporal;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+
+public class StringFunctionTest {
+
+ @Test
+ public void testSubstringWithString() {
+ List<Object> args = new ArrayList<>();
+ args.add("Hello World");
+ args.add(1);
+ Assertions.assertEquals("Hello World", StringFunction.substring(args));
+
+ args.clear();
+ args.add("Hello World");
+ args.add(7);
+ Assertions.assertEquals("World", StringFunction.substring(args));
+
+ args.clear();
+ args.add("Hello World");
+ args.add(1);
+ args.add(5);
+ Assertions.assertEquals("Hello", StringFunction.substring(args));
+ }
+
+ @Test
+ public void testSubstringWithLocalDate() {
+ List<Object> args = new ArrayList<>();
+
+ // Test LocalDate
+ LocalDate date = LocalDate.of(2023, 12, 25);
+ args.add(date);
+ args.add(1);
+ args.add(4);
+ Assertions.assertEquals("2023", StringFunction.substring(args));
+ }
+
+ @Test
+ public void testSubstringWithLocalDateTime() {
+ List<Object> args = new ArrayList<>();
+
+ // Test LocalDateTime
+ LocalDateTime dateTime = LocalDateTime.of(2023, 12, 25, 15, 30, 45);
+ args.add(dateTime);
+ args.add(2);
+ args.add(6);
+ Assertions.assertEquals("023-12", StringFunction.substring(args));
+ }
+
+ @Test
+ public void testSubstringWithOffsetDateTime() {
+ List<Object> args = new ArrayList<>();
+
+ // Test OffsetDateTime
+ OffsetDateTime offsetDateTime =
+ LocalDateTime.of(2023, 12, 25, 15, 30,
45).atOffset(ZoneOffset.UTC);
+ args.add(offsetDateTime);
+ args.add(1);
+ args.add(4);
+ Assertions.assertEquals("2023", StringFunction.substring(args));
+ }
+
+ @Test
+ public void testSubstringWithUtilDate() {
+ List<Object> args = new ArrayList<>();
+
+ // Test java.util.Date
+ Date utilDate = new Date(123, 11, 25); // Year 2023 (123 + 1900),
Month 12, Day 25
+ args.add(utilDate);
+ args.add(1);
+ args.add(4);
+ // Should extract year part from formatted string "2023-12-25 00:00:00"
+ Assertions.assertEquals("2023", StringFunction.substring(args));
+ }
+
+ @Test
+ public void testSubstringWithNullInput() {
+ List<Object> args = new ArrayList<>();
+ args.add(null);
+ args.add(1);
+ Assertions.assertNull(StringFunction.substring(args));
+ }
+
+ @Test
+ public void testSubstringWithTemporal() {
+ List<Object> args = new ArrayList<>();
+
+ // Test LocalTime (as a Temporal implementation not explicitly handled)
+ Temporal time = LocalTime.of(15, 30, 45);
+ args.add(time);
+ args.add(1);
+ args.add(5);
+ // Should extract time part from formatted string "15:30:45"
+ Assertions.assertEquals("15:30", StringFunction.substring(args));
+ }
+}
