[
https://issues.apache.org/jira/browse/FLINK-30924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17685989#comment-17685989
]
Feng Jin edited comment on FLINK-30924 at 2/8/23 5:04 PM:
----------------------------------------------------------
[~martijnvisser] The result for TO_TIMESTAMP(FROM_UNIXTIME(0)) is exactly
correct. But the question is, When converting BIGINT type to TIMESTAMP type
, should we recommend users to use TO_TIMESTAMP(FROM_UNIXTIME(xxx)) ?
There is the result when running `cast 0 to TIMESTAMP` on Flink1.12:
Running:
{code:sql}
select cast(0 as TIMESTAMP);{code}
Returns:
{panel}
1970-01-01T00:00
{panel}
Running:
{code:sql}
SET 'table.local-time-zone' = 'Asia/Shanghai';
select cast(0 as timestamp); {code}
Returns:
{panel}
1970-01-01T00:00
{panel}
too.
The data of BIGINT type is usually an absolute number of seconds, it is time
zone independent, it should not be changed in any time zone, and the data of
TIMESTAMP type is also time zone independent data, when we convert these two
types, We should not rely on session time zone。 Maybe my understanding is
wrong, but at least this is the behavior in 1.12。
was (Author: hackergin):
[~martijnvisser] The result for xxx is exactly correct. But the question is,
When converting BIGINT type to TIMESTAMP type
, should we recommend users to use TO_TIMESTAMP(FROM_UNIXTIME(xxx)) ?
There is the result when running `cast 0 to TIMESTAMP` on Flink1.12:
Running:
{code:sql}
select cast(0 as TIMESTAMP);{code}
Returns:
{panel}
1970-01-01T00:00
{panel}
Running:
{code:sql}
SET 'table.local-time-zone' = 'Asia/Shanghai';
select cast(0 as timestamp); {code}
Returns:
{panel}
1970-01-01T00:00
{panel}
too.
The data of BIGINT type is usually an absolute number of seconds, it is time
zone independent, it should not be changed in any time zone, and the data of
TIMESTAMP type is also time zone independent data, when we convert these two
types, We should not rely on session time zone。 Maybe my understanding is
wrong, but at least this is the behavior in 1.12。
> 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
> Priority: Major
>
> 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)
