[
https://issues.apache.org/jira/browse/FLINK-30924?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17686899#comment-17686899
]
Leonard Xu edited comment on FLINK-30924 at 2/10/23 7:03 AM:
-------------------------------------------------------------
{code:java}
Flink SQL> SET 'table.local-time-zone' = 'Asia/Shanghai';
Flink SQL> select TO_TIMESTAMP(FROM_UNIXTIME(0));
// result
1970-01-01 08:00:00.000
{code}
Hi, [~hackergin]
The result is also reasonable and correct from my understanding. Time is a
complex topic, and I'll try to explain some part of it and make your case
simpler to understand.
When we use a long value with BIGINT(or numeric SQL type) to represent a time,
it usually comes from the upstream system's unix time (e.g.
System.currentMills() in java, unix time in Linux OS). We may have some
question about the long value.
*Q1: What's the meaning of the long value?*
* It means the *past period* since an fixed point in the absolute timeline,
the fixed point people called it epoch,
the wall-clock is 1970-01-01 00:00:00 in region whose time zone is UTC+0 when
epoch point arrived，
the wall-clock is 1970-01-01 01:00:00 in region whose time zone is UTC+1 when
epoch point arrived, and so on.
*Q2:Thus, a epoch 4 seconds means the time past 4 seconds since the epoch
point, what the wall-clock is in UTC+0 region and UTC+1 region at this time
point？*
I guess you could give the right wall-clocks:
1970-01-01 00:00:04 in UTC+0 regions,
1970-01-01 01:00:04 in UTC+1 regions.
*Q3: Is the epoch seconds related to timezone?*
No, it means the past time period , the past time period is same and equal in
any region，it usually represented in a long value in various systems as well as
Flink SQL.
*Q4:How we interpret the epoch seconds to a human readable timestamp expression
like a wall-clock ?*
Yes, we need timezone here.
We interpret epoch 4 seconds to wall-clock 1970-01-01 01:00:04 in these regions
with UTC+1 timezone,
We interpret epoch 4 seconds to wall-clock 1970-01-01 08:00:04 in these regions
with UTC+8 timezone.
Here you should understand why the conversion between timestamp and bingint in
Flink SQL is correct.
I didn't explain the SQL type TIMESTAMP and TIMESTAMP_LTZ, nor the concept
Instant, time zone. If you want to dig more about the type and concept , I can
give some conclusion is :
* `long epoch second` matches `Instant concept` matches SQL type
`TIMESTAMP_LTZ`
* `timestamp string` matches `wall-clock concept` matches SQL type `TIMESTAMP`
was (Author: leonard xu):
{code:java}
Flink SQL> SET 'table.local-time-zone' = 'Asia/Shanghai';
Flink SQL> select TO_TIMESTAMP(FROM_UNIXTIME(0));
// result
1970-01-01 08:00:00.000
{code}
[~hackergin] The result is also reasonable and correct from my understanding.
Time is an complex topic, and I'll try to explain some some part of it and make
your case simpler to understand.
When we use a long value with BIGINT(or numeric SQL type) to represent a time,
it usually comes from the upstream system's unix time (e.g.
System.currentMills() in java, unix time in Linux OS). We may have some
question about the long value.
Q1: What's the meaning of the long value?
It means the *past period* since an fixed point in the absolute timeline,
the fixed point people called it epoch,
the wall-clock is 1970-01-01 00:00:00 in region whose time zone is UTC+0 when
epoch point arrived，
the wall-clock is 1970-01-01 01:00:00 in region whose time zone is UTC+1 when
epoch point arrived, and so on.
Q2:Thus, a epoch 4 seconds means the time past 4 seconds since the epoch point,
what the wall-clock is in UTC+0 region and UTC+1 region at this time point？I
guess you could give the right wall-clocks:
1970-01-01 00:00:04 in UTC+0 regions,
1970-01-01 01:00:04 in UTC+1 regions.
Q3: Is the epoch seconds related to timezone?
No, it means the past time period , the past time period is same and equal in
any region，it usually represented in a long value in various systems as well as
Flink SQL.
Q4:How we interpret the epoch seconds to a human readable timestamp expression
like a wall-clock ?
Yes, we need timezone here.
We interpret epoch 4 seconds to wall-clock 1970-01-01 01:00:04 in these regions
with UTC+1 timezone,
We interpret epoch 4 seconds to wall-clock 1970-01-01 08:00:04 in these regions
with UTC+8 timezone.
Here you should understand why the conversion between timestamp and bingint in
Flink SQL is correct.
I didn't explain the SQL type TIMESTAMP and TIMESTAMP_LTZ, nor the concept
Instant, time zone. If you want to dig more about the type and concept , I can
give some conclusion is :
* `long epoch second` matches `Instant concept` matches SQL type `TIMESTAMP_LTZ`
* `timestamp string` matches `wall-clock concept` matches SQL type `TIMESTAMP`
> 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)
