davidzollo opened a new issue, #9286: URL: https://github.com/apache/seatunnel/issues/9286
### Search before asking - [x] I had searched in the [feature](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22Feature%22) and found no similar feature requirement. ### Description Currently, in SeaTunnel's SQL Transform, the `substring` and `substr` functions can only operate on string type fields. However, when trying to extract portions of a date or timestamp field (e.g., only getting the year part from a date), users have to explicitly convert the date field to string first, which is not intuitive and introduces extra complexity. ## Current Behavior When trying to use `substring` function directly on a date field: ```sql SELECT substring(update_time, 1, 10) FROM my_table ``` This fails because the `substring` function doesn't accept date/timestamp input types. ## Expected Behavior The `substring` and `substr` functions should be able to automatically handle date/timestamp types by converting them to a standard string representation before performing the substring operation. For example: ```sql -- Should return '2023-01-15' (just the date part) SELECT substring(timestamp_column, 1, 10) FROM my_table -- Should return '2023' (just the year) SELECT substring(date_column, 1, 4) FROM my_table ``` ## Proposed Solution Enhance the `StringFunction.substring()` method in the SQL Transform implementation to: 1. Check if the input is a date type (Date, Timestamp, LocalDate, LocalDateTime, etc.) 2. If it is, convert it to a string using a standardized formatter (e.g., `yyyy-MM-dd HH:mm:ss` for timestamps) 3. Then perform the substring operation as usual ## Benefits 1. Improved user experience by allowing direct manipulation of date fields 2. Alignment with common SQL behavior in popular databases ## Affected Component SQL Transform module in SeaTunnel transforms-v2. ### Usage Scenario _No response_ ### Related issues _No response_ ### Are you willing to submit a PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
