This is an automated email from the ASF dual-hosted git repository.
leonard pushed a commit to branch release-1.17
in repository https://gitbox.apache.org/repos/asf/flink.git
The following commit(s) were added to refs/heads/release-1.17 by this push:
new 7e9cf7af50f [FLINK-30959][table][docs] Improve the documentation of
UNIX_TIMESTAMP function for different argument formats
7e9cf7af50f is described below
commit 7e9cf7af50f87d997dd660bc6300eb791bc0a9cf
Author: Hang Ruan <ruanhang1...@hotmail.com>
AuthorDate: Wed Mar 15 23:35:13 2023 +0800
[FLINK-30959][table][docs] Improve the documentation of UNIX_TIMESTAMP
function for different argument formats
This closes #22187.
---
docs/data/sql_functions.yml | 41 +++++++++++++++++++++++++++++------------
docs/data/sql_functions_zh.yml | 37 +++++++++++++++++++++++++++----------
2 files changed, 56 insertions(+), 22 deletions(-)
diff --git a/docs/data/sql_functions.yml b/docs/data/sql_functions.yml
index 4040ff49739..205dcae743c 100644
--- a/docs/data/sql_functions.yml
+++ b/docs/data/sql_functions.yml
@@ -499,7 +499,24 @@ temporal:
description: Gets current Unix timestamp in seconds. This function is not
deterministic which means the value would be recalculated for each record.
- sql: UNIX_TIMESTAMP(string1[, string2])
table: unixTimestamp(STRING1[, STRING2])
- description: '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.'
+ description: |
+ Converts a date time string string1 with 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.
+
+ If a time zone is specified in the date time string and parsed by UTC+X
format such as "yyyy-MM-dd HH:mm:ss.SSS X", this function will use the
specified timezone in the date time string instead of the timezone in table
config.
+ If the date time string can not be parsed, the default value
Long.MIN_VALUE(-9223372036854775808) will be returned.
+
+ ```sql
+ Flink SQL> SET 'table.local-time-zone' = 'Europe/Berlin';
+
+ -- Returns 25201
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001', 'yyyy-MM-dd
HH:mm:ss.SSS');
+ -- Returns 1
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001 +0800',
'yyyy-MM-dd HH:mm:ss.SSS X');
+ -- Returns 25201
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001 +0800',
'yyyy-MM-dd HH:mm:ss.SSS');
+ -- Returns -9223372036854775808
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001', 'yyyy-MM-dd
HH:mm:ss.SSS X');
+ ```
- sql: TO_DATE(string1[, string2])
table: toDate(STRING1[, STRING2])
description: Converts a date string string1 with format string2 (by
default 'yyyy-MM-dd') to a date.
@@ -516,7 +533,7 @@ temporal:
Note that this function can return `NULL`, and you may have to consider
this case. For example, if you want to filter out late data you can use:
- ```
+ ```sql
WHERE
CURRENT_WATERMARK(ts) IS NULL
OR ts > CURRENT_WATERMARK(ts)
@@ -547,7 +564,7 @@ conditional:
If all arguments are NULL, it returns NULL as well. The return type is
the least restrictive, common type of all of its arguments.
The return type is nullable if all arguments are nullable as well.
- ```
+ ```sql
-- Returns 'default'
COALESCE(NULL, 'default')
@@ -625,7 +642,7 @@ json:
allowed. If the string is valid JSON, but not that type, `false` is
returned. The default is
`VALUE`.
- ```
+ ```sql
-- TRUE
'1' IS JSON
'[]' IS JSON
@@ -658,7 +675,7 @@ json:
If the error behavior is omitted, `FALSE ON ERROR` is assumed as the
default.
- ```
+ ```sql
-- TRUE
SELECT JSON_EXISTS('{"a": true}', '$.a');
-- FALSE
@@ -682,7 +699,7 @@ json:
This function returns a JSON string containing the serialized value. If
the value is `NULL`,
the function returns `NULL`.
- ```
+ ```sql
-- NULL
JSON_STRING(CAST(NULL AS INT))
@@ -721,7 +738,7 @@ json:
When using JSON_VALUE in SQL, the path is a character parameter which is
already single quoted,
so you have to escape the single quotes around property name, such as
`JSON_VALUE('{"a b": "true"}', '$.[''a b'']')`.
- ```
+ ```sql
-- "true"
JSON_VALUE('{"a": true}', '$.a')
@@ -757,7 +774,7 @@ json:
case an error was raised, respectively. By default, in both cases `null`
is returned. Other
choices are to use an empty array, an empty object, or to raise an error.
- ```
+ ```sql
-- '{ "b": 1 }'
JSON_QUERY('{ "a": { "b": 1 } }', '$.a')
-- '[1, 2]'
@@ -802,7 +819,7 @@ json:
`JSON_ARRAY`) are inserted directly rather than as a string. This allows
building nested JSON
structures.
- ```
+ ```sql
-- '{}'
JSON_OBJECT()
@@ -838,7 +855,7 @@ json:
This function is currently not supported in `OVER` windows.
- ```
+ ```sql
-- '{"Apple":2,"Banana":17,"Orange":0}'
SELECT
JSON_OBJECTAGG(KEY product VALUE cnt)
@@ -857,7 +874,7 @@ json:
`JSON_ARRAY`) are inserted directly rather than as a string. This allows
building nested JSON
structures.
- ```
+ ```sql
-- '[]'
JSON_ARRAY()
-- '[1,"2"]'
@@ -884,7 +901,7 @@ json:
This function is currently not supported in `OVER` windows, unbounded
session windows, or hop
windows.
- ```
+ ```sql
-- '["Apple","Banana","Orange"]'
SELECT
JSON_ARRAYAGG(product)
diff --git a/docs/data/sql_functions_zh.yml b/docs/data/sql_functions_zh.yml
index 2aab48e4fdc..04f8757a4be 100644
--- a/docs/data/sql_functions_zh.yml
+++ b/docs/data/sql_functions_zh.yml
@@ -630,6 +630,23 @@ temporal:
description: |
使用表配置中指定的时区将格式为 string2 的日期时间字符串 string1（如果未指定默认情况下：yyyy-MM-dd HH:mm:ss）
转换为 Unix 时间戳（以秒为单位）。
+
+ 如果日期时间字符串指定了时区并使用UTC+X的格式解析（例如："yyyy-MM-dd HH:mm:ss.SSS X"），此函数将会使用日期
+ 时间字符串中的时区来转换，而不是表配置的时区。
+ 如果日期时间字符串无法正常解析，此函数将会默认返回Long.MIN_VALUE（即-9223372036854775808）作为结果。
+
+ ```sql
+ Flink SQL> SET 'table.local-time-zone' = 'Europe/Berlin';
+
+ -- Returns 25201
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001', 'yyyy-MM-dd
HH:mm:ss.SSS');
+ -- Returns 1
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001 +0800',
'yyyy-MM-dd HH:mm:ss.SSS X');
+ -- Returns 25201
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001 +0800',
'yyyy-MM-dd HH:mm:ss.SSS');
+ -- Returns -9223372036854775808
+ Flink SQL> SELECT UNIX_TIMESTAMP('1970-01-01 08:00:01.001', 'yyyy-MM-dd
HH:mm:ss.SSS X');
+ ```
- sql: TO_DATE(string1[, string2])
table: toDate(STRING1[, STRING2])
description: 将格式为 string2（默认为 'yyyy-MM-dd'）的字符串 string1 转换为日期。
@@ -648,7 +665,7 @@ temporal:
TIMESTAMP_LTZ(3)。
请注意，此函数可以返回 `NULL`，您可能必须考虑这种情况。例如，如果您想过滤掉后期数据，您可以使用：
- ```
+ ```sql
WHERE
CURRENT_WATERMARK(ts) IS NULL
OR ts > CURRENT_WATERMARK(ts)
@@ -737,7 +754,7 @@ json:
指定可选类型参数将会限制 JSON 对象所允许的类型。
如果字符串是有效的 JSON，但不是指定的类型，则返回 `false`。默认值为 `VALUE`。
- ```
+ ```sql
-- TRUE
'1' IS JSON
'[]' IS JSON
@@ -770,7 +787,7 @@ json:
如果要忽略错误行为，那么将 `FALSE ON ERROR` 设为默认值。
- ```
+ ```sql
-- TRUE
SELECT JSON_EXISTS('{"a": true}', '$.a');
-- FALSE
@@ -793,7 +810,7 @@ json:
此函数返回一个包含序列化值的 JSON 字符串。如果值为 `NULL`，函数返回 `NULL`。
- ```
+ ```sql
-- NULL
JSON_STRING(CAST(NULL AS INT))
@@ -825,7 +842,7 @@ json:
对于路径表达式中包含特殊字符（如空格），你可以使用`['property']` 获
`["property"]`来引用父对象中指定的属性。请确保在属性名两侧加上单引号或双引号。
当在 SQL 使用 JSON_VALUE
时，路径作为一个字符串参数已经被单引号引用了，因此你必须将属性名上的单引号转义，如`JSON_VALUE('{"a b": "true"}', '$.[''a
b'']')`。
- ```
+ ```sql
-- "true"
JSON_VALUE('{"a": true}', '$.a')
@@ -859,7 +876,7 @@ json:
`onEmpty` 和 `onError` 分别决定路径表达式为空或引发错误时的行为。默认情况下，这两种情况都会返回 `null`。其他选择是
使用空数组、空对象或引发错误。
- ```
+ ```sql
-- '{ "b": 1 }'
JSON_QUERY('{ "a": { "b": 1 } }', '$.a')
-- '[1, 2]'
@@ -901,7 +918,7 @@ json:
值是由另一个 JSON 构造函数调用 (`JSON_OBJECT`，`JSON_ARRAY`)
直接插入所创建，而不是作为一个字符串。它允许构建嵌套的 JSON 结构。
- ```
+ ```sql
-- '{}'
JSON_OBJECT()
@@ -936,7 +953,7 @@ json:
目前在 `OVER` windows 中不支持此函数。
- ```
+ ```sql
-- '{"Apple":2,"Banana":17,"Orange":0}'
SELECT
JSON_OBJECTAGG(KEY product VALUE cnt)
@@ -951,7 +968,7 @@ json:
元素是由另一个 JSON 构造函数调用 (`JSON_OBJECT`，`JSON_ARRAY`)
直接插入所创建，而不是作为一个字符串。它允许构建嵌套的 JSON 结构。
- ```
+ ```sql
-- '[]'
JSON_ARRAY()
-- '[1,"2"]'
@@ -976,7 +993,7 @@ json:
此函数目前不支持 `OVER` windows、未绑定的 session windows 或 hop windows。
- ```
+ ```sql
-- '["Apple","Banana","Orange"]'
SELECT
JSON_ARRAYAGG(product)
