[
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 8:16 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 a 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 the conclusion as following :
* l{*}ong epoch second{*} matches concept *Instant* matches SQL type
*TIMESTAMP_LTZ*
* *timestamp string* matches concept *wall-clock* 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}
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`
> 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)
