Как я могу проанализировать временную метку ISO 8601 с помощью Snowflake SQL?

Я ищу общую функцию, которая позволяет мне анализировать временные метки 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) и наличия дополнительных секунд и дробных секунд.


snowflake-cloud-data-platform datetime
person RubenLaguna    schedule 25.11.2019    source источник
comment
Пожалуйста, укажите все варианты метки времени в своем вопросе.   -  person Tim Biegeleisen    schedule 25.11.2019
comment
Я включил несколько известных мне примеров вариантов iso8601. Но то, что мне нужно, - это то, что обрабатывает все вариации (которые есть в стандарте ISO), те, о которых я знаю, и, возможно, другие, которые, возможно, есть в стандарте.   -  person RubenLaguna    schedule 25.11.2019

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете установить параметр TIMESTAMP_INPUT_FORMAT равным AUTO ,
что означает, что будут распознаваться следующие форматы:
Поддерживаемые форматы для автоматического определения / форматов отметок времени

Если основная проблема заключается в двоеточиях, вы можете удалить двоеточия из входной строки перед преобразованием в формате TIMESTAMP:

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

JavaScript, похоже, распознает больше вариантов ISO, чем Snowflake SQL, но усекает до точности (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
person Hans Henrik Eriksen    schedule 25.11.2019
comment
alter session set timestamp_input_format = auto; select to_timestamp_tz('2012-01-01T12:00:00.123456+0000'); // not recognized select to_timestamp_tz('2012-01-01T12:00:00+0000');// not recognized двоеточие - это часть проблемы, вторая - необязательные секунды. - person RubenLaguna; 25.11.2019
comment
Но это интересный выбор, который они сделали с поддерживаемыми форматами для автоматического обнаружения. - person RubenLaguna; 25.11.2019
comment
Я немного поэкспериментировал, и JavaScript, похоже, принимает больше вариантов, но все же не все - person Hans Henrik Eriksen; 25.11.2019