如何在flink SQL中使用ISO-8601日期？

des4xlb0 于 2022-12-09 发布在 Apache

关注(0)|答案(2)|浏览(397)

Based on my research Flink SQL accepts "0000-01-01 00:00:00.000000000" as the timestamp format, but my timestamps in kafka are coming in "0000-01-01T00:00:00.000000000" format which causes by flink sql queries to fail.
Is there a way to convert, a somewhat common format (e.g. 2022-05-02T18:28:07.881414Z) for a date to the desired format in flink? In other SQL languages this would be a very simple operation, but from reading Flink documentation I cannot see a way to do it.
I tried doing a string replace and converting the string to timestamp but it did not work. I'm running Flink 1.13.
I tried using CONVERT_TZ() but it does not work with a string timestamp:
This works:

SELECT
  CONVERT_TZ(string_ts, 'UTC', 'America/Los_Angeles') as test_ts
FROM
  (VALUES ('2022-05-02 18:28:07.881414Z')) AS NameTable(string_ts);

This doesn't work:

SELECT
  CONVERT_TZ(string_ts, 'UTC', 'America/Los_Angeles') as test_ts
FROM
  (VALUES ('2022-05-02T18:28:07.881414Z')) AS NameTable(string_ts);

This gives me a timestamp but I'm not interested in changing the timezone. How can I convert the string with the replace to a timestamp that can be used with other timestamp functions? Such as timestamp diff:

SELECT
    TO_TIMESTAMP(replace(string_ts, 'T', ' ')) -- gives null
    , CONVERT_TZ(replace(string_ts, 'T', ' '), 'UTC', 'America/Los_Angeles') -- works
FROM
  (VALUES ('2022-05-02T18:28:07.881414Z')) AS NameTable(string_ts);

How can I get Flink to recognize '2022-05-02T18:28:07.881414Z' as a timestamp?

apache-flink

来源：https://stackoverflow.com/questions/72136930/how-to-use-iso-8601-date-in-flink-sql

2条答案

按热度按时间

cbjzeqam1#

如果使用JSON格式接收此数据，则可以设置此选项
json.timestamp-format.standard: ISO-8601
to tell Flink how the strings are meant to be interpreted. This is described in the documentation at https://nightlies.apache.org/flink/flink-docs-stable/docs/connectors/table/formats/json/#json-timestamp-format-standard.
如果需要使用Flink SQL将ISO-8601字符串转换为时间戳，您可能会发现CONVERT_TZ(string1, string2, string3)很有用，或者您可能更喜欢将此转换作为用户定义函数来实现。

赞(0）回复(0）举报 2022-12-09

mrwjdhj32#

SELECT
    TO_TIMESTAMP(replace(replace(string_ts, 'T', ' '), 'Z' ,'' ))
FROM
  (VALUES ('2022-05-02T18:28:07.881414Z')) AS NameTable(string_ts);

赞(0）回复(0）举报 2022-12-09

我来回答

如何在flink SQL中使用ISO-8601日期？

2条答案

相关问题

热门标签

最新问答