datetime - 如何使用 Snowflake SQL 解析 ISO 8601 时间戳？

Question

我正在寻找一个允许我解析 ISO8601 时间戳的通用函数。我知道 to_timestamp_tz，但我找不到创建 format 参数的方法，该参数将解析 ISO-8601 日期时间的所有可能变体:

select '2012-01-01T12:00:00+00:00'::timestamp_tz; // this works 

select '2012-01-01T12:00:00+0000'::timestamp_tz; //Timestamp '2012-01-01T12:00:00+0000' is not recognized, although is a valid iso8601 (no colon in the timezone)

select to_timestamp_tz('2012-01-01T12:00:00.123456+00:00', 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM'); // works
select to_timestamp_tz('2012-01-01T12:00:00.123456+0000', 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM'); // Can't parse '2012-01-01T12:00:00.123456+0000' as timestamp with format 'YYYY-MM-DDTHH24:MI:SS.FFTZH:TZM', again because of it has no colon in the timezone


select to_timestamp_tz('2012-01-01T12:00:00.123456+0000', 'YYYY-MM-DDTHH24:MI:SS.FFTZHTZM'); //works

select to_timestamp_tz('2012-01-01T12:00:00.123456+00:00', 'YYYY-MM-DDTHH24:MI:SS.FFTZHTZM'); //Can't parse '2012-01-01T12:00:00.123456+00:00' as timestamp with format 'YYYY-MM-DDTHH24:MI:SS.FFTZHTZM' , fails because it doesn't expect a colon in the timezone

那么有没有办法解析通用的 ISO 8601？ (我的输入可能带有 ISO 8601 的不同变体)。

它应该解析的示例输入:

2012-01-01T12:00:00.123456+00:00
2012-01-01T12:00:00.123456+0000
2012-01-01T12:00:00.123456+00
2012-01-01T12:00:00.123456Z
2012-01-01T12:00+00:00 // no seconds
2012-01-01T12:00+0000
2012-01-01T12:00+01
2012-01-01T12:00Z

主要是为了处理 4 种表示 UTC 偏移量的方式(+00:00、+0000、+00 和 Z) 并且有可选的秒和小数秒。

Answer 1

你可以设置Parameter TIMESTAMP_INPUT_FORMAT为AUTO,
这意味着将识别以下格式:
Supported Formats for AUTO Detection/Timestamp Formats

如果主要问题是冒号，您可以在使用 TIMESTAMP 格式转换之前从输入字符串中去除冒号:

SELECT TO_TIMESTAMP_LTZ(
  TRANSLATE('2019-11-25T14:16:36.556 +01:00', ':', ''),
  'YYYY-MM-DD"T"HH24MISS.FF TZHTZM'
);

JavaScript 似乎比 Snowflake SQL 识别更多的 ISO 变体，但截断到精度 (3):

CREATE OR REPLACE FUNCTION CONV_TS(DT TEXT) RETURNS VARIANT LANGUAGE JAVASCRIPT STRICT
  AS 'return new Date(DT).toJSON()';
SELECT TRY_TO_TIMESTAMP_TZ(TS) TRY_TZ, CONV_TS(TS)::TIMESTAMP_TZ JS_TS, TS FROM VALUES
('2012-01-01T12:00:00.123456+00:00'),
('2012-01-01T12:00:00.123456+0000'), // Also fails TRY%
('2012-01-01T12:00:00.123456+00'), // Fails JS
('2012-01-01T12:00:00.123456Z'),
('2012-01-01T12:00+00:00'),
('2012-01-01T12:00+0000'), // Also fails TRY%
('2012-01-01T12:00+01'), // Fails JS
('2012-01-01T12:00Z') v(ts);

=>

2012-01-01 12:00:00.123 +0000  2012-01-01 12:00:00.123 +0000  2012-01-01T12:00:00.123456+00:00
NULL                           2012-01-01 12:00:00.123 +0000  2012-01-01T12:00:00.123456+0000
NULL                           NULL                           2012-01-01T12:00:00.123456+00
2012-01-01 12:00:00.123 +0000  2012-01-01 12:00:00.123 +0000  2012-01-01T12:00:00.123456Z
2012-01-01 12:00:00.000 +0000  2012-01-01 12:00:00.000 +0000  2012-01-01T12:00+00:00
NULL                           2012-01-01 12:00:00.000 +0000  2012-01-01T12:00+0000
NULL                           NULL                           2012-01-01T12:00+01
2012-01-01 12:00:00.000 +0000  2012-01-01 12:00:00.000 +0000  2012-01-01T12:00Z