[ https://issues.apache.org/jira/browse/FLINK-30959 ]
Martijn Visser deleted comment on FLINK-30959:
----------------------------------------
was (Author: martijnvisser):
[~yunfengzhou] The docs state "Converts date time string string1 in format
string2 (by default: yyyy-MM-dd HH:mm:ss if not specified) to Unix timestamp
(in seconds), using the specified timezone in table config."
So this function does not take the timezone from the input string into
consideration when doing the conversion, but the timezone that's active for
that session in Flink (which you can change using SET 'table.local-time-zone' =
'Asia/Shanghai'; for example. So I think the function works as designed
> UNIX_TIMESTAMP's return value does not meet expected
> ----------------------------------------------------
>
> Key: FLINK-30959
> URL: https://issues.apache.org/jira/browse/FLINK-30959
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API
> Affects Versions: 1.15.2
> Reporter: Yunfeng Zhou
> Priority: Major
>
> When running the following pyflink program
>
> {code:python}
> import pandas as pd
> from pyflink.datastream import StreamExecutionEnvironment, HashMapStateBackend
> from pyflink.table import StreamTableEnvironment
> if __name__ == "__main__":
> input_data = pd.DataFrame(
> [
> ["Alex", 100.0, "2022-01-01 08:00:00.001 +0800"],
> ["Emma", 400.0, "2022-01-01 00:00:00.003 +0000"],
> ["Alex", 200.0, "2022-01-01 08:00:00.005 +0800"],
> ["Emma", 300.0, "2022-01-01 00:00:00.007 +0000"],
> ["Jack", 500.0, "2022-01-01 08:00:00.009 +0800"],
> ["Alex", 450.0, "2022-01-01 00:00:00.011 +0000"],
> ],
> columns=["name", "avg_cost", "time"],
> )
> env = StreamExecutionEnvironment.get_execution_environment()
> env.set_state_backend(HashMapStateBackend())
> t_env = StreamTableEnvironment.create(env)
> input_table = t_env.from_pandas(input_data)
> t_env.create_temporary_view("input_table", input_table)
> time_format = "yyyy-MM-dd HH:mm:ss.SSS X"
> output_table = t_env.sql_query(
> f"SELECT *, UNIX_TIMESTAMP(`time`, '{time_format}') AS unix_time FROM
> input_table"
> )
> output_table.execute().print()
> {code}
> The actual output is
> {code}
> +----+--------------------------------+--------------------------------+--------------------------------+----------------------+
> | op | name | avg_cost |
> time | unix_time |
> +----+--------------------------------+--------------------------------+--------------------------------+----------------------+
> | +I | Alex | 100.0 |
> 2022-01-01 08:00:00.001 +0800 | 1640995200 |
> | +I | Emma | 400.0 |
> 2022-01-01 00:00:00.003 +0000 | 1640995200 |
> | +I | Alex | 200.0 |
> 2022-01-01 08:00:00.005 +0800 | 1640995200 |
> | +I | Emma | 300.0 |
> 2022-01-01 00:00:00.007 +0000 | 1640995200 |
> | +I | Jack | 500.0 |
> 2022-01-01 08:00:00.009 +0800 | 1640995200 |
> | +I | Alex | 450.0 |
> 2022-01-01 00:00:00.011 +0000 | 1640995200 |
> +----+--------------------------------+--------------------------------+--------------------------------+----------------------+
> {code}
> While the expected result is
> {code:java}
> +----+--------------------------------+--------------------------------+--------------------------------+----------------------+
> | op | name | avg_cost |
> time | unix_time |
> +----+--------------------------------+--------------------------------+--------------------------------+----------------------+
> | +I | Alex | 100.0 |
> 2022-01-01 08:00:00.001 +0800 | 1640995200 |
> | +I | Emma | 400.0 |
> 2022-01-01 00:00:00.003 +0000 | 1640966400 |
> | +I | Alex | 200.0 |
> 2022-01-01 08:00:00.005 +0800 | 1640995200 |
> | +I | Emma | 300.0 |
> 2022-01-01 00:00:00.007 +0000 | 1640966400 |
> | +I | Jack | 500.0 |
> 2022-01-01 08:00:00.009 +0800 | 1640995200 |
> | +I | Alex | 450.0 |
> 2022-01-01 00:00:00.011 +0000 | 1640966400 |
> +----+--------------------------------+--------------------------------+--------------------------------+----------------------+
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
