如何使用Snowflake SQL解析ISO 8601时间戳？ [英] How can I parse an ISO 8601 timestamp with Snowflake SQL?

问题描述

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

I'm looking for a generic function that allows me to parse ISO8601 timestamps. I know about to_timestamp_tz but I couldn't find a way to create a format parameter that will parse all the possible variations of ISO-8601 datetimes:

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变体。）

So is there anyway to parse a generic ISO 8601? (My input can come with different variations of 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

大多数情况下减少了处理UTC偏移量的4种表达方式（ +00：00 ， +0000 ， +00 和 Z ），并且具有可选的秒和小数秒。

Mostly is reduced to handle the 4 ways of expressing the UTC offset (+00:00, +0000, +00 and Z) and having optional seconds and fractional seconds.

推荐答案

您可以设置参数TIMESTAMP_INPUT_FORMAT 设置为 AUTO ，

表示将识别以下格式：

自动检测/时间戳格式支持的格式

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

If the main problem is colons, you can strip the colons from the input string before converting using a TIMESTAMP format:

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）：

JavaScript seems to recognize more ISO variants than Snowflake SQL, but truncates to precision (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

这篇关于如何使用Snowflake SQL解析ISO 8601时间戳？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！