Feng Jin created FLINK-30924:
--------------------------------
Summary: Conversion issues between timestamp and bingint
Key: FLINK-30924
URL: https://issues.apache.org/jira/browse/FLINK-30924
Project: Flink
Issue Type: Bug
Components: Table SQL / API
Affects Versions: 1.16.1
Reporter: Feng Jin
When casting to convert timestamp and bigint, the following exception is
thrown:
{code:java}
//代码占位符
org.apache.flink.table.api.ValidationException: The cast from NUMERIC type to
TIMESTAMP type is not allowed. It's recommended to use
TO_TIMESTAMP(FROM_UNIXTIME(numeric_col)) instead, note the numeric is in
seconds.
{code}
However, the FROM_UNIXTIME function will use the local time zone for
conversion, but the TO_TIMESTAMP function will not use the local time zone but
will use the UTC time zone conversion, so that the actual result in the wrong
result.
The following is an example of the results of the test
{code:java}
//代码占位符
Flink SQL> SET 'table.local-time-zone' = 'Asia/Shanghai';
Flink SQL> select TO_TIMESTAMP(FROM_UNIXTIME(0));
// result
EXPR$0
1970-01-01 08:00:00.000
{code}
UNIX_TIMESTAMP(CAST(timestamp_col AS STRING)) has the same problem.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
