[jira] [Created] (FLINK-30924) Conversion issues between timestamp and bingint

["Feng Jin (Jira)" <ji...@apache.org>]

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)